Enable backup compression by default?

Question: We’ve just upgraded to SQL Server 2008 R2 Standard Edition and we’re looking forward to saving backup space using the native backup compression feature. I’ve heard that I should never enable it at the instance level. Can you explain why?

Answer: The SQL community was really pleased when Microsoft listened to everyone’s feedback and made backup compression available in Standard Edition of SQL Server 2008 R2 (it was only available in Enterprise Edition in SQL Server 2008).

I like backup compression not just for the space savings, but for the time savings during backup and restore operations. During a restore, the more compressed the backup is, the shorter the restore time (generally) and so the more precious downtime is reduced.

The catch is in the phrase ‘the more compressed the backup is’. Just because the backup compression algorithm ran, it doesn’t guarantee that any compression was able to be done of the backup data. The compression algorithm used is proprietary to Microsoft, but like most common compression algorithms, the amount of compression achieved depends on what is being compressed.

For instance, mostly a database containing nearly empty data file pages might compress well, but a database that has transparent data encryption enabled (and so is a stream of small, random integers) will not compress at all. There’s no telling how much compression will be achieved without trying backup compression for each database.

The downside of trying to compress uncompressible data is that the CPU cycles are still used to run the compression algorithm, even when the compression achieved is zero.

For this reason, it can be detrimental to enable backup compression by default at the instance level without first checking which backups are not worth trying to compress. What the threshold is for ‘not worth it’ is going to vary based on achievable space savings, how desirable it is to save space in a company, and how CPU-bound the SQL Server instance is that will be performing the backups.

If you find that the majority of the backups on the instance compress enough for your needs, it can be easier to enable backup compression at the instance level and then specify WITH NO_COMPRESSION for those few backups you do not want to waste CPU cycles trying to compress. And of course the opposite is true – you can leave backup compression disabled at the instance level and specify WITH COMPRESSION for those backups you do want to compress.

To summarize, what you’ve been told is incorrect. It’s perfectly feasible to enable backup compression at the instance level – you just need to make sure that you take account of each database’s compressibility appropriately.

Learn more from Michael K. Campbell's "Don't Forget about Backup Compression."

Discuss this Blog Entry 1

on Apr 12, 2012
Choosing whether to enable backup compression at the instance level is important if your backup is using the Full, Differential, and Log modes paradigm. If, however, you're using an online backup service for your SQL server that uses sub-file level technology to scan the database byte-for-byte to find database pages that have changed, and only transmits those pages to the offsite backup location, then you don't have to worry about compression at all. Read more about doing SQL server backups online here: http://info.zetta.net/2-totally-ways-sql-server-backup-recovery/

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.


Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×