SQL Server 2008 Native Backup Compression

One of the more valuable new features in SQL Server 2008 is native backup compression. This allows SQL server to compress both full and transaction log backups, thereby saving both disk space and disk I/O during the backup (at the cost of some extra CPU pressure).  Backup compression also reduces the time needed to complete a backup or a restore in most cases, since you are usually I/O limited during backup/restore operations. Compressed backups are typically two to three times faster than uncompressed backups.

In testing and production use with many different databases, I have been seeing anywhere between about a two to one up to about six to one compression ratio. You will get lower backup compression ratios if the data is already compressed (by SQL Server data compression or by a client or middle-tier application).  You can also use Resource Governor to limit the CPU utilization of a compressed backup, (although I usually only see about a 10-15% increase due to backup compression on a multi-CPU machine).

This is an Enterprise Edition only feature, although you can restore a compressed backup using a non-Enterprise SKU.  The query below lets you look at your backup history and statistics.

 

-- Look at compressed full backups
    SELECT bs.server_name, bs.database_name AS 'Database Name', 
    CONVERT (BIGINT, bs.backup_size / 1048576 ) AS 'Uncompressed Backup Size (MB)',
    CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS 'Compressed Backup Size (MB)',
    CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
    CONVERT (FLOAT, bs.compressed_backup_size))) AS 'Compression Ratio', 
    DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS 'Backup Elapsed Time (sec)',
    bs.backup_finish_date AS 'Backup Finish Date'
    FROM msdb.dbo.backupset AS bs 
    WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 
    AND bs.backup_size > 0
    AND bs.type = 'D' -- Change to L if you want Log backups
    ORDER BY bs.backup_finish_date DESC

 

Technorati Tags:
This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to SQL Server 2008 Native Backup Compression

  1. Karl Fasick says:

    Thanks again, Glenn, I needed a way to calculate churn for DPM sizing. I couldn’t just add up the .TRN files since they’re compressed. I found your post and query right away and got my result.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s