Backup compression in SQL Server 2008

Backup compression in SQL Server 2008

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/12/12/backup-compression-in-sql-server-2008.aspx

Having a few moments to spare, I decided to give this a spin.

Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. For example:

BACKUP DATABASE  Adventureworks 
TO DISK =  'C:/Advc.bak' 
WITH  INIT ,  COMPRESSION

For fun, I compared backup file size and backup time between compressing and not compressing. I ran below after priming the cache (not that it should matter since backup read pages from disk, see http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/03/does-backup-utilize-pages-in-cache.aspx ). I also deleted the backup files, if they exist, before execution.

DECLARE  @dt  datetime

SET  @dt  =  GETDATE ()
BACKUP DATABASE  Adventureworks 
TO DISK =  'C:/Adv.bak' 
WITH  INIT
SELECT  DATEDIFF ( ms ,  @dt ,  GETDATE ())

SET  @dt  =  GETDATE ()
BACKUP DATABASE  Adventureworks 
TO DISK =  'C:/Advc.bak' 
WITH  INIT ,  COMPRESSION
SELECT  DATEDIFF ( ms ,  @dt ,  GETDATE ())

Size of backup file is 129 MB vs. 35 MB. Of course, compression ratio varies depending on what type of data there is in the database (string data tend to compress better than other data,. for instance). Time to execute commands were 7.5 seconds vs. 3.8 seconds. Now, this is a virtual machine on VPC and Adventureworks is a tiny database. But at least we can see clear potential for savings here.

So how do we know if a database backup was compressed? If you've used RESTORE HEADERONLY, you probably noticed this annoying column called "Compressed". Well, this is the first time we see a value of "1" in that column.

notes: from envykok

RESTORE HEADERONLY FROM DISK ='

C:/Advc.bak'

http://blog.csdn.net/envykok/category/680357.aspx

 

It seems we cannot mix compressed and non-compressed backups on the same file, though. (Not that I often have multiple backups on the same file.) If I append a backup using COMPRESSION on a file were there already are non-compressed backup, I get an error. Or I do a backup without COMPRESSION on a file where there are compressed backups, the new backup will be compressed (even when not saying COMPRESSION). Something to look out for if you have several backups on the backup files.

So what about the RESTORE command? Well, there's nothing to say, really. You don't have to specify in the RESTORE command that the backup was compressed. Time for RESTORE was 10.4 vs 6.3 seconds (with the destination database already existing). I expect the difference to be bigger on real installation and realistic db size.

 

Notes: From envykok

I have given a test on

Back up Excute time : 80,000 ms vs. 38,586 ms (left number is without compress, all belows statistics are same)

Back file size (orignial size : 7.8G) : 2,300 M vs 500 M

Restore Excute time : 310, 753 ms vs. 298, 103 ms

Restore script :

 

DECLARE  @dt  datetime

SET  @dt  =  GETDATE ()
RESTORE DATABASE  Adventureworks 
FROM DISK =  'C:/Adv.bak' 
WITH Replace
SELECT  DATEDIFF ( ms ,  @dt ,  GETDATE ())

SET  @dt  =  GETDATE ()
RESTORE DATABASE  Adventureworks 
FROM DISK =  'C:/Advc.bak' 
WITH Replace

SELECT  DATEDIFF ( ms ,  @dt ,  GETDATE ())