SQL Server Utilities - Compressing SQL Server backups using 7Zip4Powershell

When maintaining client’s systems, I tend to pay extra attention about SQL Servers backups, being a ware that the data in most cases is the organization’s most important asset. With that in mind I always look for the extra layer of protection I can provide so that data is protected against any potential threat and that can be a human error, data corruption, Ransomware etc.

For example, If the SQL Server instance is a vm located at Azure then the simplest way to achieve this goal is to point the backups to Azure Blob Storage destination rather than a local drive connected to the vm.

But for an on-prem SQL Server instance this approach would be to move backups offsite and for that purpose I have coded a Windows service named “CloudUploader” that uses the Amazon API to upload sql server backup files to Amazon s3 storage and the Azure API to upload files to Azure Storage.

Of course, the client network connectivity needs to support the amount of volume/traffic that needs to be uploaded and backup policy may need some adjustment such as a full database weekly instead of daily (with daily differentials and ongoing t-logs).

In cases where the client uses sql server web edition which does not support backup compression I use PowerShell to compress the backup files prior to the upload.

The simplest way to compress files with PowerShell is using 7Zip4Powershell.

When using PowerShell with 7Zip4Powershell for the first time I have tested the various compression options that are available such as GZip and Tar but the best performing compression algorithm by far was SevenZip.

The bellow table shows a test result using 7Zip4Powershell while selecting the SevenZip compression algorithm with the various compression options (compression level). I needed this in order to make a wise decision and balance between the need to keep file size as small as possible to reduce upload time while on the other hand shortening compression time as possible to reduce CPU load off the host machine.

It turns out that best value/performance compression method is the Low compression that provided a pretty good result (168 mb) in only 37 seconds. The file I used for all tests is a none compressed sql server backup sized 4.4GB (4443 mb).

The PowerShell script that I use which also uses log4net to log the operations is located at my small github project here

https://github.com/yanivetrogi/7Zip4Powershell.git