Compressing SQL Server Backups

Backups in SQL Server have the potential for a great amount of compression. Back with SQL Server 2000 I had devised a way the automate the compression of the backups after the backup jobs ran. Even with SQL Server 2005, all database backups to disk are stored as an uncompressed bak file. You have to look to third party tools like Red Gate SQL Backup to compress on the fly or perform compression of the files after the backup process completed. With SQL Server 2008 you are able to create a backup that compresses during the backup process. One unfortunate caveat is that the feature was only available in Enterprise Edition and above. But with SQL Server 2008 R2, Microsoft added the feature to Standard Edition as well. Another thing to keep in mind is that compressed backups can be restored to any edition of SQL Server since SQL Server 2008, but not any version from SQL Server 2005 and before.

Using compression during backup is simply a matter of selecting compression from a drop down, adding a switch to a T-SQL command, or setting the server default for compression. Using the SSMS interface, right click the database, go to Tasks, and select Back up. Set any of the General settings as you normally would. Under the Options page, at the bottom, is a drop down for compression. Simply choose to “Compress Backup” and that’s it.

Choose Compress Backup on Options Page

To use compression with T-SQL, all you need to do is add the COMPRESSION switch to the backup statement in the WITH clause.

BACKUP DATABASE [CAI] TO  DISK = N'E:\MSSQLBackup\CAI.bak' WITH COMPRESSION;

By default, SQL Server sets the backup compression to 0, or off. To change the default value use sp_configure.

sp_configure 'backup compression default', 1
RECONFIGURE
GO

You may want to evaluate whether or not you want the default value set to true. Books Online states that compression significantly increases CPU usage. But since most backups occur during non-peak hours, it may be a great trade off for the disk space savings.

You can analyze the effects of compression by looking at a couple of fields in the backupset table. For example, the following statement shows that the backup achieved a 5:1, or 80%, compression ratio. Instead of a 1.4GB backup it created a 283MB backup.

SELECT TOP 1 name, backup_size, compressed_backup_size, 
       backup_size/compressed_backup_size as ratio 
FROM msdb..backupset where name like 'MID%';

This backup had an 80% compression rate

Give compression a try and save some space. For more in depth discussion see this article by SQLCAT back in 2008 that talks more about compression ratios and ways to optimize the process.

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *