Compressing SQL Server Backups

A useful SQL Server 2008 feature, is the ability to compress backups - as they are created. Sadly this is an Enterprise Edition feature only, although Standard edition can restore a compressed backup.

BACKUP DATABASE WareHouse TO DISK = 'E:\Temp\WareHouse.BAK'
WITH COMPRESSION, INIT

Using this, I'll compare Backup File sizes, and duration. I have a single, 15GB database, comprised of two tables. They'll be backed up to the same location. One time with COMPRESSION, the other time using the default uncompressed format.



Lets now look at restore times for the two different backups



In this scenario, it is actually faster to work with Compressed Backups, even for restores. Though the trade-off is CPU load, compressed backups require alot more CPU time than the uncompressed formats.