Disk space may be cheap but it is still not free! And, while SQL Server 2008 supports online compression of backups, it is only available in the Enterprise edition. So I wrote a simple powershell script (more like a snippet) to compress any backup (.bak and .trn files) older than 2 days.


cd E:\MSSQL\BACKUP\SalesLogix
dir *.trn,*.bak | where { $_.CreationTime.CompareTo([DateTime]::Now.AddDays(-2)) -lt 0 } |% `
{ & 'C:\Program Files\7-Zip\7z.exe' a "$_.zip" $_; rm $_ }


Could replace “.zip” with “.7z” to do a 7-zip compression – it will take a bit less disk space but more cpu. Could also be tweaked a bit to support recursion.

I saved that to E:\MSSQL\Backup\ZipBackups.ps1 and created a schedule task to invoke “powershell E:\MSSQL\Backup\ZipBackups.ps1″. This requires the execution policy to be set on powershell, to allow unsigned local scripts:


set-executionpolicy RemoteSigned


I am still working on getting more familiar with powershell as it can be a nifty tool (and is becoming more and more standard on Windows servers as it is bundled with other packages)