Powershell Sql Backup To AWS S3

SqlBackupToS3.ps1

# 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
}