Backup Compression in SQL Server 2008

All SqlServerCurry.com readers, welcome to the year 2011!

I thought of starting this year with a relatively new and exciting topic – Backup Compression in SQL Server 2008! Backup Compression is a new database administration feature in SQL Server 2008 and higher which allows you to compress your database backup. In this article, I will list some frequently asked questions (FAQ) about Backup Compression in SQL Server 2008.

Advantages and Disadvantages of Backup Compression

Backup compression reduces the amount of data SQL Server 2008 has to write, thus leading to lower disk space consumption, faster backup speeds, quicker restores and quicker mirroring. Backup compression is a boon especially while mirroring your live databases!

However implement Backup compression with caution. The CPU usage becomes very high at times while the backup is being compressed. So always remember to run the backup in a low-priority session. Check this article for more info How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

Note: As of now, you cannot use Backup compression and Encryption at the same time. This is probably because encrypted data is already compressed. However RedGate’s SQL Backup is an awesome alternative.

Which Edition of SQL Server 2008 supports Backup Compression?

There seems to be some confusion amongst SQL Server users as to which version of SQL Server 2008 supports Backup Compression. Here are some pointers:

- If you are using SQL Server 2008, then Backup Compression is available only in SQL Server 2008 Enterprise Editions. However a compressed backup can be restored in any edition of SQL Server 2008.

- If you are using SQL Server 2008 R2, then Backup Compression is available in SQL Server 2008 R2 Standard, Enterprise and DataCenter Editions. A compressed backup can be restored in any edition of SQL Server 2008 and higher versions (R2).

How to Enable Backup Compression?

By default, backup compression is off i.e. ‘backup compression default’ is set to 0. However you can configure the server instance to compress all backups by default by using the following code Here we are using 1 to enable backup compression.

Once you have configured the server instance to compress all backups by default, you can override this option for specific backups using either WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement.

How to calculate Compression Ratio of Backups?

To calculate the compression ratio of a backup, divide the original ‘backup_size’ by the ‘compressed_backup_size’ column of the backupset history table, as shown below:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the type of data you have compressed.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook