Pros / Cons of Database Backup Compression

Interesting, however post-processing to e.g. ZIP requires I/O bandwidth to create the original (full size) backup in the first place. I've read that that actually uses more CPU than compressing (i.e. within SQL) and thus writing a smaller file all in one step. I suppose you also have the extra downtime of decompressing the ZIP file before restore can start - with the Business Manager marching up and down in your office ... sweating !!!

But if maximum compression is the goal then you need the best compression tool you can get. I expect that SQL is doing a compromise of minimal-CPU at the risk of inefficient compression. When I have used ZIP compression tools with the "maximum compression" settings they take much longer, and usually achieve very little extra compression - in fact if I have a huge file to ZIP-and-transfer I usually use the FAST compression method as the filesize for transfer is not that much bigger, but the time saved on compression (and decompression :slight_smile: ) is significant.

There used to be a tool (forgotten the name now) which was installed as a Driver. Given the location of SQL backups (and BCP output folders etc.) it intercepted the file generated by SQL and compressed it on-the-fly, and similarly decompressed it when read. It was able to optionally use the ZIP file format, so compressed backup files could be used on other servers by just de-compressing with a ZIP tool. Maybe it's died a death now, superseded by the in-built compression etc., it must have been 2010, maybe earlier, when I knew of it. But that still suffered from needing the same I/O bandwidth as an uncompressed backup

1 Like