Explore SQL Server 2008 Backup compression

Problem

Database backup and maintenance windows for large databases are always blocked, especially when we need a full database backup to initialize a database mirror or transaction log shipping session. When we browsed the new features of SQL Server 2008, we found that a feature called database backup compression can be used to significantly reduce backup and restore operations. Can you give us a detailed description of how to use it?

Expert answers

Database compression is a new feature of SQL Server 2008 that can significantly reduce backup and restore operations. By default, backup compression is turned off at the server instance level. Unlike other Third-party software that provides different levels of compression, SQL Server 2008 provides only one level.

In this article, we'll look at how to perform database backup compression and compare the backup and restore times of a normal database backup with the backup and restore time of a compressed database backup, and the size of the backup file. Let's start by creating a full backup of the AdventureWorks database, which is about 178.75 MB in size. I chose to use a TSQL script for backup and recovery, so I can see how much time it took for this process. I'm running on a VMware with 2GB RAM, so it might be different from running on a physical server.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\SQLData\AdventureWorks_uncompressed.bak'
WITH INIT, STATS = 10

By default, the uncompressed backup data process in my example took 29.2 seconds and the backup size was 132 MB.

Figure 1

Figure 2