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.
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.