Compressing SQL Server Backups With Windows PowerShell and 7-zip

We run monthly full backups, daily differential backups, and transaction log backups every hour. The backup drive fills up quickly. So I decided that I wanted to compress all of the .bak and .trn files into their own .7z files using 7-zip. Doing something like this in a bash shell is trivial. Windows PowerShell makes it trivial as well (as long as you have it installed). Here's what I did:


get-childitem -recurse |
where { $_.extension -match ".(bak|trn)" -and
-not (test-path ($_.fullname -replace "(bak|trn)", "7z")) } |
foreach { F:\7za.exe a ($_.fullname -replace "bak", "7z") $_.fullname }


Here's a breakdown of what's going on:

get-childitem -recurse: Retreives all files recursively from the current directory

where { $_.extension -match ".(bak|trn)" -and -not (test-path ($_.fullname -replace "(bak|trn)", "7z")): Filters the filelist to only include files that end in .bak and .trn and also where there isn't already a file with the same name but with an extension of .7z

foreach { F:\7za.exe a ($_.fullname -replace "bak", "7z") $_.fullname }: Run the 7za.exe command line utility to add the .bak or .trn into a .7z file.

After this command completed, I ran the following command to remove all of the original .bak or .trn files if they have a corresponding .7z file:


get-childitem -recurse |
where { $_.extension -match ".(bak|trn)" -and
(test-path ($_.fullname -replace "(bak|trn)", "7z")) } |
foreach { del $_.fullname }


By the way, the 7-zip command line utility is good about deleting .7z files that were not properly created (e.g. you cancelled the compression before it finished.)