Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008- Backup Compression

SQL Server 2008 will have the ability to compress a database, but that functionality is not in the latest beta release (CTP5).  However CTP5 does have the ability to compress backups by simply extending the existing backup command:

backup database adventureworks to disk=’C:\abc\adventureworks_compress.bak’
    with compression

What’s interesting about this is that it doesn’t just compress the backup to less than a quarter of the uncompressed size (for this database), it does it fater -  the compressed backup took 8.9 seconds compared to 20.1 seconds for an uncompressed backup on my virtual machine. 

It get’s better as the restore process is also faster – restoring the compressed backup in 12.3s compared to restoring the uncompressed backup in 19.7s.

Note the restore command is the same as in SQL server 2005 i.e. you don’t need to tell restore the backup is compressed.

So why wouldn’t you compress your backups?

I have absolutely no idea!