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”