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)


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.

3 responses to “Backup Compression with SQL Server 2008

Leave a Reply

Your email address will not be published.

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.

This site uses Akismet to reduce spam. Learn how your comment data is processed.