Rotating Header Image

SQL Server 2008 – Backup compression

Backup compression is something new to SQL Server in SQL 2008 and it’s something that products like SQL LiteSpeed will be quite interested. Using a sample database I ran the following scripts and listed the results below:


DECLARE @StartDateTime DATETIME = GETDATE()
BACKUP DATABASE <<DatabaseName>>  TO DISK = 'D:\NotCompressed.bak' WITH INIT, COPY_ONLY
SELECT DATEDIFF(ms, @StartDateTime, GETDATE())

SET @StartDateTime = GETDATE()
BACKUP DATABASE <<DatabaseName>>  TO DISK = 'D:\Compressed.bak'  WITH INIT, COPY_ONLY, COMPRESSION
SELECT DATEDIFF(ms, @StartDateTime, GETDATE())

BACKUP SIZE (my sample db): 129 MB (uncompressed) vs. 35 MB (compressed).

Compression ratio varies depending on what type of data there is in the database (ie string data tend to compress better than other data).

BACKUP EXECUTION TIME (my sample db): 8 seconds (uncompressed) vs. 4 seconds (compressed).

Keeping in mind that this is a small sample database but on a much larger scale these types of savings would be huge.

RESTORE EXECUTION TIME (my sample db): 11 seconds (uncompressed) vs. 6 seconds (compressed).

This is the one that suprised me… I thought there would be more overhead with uncompressing the backup file that would cause the restore time to be longer but the only measure that I saw was that the CPU utilization was alot higher on the compressed backup.

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. Riff lacks the polish of the social network’s official app or its Messenger app. While Riff is very easy and surprisingly fun, we think it would benefit from a design more consistent with Facebook’s other apps.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.