SQL Server 2008’s backup compression feature allows IT pros to save disk space when making full SQL backups. Database administrators, as well as SQL administrators, have long preferred to have a SQL backup (or a flat file) on disk coupled with transaction log backups on disk for full point in time recovery.

Compressing the SQL backups will save you space, albeit at the expense of increased CPU and disk I/O compared to an uncompressed backup. There are a number of ways to enable SQL backup compression, and Tim Chapman’s TechRepublic article shows how to enable it with SQL scripting.

Many customers were upset that this feature was only available with the Enterprise edition of SQL Server 2008. With the R2 release of SQL Server 2008, the Standard edition now has the compressed backup feature. Beyond configuring a compressed backup with SQL code as Tim explains, administrators can specify options within the SQL Server Management Studio on how to manage compression. This is a relief to me, as I usually prefer the user interface.

There are two main approaches to configuring backup compression: a SQL Server value or a per-job value. The SQL Server value is set in the Properties of the database server (Figure A).
Figure A

Click the image to enlarge.

The other way to configure a compressed backup is via the explicit job configuration (Figure B). This can be specific for a designated database or allow a backup to go against the server policy for the I/O and CPU reasons mentioned earlier in the tip.
Figure B

Click the image to enlarge.

Your mileage may vary when using compressed backups so take a peek inside your databases to see how well they respond to the compression feature. Generally speaking, if you only get 4-6% compression benefit in terms of storage reduction, it may not be worth the extra disk I/O and CPU cycles. On the other hand, a 90% compression benefit may be worth the extra work on the server.

Do you use the SQL compressed backup feature? If so, share your comments about this feature.

TechRepublic’s Servers and Storage newsletter, delivered on Monday and Wednesday, offers tips that will help you manage and optimize your data center. Automatically sign up today!