January 01, 2011

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.

SQL Server 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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

2 Responses to "Backup Compression in SQL Server 2008"
  1. Jayaprakash Raman said...
    January 4, 2011 at 10:49 PM

    Earlier i was using Sql Server 2008 standard edition. There is no compression feature available for 2008 Std Edition. It is available in 2008 Enterprise Edition only. I thought the same is carried in 2008 R2 also. But after reading this article i realized that backup compression is available in Sql Server 2008 R2 std, ent and Data Center editions. Thanks for info. I tried this in my machine and found working fine. Thanks a lot for this article/ useful info .

  2. Sriram said...
    January 13, 2011 at 10:55 AM

    Is there any calculation with us for calculating the cpu cost when we are enabling backup compression, if we have some calculation for that also it would be easier for us to decide and see if we should really go for it with the current infrastructure or not.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions