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- Backup Compression

  • Comments 10
  • Likes

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!

  • As I was romping through Page and Row compression last night (while at the same time losing my voice

  • As I was romping through Page and Row compression last night (while at the same time losing my voice

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

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


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

  • 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

  • Does backup compression have any benefits with log shipping?

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

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

  • BShereood intersting but why do you need to do this?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment