Varun Dhawan's Blog

SQL Server 2008 Backup Compression

I’m still in process to make myself familiar with latest SQL Server release i.e. SQL Server 2008. To begin with, I concerted on new features/enhancements related to database administration and came across with this interesting new feature - SQL Server 2008 Backup Compression.

In the past few years, there has been a tremendous growth in database sizes and consequently a steep increase in overall spending on datacenter space. While as a DBA, we may have limited measures to counter data growth, we however have major control on space usage when it comes to dealing with database Backups and Restores (particularly with large databases).

SQL Server 2008 offers you capability of compressing your database backups *. The idea behind compression is simple: To Save Space and allow the backup of more data onto a given media set.

Let’s have a closer look at this exciting new feature; SQL Server 2008 Backup Compression

By default, the ‘Backup Compression’ is set OFF. This can be set to ON (enabled) from Management Studio GUI (refer screen) or with T-SQL Command (refer code)

-----------------------------------------------------------------------------------

USE master;

GO

EXEC sp_configure ‘backup compression default’, '1';

RECONFIGURE WITH OVERRIDE;

-----------------------------------------------------------------------------------

Once enabled, all the subsequent backups will be compressed by default.

Note: If in case you don’t want to enable the ‘Backup Compression’ at server level, you can override this setting for a specific backup job.

Now, let’s understand how ‘Backup Compression’ works. I’ll illustrate with a simple example of manually enabling the backup compression for a specific backup job.

Example

• I have a database named ‘A_large_database’ of size 1997.94 MB’s
• I will now take a database backup with below T-SQL. REMEMBER: The default compression is NOT enabled.

----------------------------------------------------------------------------------

BACKUP DATABASE A_large_database

TO DISK = 'D:\tempdb\Before_Compression.bak'

GO

----------------------------------------------------------------------------------

• The size of backup file created is 4272 KB’s
• Will now try taking a backup with compression

----------------------------------------------------------------------------------

BACKUP DATABASE A_large_database

TO DISK = 'D:\tempdb\With_Compression.bak'

WITH COMPRESSION

GO

----------------------------------------------------------------------------------

• The size of backup file created is 775 KB’s i.e. Just 18 % of our previous backup files size.

Immediate Benefits:

1. Less storage and Tape requirements

2. Reduced spending (due to point 1)

3. Faster database recoveries - will cover this in detail in my subsequent blog post, “How to achieve faster Database recoveries using SQL Server 2008 Backup Compression”

All in all, SQL Server 2008 Backup Compression is a very useful feature and can be adopted in your environment without much change to your applications. For more details, please refer Backup Compression >> http://technet.microsoft.com/en-us/library/bb964719.aspx

* This feature is currently available only in SQL Server 2008 Enterprise Edition, however, all SQL Server 2008 edition can restore a compressed backup.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.