Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL server with enterprise edition, there are databases with all the tables/indexes compressed. Does it still make sense to do compressed backup?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Yes, in my opinion you should still use compression when backing up, even if the data is compressed. (Exceptions noted below.)

The compression used during backup is able to work across all of the data, unlike page and row compression, which are constrained to only considering the data in each individual page, one page at a time. Think about a phone book in a large metro are: imagine the difference between being able to use dictionary compression for all instances of the word Smith on one page, and then for all instances of the word Smith across all pages. That's similar to the improvement backup compression can add on top of already compressed data.

In my testing, using backup compression on top of page/row compression always yielded smaller backups and completed faster. There is also a lot of interesting commentary where others have confirmed the same. I don 't want to copy all of that here but you can read it at the following URL:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/03/15/yes-you-can-benefit-from-both-data-and-backup-compression.aspx

There are exceptions, of course: if your system is already heavily CPU-bound, compression isn't free, and it may make that problem worse. Also if your databases are encrypted with TDE, you'll find that the compression has little to no effect, because the output of that encryption is not a good candidate for further compression.

share|improve this answer
1  
Also compression i the database is limited to certain low power algorithms. For obvious reasons (speed). –  TomTom Oct 26 '13 at 5:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.