Encrypting Sql Server Backups using 7-Zip & Powershell

If like me, you’re moving off using litespeed compression to using native Sql Server 2012 compression, there is one feature that you’re likely to miss; encryption.  Here is how you can use 7-zip via powershell to implement encryption (the default encryption for 7-zip is AES-256).

if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"}
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe"

$path = "C:\PathToBackupFolder"
$password = "TestPassword"

$files = gci -Path $path | where{$_.Extension -eq ".bak"}
foreach($file in $files)
{
	$NewFile = "$path\$file" -replace ".bak",".bak.7z"
	$cmdOutput = sz a -tzip "$NewFile" $file.FullName -p$password
	$cmdOutput[8]
	if($cmdOutput[8] -eq "Everything is Ok")
	{
		Remove-Item -Path $file.FullName
	}
	else
	{
		Send-MailMessage -To "whomever@wherever.com" `
		-Subject "Error in compressing file on ServerName" `
		-Body "There was an error in compressing the file <b>$file</b> on ServerName.  Please look into it." `
		-SmtpServer "mail.wherever.com" `
		-From "server@reedbusiness.com" -BodyAsHtml
	}
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: