There was a lack of space on a destination drive, so I decided to implement SQL Server 2008 Backup Compression . The aim was to decrease the size of the storage and fit the backup file onto the drive.

The usage of COMPRESSION at the BACKUP phase is distinct from Data compression in SQL Server 2008

By default SQL Server 2008 has backup compression turned off. Check sp_configure. Adding the COMPRESSION keyword , triggers the COMPRESSION process.

You’ll notice some increased CPU usage, so be careful with COMMPRESSION during high usage times.

The size impact of the database compression will depend on the data type. For example, compressing string data types  is more effective then numbers. 

A quick test of the same database indicated the level of storage space saving. The same database was backed up on both occassions. I use the Copy ONLY backup in SQL server and not break the backup chain

BACKUP DATABASE  SQLServerDB
TO DISK = 'N:\SQLServerDB.bak'  
WITH INIT,COPY_ONLY


--The backup file was 18GB


BACKUP DATABASE   SQLServerDB
TO DISK = 'N:\SQLServerDB_1.bak'  
WITH INIT,COMPRESSION,COPY_ONLY

--The backup file was 6.02 GB

 Read More

SQL Server – Differential Backup - SQL Server DBA

SQL Server backup review with Powershell - SQL Server DBA

Copy only backup in SQL server 2005 and not break the backup chain