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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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


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.