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.Databasesforeach ($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 $bakfilePathif (-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 !
Thank you. Searching for last 1 month.
Great to hear that! 🙂
Good luck.