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

The code Repo

Enjoy Coding

Computer science Engineer Student, A lover of everything that urges the mind to work hard such as Quantum Physics, General Medicine, Personal dev…