Tuning SQL Server 2008's Backup Compression

SQLCAT - Tuning the Performance of Backup Compression in SQL Server 2008

"...

Overview

Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not. They also apply to restore operations; however, restore will not be covered in depth in this document. For an introduction to the backup compression feature, see Backup Compression, in SQL Server Books Online.

Benefits of Backup Compression

One major benefit of backup compression is space saving. The size of the compressed backup is smaller than that of the uncompressed backup, which results not only in space savings, but also in fewer overall I/O operations during backup and restore operations. The amount of space you save depends upon the data in the database, and a few other factors, such as whether the tables and indexes in the database are compressed, and whether the data in the database is encrypted.  ...

...

Table 1 shows the output of the above query after taking an uncompressed and a compressed backup. Database Name Uncompressed Backup Size (MB) Compressed Backup Size (MB) Compression Ratio Backup Time (Seconds) Comments BCTEST 292705 95907 3.05 1705 Compressed backup BCTEST 292705 292705 1 3348 Uncompressed backup

..."

The new compression features are those that will push me to push for quick deployment of SQL 2008 (well that and the new transparent encryption, Policy Management, data types, PowerShell and other coolness in 2k8 ;)

This SQLCAT article is a very nice in-depth one which covers a good bit of the inner workings of this feature as well as tuning suggestions (hard to imagine given the title isn't it?... :)