SQL Server Backup Compression

If you’ve played in the IT Pro playground long enough, I’m sure that you have seen the database that is impossible to backup. This is that database in your organization that continues to grow and grow and your backups take longer and longer. Next thing you know, you are using two tapes, then three, and possibly more. Help has arrived! A new SQL Server 2008 Feature called:

SQL Server 2008 Database Backup Compression

Who’s it for? DBA’s and IT Pros who backup and restore databases in SQL Server 2008.

When does it ship? Backup Compression is part of SQL Server 2008 and will ship as part of the Enterprise Edition of the SQL Server 2008 product.

What does it do? It compresses backups. But how much? Well the simple answer is that “it depends”.

Compression Ratio Factors:

· Data Type: Random Data and GUID’s don’t compress nearly as well as character data.

· Encryption: Encrypted data compresses significantly less (if at all) than unencrypted data.

· Database compression: Just like encryption a database that is compressed may not compress for backups.

Based on those factors database compression ranges in the 50-70% range.

What is the performance impact? When database compression is enabled database backups are smaller, however, that compression does significantly increase CPU utilization. But don’t let that scare you off completely. Backup compression also has the effect of reducing the amount of time to backup. I have seen demonstrations where the CPU utilization increase is around 25-30% during the backup/compression operation, however the backup run time was reduced in the 45% to 50% range. Paul Randal over at SQL Skills has a great blog post that shows visually the impact of database compression on both backup and restore operations. (https://www.sqlskills.com/blogs/paul/2008/01/09/SQLServer2008BackupCompressionCPUCost.aspx) So even though you have higher CPU utilization, your backups run in a shorter time.

What do I need to be aware of before I start compressing my backups?

· Have I mentioned that the ability to compress a backup is a feature that is only available in the Enterprise Edition?

· Any edition of SQL Server 2008 can ‘decompress’ a compressed backup.

· You cannot mix compressed and uncompressed backups in a single media set. Make sure you are putting your compressed backups together and your uncompressed in another separate set of media.

· Make sure that the compression is worth the CPU performance hit.

How do I set compression on my backups?

· In SQL Server Management Studio, in the options pane of the backup database task wizard you can select encryption.

· In the Define Backup Database Task Window of the Maintenance Plan Wizard

· Using Integration Services define a package to backup databases.

What about encryption and Backup Compression? Backup Encryption can be accomplished by enabling Transparent Data Encryption and then taking a backup. However when you enable encryption you lose the ability to compress the backup. Unfortunately backup compression and Transparent Data Encryption are mutually exclusive.  It is not recommended to enable database backup compression when using Transparent Data Encryption.

Get started

Backup Compression on Microsoft TechNet

SQL Server Books Online (CTP 6 Version)

Technorati Tags: SQL Server 2008,SQL Server,SQL,Microsoft SQL Server