SQL Server 2008 Backup Compression
– by Varun Dhawan, over at MSDN blogs.
It’s a useful option for saving space with backing up to disk, but I would disagree with the guy over tape backup savings.
Some tape units have built-in hardware compression which already compresses the data being stored on tape.
Which means there is no advantage in compressing data if you are going to be writing it straight to tape.
As I found when testing compressing large ISAM databases back in the 90’s. The data was already compressed so the tape drive couldn’t compress any further.
So the answer is:
Yes, if backing up to disk.
No, if backing up straight to tape.
Details on tape compression from an old Sony FAQ:
Q: What is the tape capacity of the SDT- 5000/5200 tape drive?
A: With the SDT-5000, tape capacity will vary depending on the length of the tape and whether hardware compression is enabled as follows:
|Tape||Native Capacity||Typical Capacity with Compression|
|60m||1.3 GB||2.6 GB|
|90m||2.0 GB||4.0 GB|
|120m||4.0 GB||8.0 GB|
NOTE: The SDT-5200 supports native capacity only.
The typical performance of the compression IC in the SDT-5000 drive is shown in the following table:
|Bitmaps||6.9 to 1|
|Database||3.9 to 1|
|CAD||3.8 to 1|
|English||2.9 to 1|
|Source||2.9 to 1|
|Spreadsheet||2.5 to 1|
|Desktop Pubs||2.5 to 1|
|Binary||1.7 to 1|
As the table indicates, the more random the data is, the less compression is possible. This is due to the fact that data compression operates on the principle of reducing the redundancy in the date string and random data has very little redundancy.