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
	if($cmdOutput[8] -eq "Everything is Ok")
		Remove-Item -Path $file.FullName
		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

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.