7-zip Backup Compression for SQL Server Standard

By | March 9, 2011

We have a vendor right now that periodically requests a backup file FTP’d to their server. Unfortunately we do not run the Enterprise version of SQL Server to be able to compress the backups, and nobody wants to manually take a backup file and compress it on a regular basis.

Because of this I created an SSIS job that utilizes one of my favorite freebies, 7-zip, to uber-compress the backup file.

Here’s how:

First you’ll need to download the command-line version of 7-zip: http://downloads.sourceforge.net/sevenzip/7za920.zip

Then I extracted the 7za.exe file to c:\zip

Create a new SSIS project in Visual Studio.

Add an Execute Process Task.

Edit the Process Task as shown:

The arguments I’m using are: a -tzip (for zip) -mx9 (maximum compression) <output path and filename> <path to backup>
The executable is pointed to the command-line version of 7-zip.

Run the SSIS task. Will zip up the file (will take a while).

The AdventureWorks backup was 187k uncompressed.
After compression it was whittled down to 35k and ready to FTP.

Leave a Reply

Your email address will not be published. Required fields are marked *