Automate your SQL Server backups into S3 bucket
Code PowerShell of Backup Databases from SQL Server to S3 bucket files
Here is the code, read it carefuly it’s much simple
################################## Preparing Env
New-Item -Path “C:\” -Name “tempSqlBackup” -ItemType “directory” -Force
New-Item -Path “C:\” -Name “tempSqlZip” -ItemType “directory” -Force
$SqlBackup = “C:\tempSqlBackup\”
$SqlZip = “C:\tempSqlZip\”
################################## Backup DBs except tempdb, master
Import-Module SQLPS
$serverInstance = “localhost\SQLEXPRESS” # ← — — — Specify the instance name
$databases = Get-SqlDatabase -ServerInstance $serverInstance | Where { ($_.Name -ne ‘tempdb’) -and ($_.Name -ne ‘master’) }
foreach($database in $databases){
$dbName = $database.Name
Backup-SqlDatabase -ServerInstance $serverInstance -Database “$dbName” -BackupFile “$SqlBackup\$dbName.bak” #-CompressionOption On -Checksum
}
#Remove-Module -Name SQLPS
################################## folder compressing
$DT = Get-Date -UFormat “%Y-%m-%d-%H-%M”
compress-archive -path $SqlBackup -destinationpath “$SqlZip\backup-$DT.zip” -compressionlevel optimal
Remove-Item -LiteralPath $SqlBackup -Force -Recurse -Confirm:$false
################################## S3 file Uploading
$s3Bucket = ‘<name>’ # ← — — — Specify the Name of your S3 bucket
$region = ‘<region>’ # ← — — — Specify the Region of your S3 bucket
$AwsCredName = “myAWScredentials”
$AccessKey = “<acesskey>” # ← — — — Specify the Access Key of the AWS account
$SecretKey = “<secretkey>” # ← — — — Specify the Secret Key of the AWS account
#Set-AWSCredentials -AccessKey $AccessKey -SecretKey $SecretKey -StoreAs $AwsCredName
Set-AWSCredentials $AwsCredName
Write-S3Object -BucketName $s3Bucket -StoredCredentials $AwsCredName -File “$SqlZip\backup-$DT.zip” -Region $region
Remove-Item -LiteralPath $SqlZip -Force -Recurse -Confirm:$false
Setup a Task Scheduler
After writing the code in a powershell file Ex“script_backup.ps1”, Look in the start menu for Task Scheduler
Click on Create Basic Task
After that you have to provide the name of the scheduler and some description and click Next
After that you have to set the trigger which is right for you
Then specify what you want and click next
Then specify start a program and click next
Then you have to choose the script and write before the path Powershell
Then review and click finish
Restore files from S3 bucket to sql server Databases
############################ Download from s3 bucket
$SqlBackup = “C:\”
$s3Bucket = ‘<name>’ # ← — — — Specify the Name of your S3 bucket
$region = ‘<region>’ # ← — — — Specify the Region of your S3 bucket
$AwsCredName = “myAWScredentials”
$AccessKey = “<acesskey>” # ← — — — Specify the Access Key of the AWS account
$SecretKey = “<secretkey>” # ← — — — Specify the Secret Key of the AWS account
#Set-AWSCredentials -AccessKey $AccessKey -SecretKey $SecretKey -StoreAs $AwsCredName
Set-AWSCredentials $AwsCredName
$s3Object = Get-S3Object -StoredCredentials $AwsCredName -BucketName $s3Bucket -Region $region | Sort-Object LastModified -Descending | Select-Object -First 1
$filezip = $SqlBackup + $s3Object.key
$Params = @{
BucketName = $s3Bucket
Key = $s3Object.Key
File = $filezip
Region = $region
StoredCredentials = $AwsCredName
}
Read-S3Object @Params
expand-archive -path $filezip -DestinationPath $SqlBackup
Remove-Item $filezip
############################## Restore to sql server
Import-Module SQLPS
$SqlBackup = “C:\tempSqlBackup\”
$serverInstance = “localhost\SQLEXPRESS” # ← — — — Specify the instance name
$backupFiles = Get-ChildItem -Path $SqlBackup
forEach($backupfile in $backupFiles){
$dbName = $backupfile.BaseName
$backupFile = $SqlBackup + $dbName + “.bak”
Restore-SqlDatabase -ServerInstance $serverInstance -Database $dbName -BackupFile $backupFile
}
Remove-Item -LiteralPath $SqlBackup -Force -Recurse -Confirm:$false
