If you are using SQL Server 2008 enterprise/datacenter edition, or SQL Server 2008 R2 standard or above, you are in the lucky possition to be able to compress your SQL Server backups. Often I see installations of these versions, where compression is not enabled. Let me show you a couple of ways to do that using the built-in backup features.

Command line

I have a test database called TPCH, and these are the scripts to perform a full backup without compression, and one with compression:

--Backup without compression BACKUP DATABASE [TPCH] TO DISK = 'c:\SQLBackup\TPCH_uncompressed.bak'
GO --Backup with compression BACKUP DATABASE [TPCH] TO DISK = 'c:\SQLBackup\TPCH_compressed.bak' 
WITH COMPRESSION
GO 

To see the difference, I can look in msdb.dbo.backupset, which shows you both the backup_size without compression, as well as the compressed_backup_size:

select 
database_name, 
backup_size, 
compressed_backup_size,
 (backup_size-compressed_backup_size)/backup_size*100 AS SavingsInPercent 
FROM msdb.dbo.backupset 

The output from this is:

My first backup show the same value in both backup_size and compressed_backup_size columns, and that is because bacup compression wasn’t enabled. The second one shows two different values, and a calculated saving of 58%. This value varies depending on the content of the database, but it’s not uncommon to see savings in the 80-85% area. Not bad, considering the small effort for enabling it

GUI

If you are using the GUI to take a manual backup, you will see the standard backup windows. If you go to the Options pane, you will see this:

Notice the compression part in the bottom. The default setting is “Use the default server setting”, which is “Do not compress backup”. You can select the “Compress backup”, and achieve the same result as with the “WITH COMPRESSION” in the t-sql script. But hey! If there is a “use the default server setting” option, then how can we change that? Simply right click the sql server, go to properties and open up the “Database Settings” tab:

Here you see the “Compress backup” checkbox. If you tick this, the backup’s will automatically be compressed, unless you specifically choose not to perform compression. Pretty nifty!

IBM TDP for SQL Server

If you are using IBM Tivoli Data Protection for SQL Server to perform your database backups, the default server setting above is unfortunately ignored. But if you are running TDP 5.5.4 or newer, you can actually enable it anyways. TDP for SQL Server ususally has the following root directory: c:\progra~1\Tivoli\TSM\TDPSql\ and in here you find TDPSql.cfg. In this file you simply add this line to the bottom:

SQLCOMPression          YES

Now restart your scheduler service, and you are good to go. If you would like to verify your own savings after enabling this, you can use the script from before.

Benefits

There are a few things worth mentioning. If you enable backup compression, you put extra load on the SQL Server cpu, because it needs to perform the compression. But at the same time you offload your network, because you only need to transfer 15-50% of your usual data volume. And with smaller backups you have room to hold more backups on your storage system.

Depending on your setup, the backup and restore speed will probably be a lot better as well. The backups will be faster because you need to transfer and write less data to your destination. And restores will be faster because you need to read and transfer less data from your backup source.