Introduction to SQL Server 2008 Backup Compression


Microsoft has introduced a new concept “Compression” in SQL Server 2008. Backup Compression is a new feature of SQL Server 2008 which significantly reduces the backup file size and speed up the backup and restore process. By default, this feature is turn off when you install the SQL Server 2008. Backup compression uses a Compression algorithm to compress the data stored on backup media. You can enable the backup compression using sp_configure stored procedure or by GUI.

USE master
GO 
EXEsp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE 

You can do so by setting the server-instance properties by GUI (SSMS).

Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. Let’s see the difference between the backup file size with and without specifying the COMPRESSION. I have tested this scenario on my local machine on AdventureWorksLT2008 database.

Uncompressed backup

BACKUP DATABASE AdventureWorksLT2008 
TO DISK = 'C:\Backup\AdventureWorksLT2008_Uncompressed.bak' 
WITH INIT

------------------------------
Processed 824 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Data' on file 1.
Processed 4 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Log' on file 1.
BACKUP DATABASE successfully processed 828 pages in 1.486 seconds (4.351 MB/sec).

Compressed backup

BACKUP DATABASE AdventureWorksLT2008 
TO DISK = 'C:\Praxair_Shared\AdventureWorksLT2008_Compressed.bak' 
WITH INIT, COMPRESSION

------------------------------
Processed 824 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Data' on file 1.
Processed 1 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Log' on file 1.
BACKUP DATABASE successfully processed 825 pages in 0.605 seconds (10.653 MB/sec).

The Uncompressed Backup process took 1.486 sec to complete the backup process and created 7.08 MB backup file, whereas the Compressed Backup process took just 0.605 sec and created 1.27 MB backup file.

For more information on Backup Compression you can navigate through different blogs or can refer the MSDN on Microsoft… http://msdn.microsoft.com/en-us/library/bb964719.aspx.

Advertisements

2 thoughts on “Introduction to SQL Server 2008 Backup Compression

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s