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
- Connect to the SQL Server 2008 Instance using SQL Server Management Studio
- In Object Explorer, right click the server and select properties to view the Server Property window
- 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 - 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:DatabaseBackupsSampleDB_UnCompressed.Bak’
WITH INIT, STATS = 20
