27 June,2012 by Jack Vamvas
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