Backup Compression - SQL Server 2008

Introduction

I would like to say one small story about "SQL Server Backup Compression". Most of us worry about Database Backup Time taken, Free Space required for the backup file, Etc. We have one option called Backup Compression and we are going to discuss about it in this article.

About Compression:

1. Backup compression has been introduced in SQL Server 2008 Enterprise Edition.
2. Though creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore the compressed backup file.
   
   

Advantage:

1. Compressed backup file is smaller than the uncompressed backup of the same size of Database.
2. Compressing a backup typically requires less Device I/O. So, It increases the backup speed and reduces the Size significantly

Disadvantage:

1. Compressed and uncompressed backups cannot co-exist in a single media set.
2. Previous versions of SQL Server cannot read compressed backups(SQL Server 2005/2000/7.0/6.5,...).
3. By default, compression significantly increases CPU usage, and requires additional CPU consumed by the compression activity. So,It might adversely impact all the concurrent operations of the system.
4. Therefore, we might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor
   

I would like to know whether my SQL Server is compatible for Backup Compression or Not !

USE MASTER
GO
SELECT SERVERPROPERTY('ProductVersion') [Version], 
SERVERPROPERTY('Edition') [Edition]
GO

Yes. My Version is 10.0.1600.22 (SQL Server 2008), Edition is Enterprise Edition.

So my version and edition of sql server is compatible for Backup compression.

ok. I have one very small database SQLBuddy in 2GB size.

I would like to take backup this database in both way (Normal and Compressed Mode)

1. Normal Backup:

USE MASTER
GO
BACKUP DATABASE SQLBuddy TO DISK='E:\Backup\SQLBuddy_Normal.Bak' WITH INIT
GO

The result shows like

Processed 151680 pages for database 'SQLBuddy', file 'SQLBuddy' on file 1.
Processed 1 pages for database 'SQLBuddy', file 'SQLBuddy_log' on file 1.
BACKUP DATABASE successfully processed 151681 pages in 82.583 seconds (14.349 MB/sec).

The normal backup took 14.349 MB/sec Speed ro perform the Normal Backup

2. Compressed Backup:

USE MASTER
GO
BACKUP DATABASE SQLBuddy TO DISK='E:\Backup\SQLBuddy_Compressed.Bak' WITH INIT,COMPRESSION
GO

The result shows like

Processed 151680 pages for database 'SQLBuddy', file 'SQLBuddy' on file 1.
Processed 1 pages for database 'SQLBuddy', file 'SQLBuddy_log' on file 1.
BACKUP DATABASE successfully processed 151681 pages in 47.534 seconds (24.929 MB/sec).

The compressed backup is 24.929 MB/sec Speed to perform the compressed Backup, So comparing with Normal backup, The compressed backup transfer rate is 10MB faster in the analysis done above..

I would like to examine the both Normal and Compressed backup activity using the script..

USE MASTER
GO
SELECT Database_Name [Database Name], 
  backup_size [Normal Backup], 
  compressed_backup_size [Compressed Backup],
  (100/backup_size) *compressed_backup_size [% of Backup Size],
  CASE WHEN backup_size = compressed_backup_size THEN 'Normal Backup' ELSE 'Compressed Backup' END [Backup Type],
  DATEDIFF([Second],backup_start_date,backup_finish_date) [Elapsed Time(Secs)]
FROM msdb..backupset WHERE database_name ='SQLBuddy'

The result looks like:

Analysis:

1. Normal backup file size of the same size of Database is 1.16 GB(Almost 99.99% of the actual database), But in Compressed mode is 0.34 GB(Only 29.00% of the actual database)
2. Normal backup took 87 Second(s) to complete the Backup, But in Compressed mode took only 47 Second(s) (Almost 40 Second(s) faster than the Normal Backup)

Conclusion