Backup Compression with SQL Server 2008

I wanted to share my results towards using the backup compression utility built into SQL Server 2008 Enterprise Edition.   I have noticed a compression range from 65% to 75%.

Database SQL 2005 backup SQL 2008 (compression enabled) Compression
DB One 18.2 GB 5.6 GB 69%
DB Two 3.7 GB 1.3 GB 65%
DB Three 1.2 GB .3 GB 75%

By default, backups are not compressed. You can change this setting by using sp_configure to set the value of the backup_compression_default setting or by selecting the option in the Server Properties dialog box. ( I don’t recommend  this as you can specify the compression type as shown below)

For anyone using SQL Server 2008 who is  not familiar with the compression feature you can enable it by using the following example.

When you create a backup on the options page you will notice a compression section at the bottom of the screen.  There are three values in the dropdown including use default server settings, Compress backup, and do not compress backup.  Select compress backup and click OK (I highly recommend you also check the checkbox to verify backup when finished)

SQLBackup

For those who are using the backup compression what are your results?  Shoot a responses.  I want to know how its working out for you.

  • Trevor Hall

    Great article – important to point out that backup compression is only available in the ENTERPRISE EDITION of SQL 2008, this is typically much more expensive than the standard edition

  • http://johnsterrett.com johnsterrett

    Trevor brings up a good comment. Backup compression is only available in the enterprise edition of SQL 2008.

  • http://www.google.com AndrewBoldman

    Hi, good post. I have been wondering about this issue,so thanks for posting.