Our development SQL server is a monster…there are many many databases, and hundreds, if not thousands of backup files. With each patch tested on the software we sell, there is a new backup. With each client deployment, a new database. With each new major version, a new database. Backups of the new databases inevitably occur, and so we have more files, in more folders - most of which need to be kept in case of roll-backs, bugs or deployment issues.

This all adds up to a bit of an administrative nightmare, especially since the backups eat away at my storage at a phenomonal rate. Zipping the .bak files is great, but since each DB has it’s own backup folder, it can become a bit of a nightmare to go through, zip and delete the .baks. For my first real foray into using PowerShell, I decided I’d write a script to take the legwork out of it for me.

`
# Powershell Script to recurse input path looking for .bak files, Zip them

and delete the .bak.

#

function out-zip {
Param([string]$path)
if (-not $path.EndsWith(’.zip’)) {$path += ‘.zip’}
if (-not (test-path $path)) {
set-content $path (“PK” + [char]5 + [char]6 + ("$([char]0)" * 18))
}
$ZipFile = (new-object -com shell.application).NameSpace($path)
$input | foreach {$zipfile.CopyHere($_.fullname)} | out-null
}
$FileCount =0
$FilesZipped =0
$FilesDeleted =0
$InputPath = $args[0]
if($InputPath.Length -lt 2)
{
Write-Host “Please supply a path name as your first argument” -foregroundcolor Red
return
}
if(-not (Test-Path $InputPath))
{
Write-Host “Path does not appear to be valid” -foregroundcolor Red
return
}
$BakFiles = Get-ChildItem $InputPath -Include *.bak -recurse
Foreach ($Bak in $BakFiles)
{
write-host “File: $Bak” -foregroundcolor Yellow
$ZipFile = $Bak.FullName -replace “.bak”, “.zip”
if (Test-Path $ZipFile)
{
Write-Host “$ZipFile exists already, aborted.” -foregroundcolor Red
}
else
{
Get-Item $Bak | out-zip $ZipFile
if(Test-Path $ZipFile)
{
$Response = read-host -prompt “Please wait for zip to complete then type c to continue…"
if($Response = “c”)
{
$FilesZipped++
Remove-Item $Bak.FullName
if(Test-Path $Bak.FullName)
{
Write-Host “File not deleted, manually remove $Bak.Fullname” -foregroundcolor Red
}
else
{
Write-Host “OK” -foregroundcolor Green
$FilesDeleted++
}
}
else
{
Write-Host “File delete aborted by user” -foregroundcolor Red
}
}
}
$FileCount++
}
Write-Host Files found: $FileCount
Write-Host Files Zipped: $FilesZipped
Write-Host Files Deleted: $FilesDeleted
`

Obviously, this is not something I’d recommend you running lightly without serious testing on your own systems - that said, I hope it helps! I make no warantee or any kind of promise that you won’t lose data by running this! It’s just an exercise in PowerShell for me.