How much should we compress SQL Server backup

SQL Server Standard and Enterprise Edition starting from version 2008 has option to compress backup file.

But if you are using SQL Express, you are stuck with standard backup. Another caveat to SQL Express is the lack of SQL Agent.

I usually set up SQL Express backup using task scheduler and compress the resulting backup file using 7zip with ultra compression before sending the compressed file to offsite storage.

Recently while debugging slowness in several SQL Express databases, I revisit the compression strategy.

There are tradeoff between compression ratio vs CPU/Memory/Time consumed. And based on my testing on 7zip version 19.00, the tradeoff is quite significant.

For this benchmark I use 2 backup files

1. Full backup with size 763785216 bytes (728.40 MB)
2. Differential backup with size 37342720 bytes (35.61 MB)

compression is using 7zip command line switch 

-mx[1-9] -mmt=[on|off] -bt

 Below is the result and some pretty graphs to better visualize the data

Full Backup Compression

Differential Backup Compression

Full Backup Memory Usage Compression

 

Differential Backup Memory Usage Compression

Full Backup Compression Time

Differential Backup Compression Time

Full Backup Compression CPU Cycles

Differential Backup Compression CPU Cycles

So if your objective is to compress faster, then -mx4 -mmt=on , should satisfy your requirement

But if time is not of concern then -mx=5 -mmt=on , should be enough

For me, I will stick with -mx=5 -mx=on from now on.