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!

Technorati Tags: SQL Server 2008,backup compression

Comments

• Anonymous
  December 05, 2007
  The comment has been removed
• Anonymous
  December 05, 2007
  The comment has been removed
• Anonymous
  December 08, 2007
  So why wouldn't you compress your backups? Two reasons:

1. If you don't have the Enterprise Edition of SQL Server 2008 (to create the backups, restoring works on any edition).
2. If the content of the database is already compressed. If you create a database using the Production.ProductPhoto from the AdventureWorks database and replicate the same data of the table a few times you can notice that the compressed backup can take more time than a normal backup. (I did that for 10000 rows).

• Anonymous
  December 11, 2007
  Christian

1.  I don't have the definitve list yet of what is in enterprise and what's not so you may well be right, but I can't confirm yet. 2.Fair point, also I haven't tried this with similar data in filestream to see what happens there. AF

• Anonymous
  February 22, 2008
  I had a chance to try this compression in sql server 2008… Such ability looks very interesting but this compression mechanism is not ideal. In this case, I'm still only into 3rd party solutions. For sql databases backups LiteSpeed works quite well for me. The backup process is much faster and smoother. In addition it includes such useful features like, for example, encryption or restoring individual objects.

• Anonymous
  February 22, 2008
  Jeremy, Compression ratios are about the same as lightspeed, but you can't compress encrypted databases, or apply encryption to the backup in SQL Server 2008.   As far as restoring individual objects goes then I am not sure how that would play with referential integrity, but I think this is also possible in data protection manager which will also protect the file system sharepoint and exchange

• Anonymous
  May 08, 2008
  Does backup compression have any benefits with log shipping?

• Anonymous
  February 02, 2009
  Does 2008 use sqlvdi to do compression? We have LiteSpeed problems on a VLDB supposedly because of sqlvdi.

• Anonymous
  October 12, 2009
  Why not to use compression: In my environment I leverage data deduplication. So I can take a native backup, place it into my deduplication solution, and every copy after that only stores the actual unique blocks of data. Compression techniques (of various manufacturers) seldom write data the same way twice. To be honest, this is expected as a small change in a table will change that tables final output, which may be in the middle of the data stream. Because of this, the deduplicatability if the data becomes less possible, as many blocks of very similar data now looks like new unique blocks to the deduplication solution. Actually, the same holds true to non-compressed data if it is not written out the same way twice over time. But compression techniques can really compound this issue drastically (encryption is even worse).

• Anonymous
  October 16, 2009
  BShereood intersting but why do you need to do this?