Hello techies ,

This is almost after year back to my blog , I have lot of notes to jot down on my blogs here is one interesting topic

We come across situation to backup MSSQL databases and upload to S3 and schedule it to run midnight for daily backup

There are options to get this done , one of best solution is PowerShell Script

To upload files on amazon s3 , you need to install AWS Powertools

(Optional) To zip you need 7-zip   (you can use other alternatives but you will need to alter your script accordingly , and if you have powershell5 you don’t need 7-zip will show you when we just right there 🙂 )

So ,  I have started with retrieving MSSQL database and loop through the dbs to back them up .. zip them and finally upload

Here is the script

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$server = ‘YOUR SQL INSTANCE’
$s3Bucket = ‘S3 Bucket’

$backupPath = ‘PATH FOR BACKUP’

$accessKey = ‘S3 ACCESSKEY’
$secretKey = ‘S3 SECREAT KEY’

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server
$databases =$s.Databases

foreach ($sqlDatabase in $databases) {
$database = $sqlDatabase.name
if($sqlDatabase.IsSystemObject -eq $False) #skip system dbs
{
$s3folder  = ‘/FOLDER PATH IF YOU ARE SAVING TO SPECIFIC DIRECTORY ON S3/’

$timestamp = get-date -format yyyyMMddHHmmss
$bakfileName = “$database-$timestamp.bak”
$zipfileName = “$database-$timestamp.zip”

$bakfilePath = Join-Path $backupPath $bakfileName
$zipfilePath = Join-Path $backupPath $zipfileName
$s3folder = Join-Path $s3folder $zipfileName
Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $bakfilePath

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”

#Zip the bak to save space on s3

sz a  -mx=1 -mmt=off $zipfilePath $bakfilePath

Write-S3Object -BucketName $s3Bucket  -File $zipfilePath -Key $s3folder  -AccessKey $accessKey -SecretKey $secretKey
Remove-Item $bakfilePath   #Remove Bak File
Remove-Item $zipfilePath     #Remove Zip File
}
}

Let me know your results in comments. Have a great weekend !