In SQL server with enterprise edition, there are databases with all the tables/indexes compressed. Does it still make sense to do compressed backup?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.