Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Powershell Sql Backup To AWS S3
# Needed PS packages
# Install-Package -Name pscx
# Install-Package -Name AWSPowerShell
#
# Scheduling
# The script then needs to be scheduled to run every night, I’m using scheduled tasks for this,
# creating a task that runs nightly and triggers the powershell script by running
# Powershell.exe with the arguments 
# -ExecutionPolicy Bypass C:\SqlBackup\SqlBackupToS3.ps1.
# From <https://www.rhysgodfrey.co.uk/b/blog/posts/backing-up-a-sql-database-to-amazon-s3-using-powershell>
$server = '.'
$backupPath = 'C:\SqlBackup\' #Change to a location that works for you.
$user = 'USER_NAME'
$accessKey = 'ACCESS_KEY'
$secretKey = 'SECRET'
$s3bucket = 'BUCKET_NAME'
$region = 'ap-southeast-2' #Change AWS Region
$databases = Invoke-Sqlcmd -ServerInstance $server -Query "SELECT [name]
FROM master.dbo.sysdatabases
WHERE dbid > 4 ;"
# loop through each instances and backup up all the user databases
foreach ($database in $databases)
{
$timestamp = get-date -format yyyyMMddHHmmss
$fileName = "$($database.name)-$timestamp.bak"
$zipfilename = "$($database.name)-$timestamp.zip"
$filePath = Join-Path $backupPath $fileName
$zipfilepath = Join-Path $backupPath $zipfileName
#Write-Host $fileName
Backup-SqlDatabase -ServerInstance $server -Database $database.name -BackupFile $filePath
Write-Zip -path $filePath -OutputPath $zipfilepath
Write-S3Object -BucketName $s3Bucket -File $zipfilePath -Key $zipfileName -Region $region -AccessKey $accessKey -SecretKey $secretKey
Remove-Item $backupPath$($database.name)*.bak
Remove-Item $backupPath$($database.name)*.zip
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.