Backup compression in SQL Server 2008

Introduction

Backup compression is one of the feature expected by every administrator to minimize the backup size and to squeeze the time taken for backup. In previous versions of SQL Server most of us are going for 3rd party tools like Quests Litespeed, Redgates SQL Backup etc. for which we need to pay additional amount. SQL Server 2008 adds an additional feature of taking compressed backup through native backup command itself.

Edition supporting Backup Compression

• SQL Server 2008 Enterprise Edition

Pros of taking compressed backups

• Backup size gets reduced
• Less time taken to take backup
• Less tape is required if you feed this backup file to tape
• Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.
• IO will be less compared to normal backup

Restrictions

• Compressed and uncompressed backups cannot co-exist in a media set.
• Previous versions of SQL Server cannot read compressed backups.
• NTbackups cannot share a tape with compressed SQL Server backups.
• CPU usage will be significantly higher compared to normal backup(but lasts for few minutes), this might affect the concurrent processes

Configuration

Compressed backup option is disabled by default. Its a server level configuration and you wont be able to enable for each database. Kindly note that enabling this option will allow new backups to be compressed by default (i.e without using WITH COMPRESSION parameter in backup command). You can override this setting by using WITH COMPRESSION & WITH NO_COMPRESSION parameter in BACKUP command. You can enable the same by two methods T-SQL & GUI. Lets see how to enable

T-SQL

Execute the below query in SSMS to enable backup compression by default

SP_CONFIGURE 'backup compression default',1
GO
RECONFIGURE
GO

GUI

• Open Management studio, connect to SQL Server 2008
• Right click on the server and click on properties
• Go to Database Settings tab and enable Compress Backup Check box as shown below

How to Calculate % Compression ratio

Method 1

You can make use of backupset table in MSDB database. backup_size & compressed_backup_size columns will provide you the necessary details, try the below query

SELECT (((backup_size-compressed_backup_size)/backup_size)*100) FROM msdb.dbo.backupset WHERE database_name='Mydatabasename'

Method 2

• Take a full backup without compression
• Take a full backup with compression
• Use the below calculation

  ((Fullbackup size without compression – Fullbackup size with compression)/Fullbackup without size compression)*100

Example

Lets see this with an example, here Im going to take AdventureworksDW database backup both with compression and without compression and check how CPU is utilizing

Without Compression

Backup Database AdventureWorksDW to disk = 'C:TEMPAdventureWorksDW.bak'

You can see CPU usage and time taken from the below screenshots

Average CPU Usage : 6.375

Time Taken : 9 Seconds for 85MB db

With Compression

Backup Database AdventureWorksDW to disk = 'C:TEMPAdventureWorksDW_Compressed.bak WITH COMPRESSION'

You can see CPU usage and time taken from the below screenshots

Average CPU Usage : 17.94

Time Taken : 3 Seconds for 85MB db

Compression Ratio:

SELECT (((backup_size-compressed_backup_size)/backup_size)*100) FROM msdb..backupset WHERE database_name='AdventureWorksDW'

From the example above you can see that we are attaining 80% compression ratio in 3 sec sacrificing some CPU cost.

Please note that the above values will be varied with respect to the database size, hardware etc.

Conclusion

Backup compression is a nice feature which will allow you to take compressed backups with less time & disk IO sacrificing some CPU cost for this feature.