How to configure and Use Database Backup Compression in SQL Server 2008

SQL Server 2008 introduces a new feature called Database Backup Compression. This feature allows DBA’s to compress SQL Server 2008 database backups natively rather than taking uncompressed native SQL Server database backups or using a third-party tool. By default, database backup compression is turned off in the SQL Server 2008. At present this feature is only available in the Enterprise Edition of SQL Server 2008. However, all editions of SQL Server 2008 allow the restoration of compressed database backup.

Using the Database Backup Compression feature, a DBA can perform Full, Differential and Transactional log compressed backups. There are two ways to enable Database Backup Compression (DBC). The first being enabling it at the SQL Server Instance Level and secondly by specifying the WITH COMPRESSION clause at the time of backup. This article provides a step by step guide on how to configure and use the Database Backup Compression feature in SQL Server 2008.

Configure Database Backup Compression Using the GUI

1. Connect to the SQL Server 2008 Instance using SQL Server Management Studio
2. In Object Explorer, right click the server and select properties to view the Server Property window
3. Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups:
   If the Compress backup box is blank, new backups are uncompressed by default
   If the Compress backup box is checked, new backups are compressed by default
4. The user needs to be a member of the sysadmin or serveradmin fixed server role to change the default settings for database backup compression

Configure Database Backup Compression Using TSQL

USE MASTER
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

How to Uncompress a Database Backup using TSQL
The following TSQL code can be used to perfrom an uncompressed full native backup of the SampleDB database which is 277 MB. The time taken to complete the fully uncompressed native backup was 18.287 seconds.

BACKUP DATABASE SAMPLEDB
TO DISK = N'D:\DatabaseBackups\SampleDB_UnCompressed.Bak'
WITH INIT, STATS = 20


How to Perform a Compressed Database Backup using TSQL
The following TSQL code can be used to take the fully compressed backup of the SampleDB database. The time taken to complete the compressed full backup was 6.910 seconds.

BACKUP DATABASE SAMPLEDB
TO DISK = N'D:\DatabaseBackups\SampleDB_Compressed.Bak'
WITH INIT, STATS = 20





In the current scenario we could achieve 90% compression in the database backup size when compared to the native SQL Server Database backups.

You can run the following TSQL Code and look for a value in the Compressed column. If the value is 1 then that means that the database backup is compressed and if it is 0 then it means the database backup is not compressed or it is a native backup.

RESTORE HEADERONLY
FROM DISK = N'D:\DatabaseBackups\SampleDB_Compressed.Bak'

Note: Database Backup Compression significantly increases the CPU usage and as a result it can have slight negative impact on other database operations while the database backups are perfromed. You need to carefully plan the time during which the database backup jobs can run to take the backups.

Restoring a Compressed Database Backup
The following TSQL code will restore the SampleDB database from the compressed backup and it is completed in 8.565 seconds

RESTORE FILELISTONLY
FROM DISK = N'D:\DatabaseBackups\SampleDB_Compressed.Bak'
GO
RESTORE DATABASE SampleDB
FROM DISK = N'D:\DatabaseBackups\SampleDB_Compressed.Bak'
WITH MOVE N'SampleDB_Data'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SampleDB.mdf',
MOVE N'SampleDB_Log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SampleDB_1.ldf',
RECOVERY,
STATS = 20
GO


Restoring Uncompressed Database Backup
The following TSQL code will restore the SampleDB database from the uncompressed backup and it is completed in 15.9891 seconds

RESTORE FILELISTONLY
FROM DISK = N'D:\DatabaseBackups\SampleDB_Uncompressed.Bak'
GO
RESTORE DATABASE SampleDB
FROM DISK = N'D:\DatabaseBackups\SampleDB_Uncompressed.Bak'
WITH MOVE N'SampleDB_Data'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SampleDB.mdf',
MOVE N'SampleDB_Log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SampleDB_1.ldf',
RECOVERY,
STATS = 20
GO



Performance Counters to evaluate the Database Backup Compression
The % Processor Time counter of the Processor Object
The % Disk Time counter & Avg Disk Queue Length of the Physical Disk Object
The Device Throughput Bytes/sec counter of the SQLServer:Backup Device object
The Backup/Restore Throughput/sec counter of the SQLServer:Databases object

About the Author
Ashish Kumar Mehta is currently Software Development Engineer at Microsoft. He has been working in IT for over 5 years and with database for over 4 years.