Problem
Databases keep getting larger, and the time we are given for backups to complete seldom keeps pace. With SQL 2008 you have the ability to compress your database backups, saving disk space but more importantly saving time.
Solution
Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.
To enabling backup compression at the server level, right-click on the instance name inside of server explorer in SSMS to bring up the Server Properties screen. Go to the ‘Database Settings’ and you should see the following:
In my opinion, this setting is buried on a very busy page, and is quite easy to miss. Fortunately you have a second option to configure this setting by using some T-SQL:
EXEC sys.sp_configure ‘backup compression default’, ‘1’
GO
RECONFIGURE WITH OVERRIDE
GO
If you did not want to enable compression for the server instance, you also have the option of enabling backup compression for individual backups:
BACKUP DATABASE [AdventureWorks]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.BACONMSSQL\Backup\AW_compress.bak’
WITH COMPRESSION
GO
The actual amount of compression you will get depends primarily on the type of data you have in your database. If you are storing .jpg files in your database, for example, then do not expect much benefits from compression. Another factor to consider is encryption. If your data has been encrypted previously, then your compression ratio will be close to 1:1. Lastly you need to consider if your database already has data compression enabled. If you are already compressing data then you will not see much benefit from backup compression.
To calculate the compression ratio for your backups you can utilize two columns in the backupset table inside of the msdb database. This simple T-SQL statement will show you the ratio for all of the backups currently stored in the backupset table.
SELECT backup_size/compressed_backup_size
FROM msdb..backupset
GO
Next Steps
- Enable backup compression for your instance or for individual backups by following the steps listed above.
