PowerShell Script to get\download latest SQL backup file from S3 bucket

Introduction:

In today’s data-driven world, businesses rely heavily on robust database management systems to ensure the integrity and availability of their valuable information. As part of this process, regular backups are crucial to safeguard data against unforeseen events. Recently, we received a customer request to download the latest SQL backup file from their S3 bucket. In this blog post, we will delve into the steps we took to fulfill the customer’s request and provide a seamless experience.

Understanding the Request:

When the customer approached us, they needed the most recent SQL backup file from their S3 bucket every Saturday , which was to be used for staging refresh later . Understanding the importance of the activity, we acknowledged the urgency and began working on finding a solution promptly.

We developed a PowerShell logic which traverses through the S3 bucket and its folders to get the latest .bak file saved in S3 bucket .

cls 

# Import the AWS PowerShell module
Import-Module AWSPowerShell

# Set your AWS access key and secret key
$AccessKey = ''             
$SecretKey = ''             

# Set the local directory to save the downloaded backup file
$BackupDownloadPath = ''    

#Set Database name for whcih we have to download the latest backup file
$DBName = ''    


# Set the region and bucket name
$bucket ='S3-Bucket-Name'   
$region='us-west-2'         
$folderPathInS3 = ''        # This is the subfolders path in the S3 bucket ex.  SQLBackups/Adventworks-SQL22/Daily  , basically if backups are happening inside a folder in S3      

# Connect to AWS using your credentials
Set-AWSCredentials -AccessKey $AccessKey -SecretKey $SecretKey -StoreAs s3bucketAWScredentials


#Delete any old backup file present in the download drive folder
#Get-ChildItem -Path $BackupDownloadPath -Include *.bak -File -Recurse | foreach { $_.Delete()}  -ErrorAction SilentlyContinue

#Invoke powershell to use this token to login to the s3 bucket
Set-AWSCredentials s3bucketAWScredentials  

  
#To form the date format of the backup, which we have suffixed with the DB name in below command... this is done to form a pattern as SQL backups name generally have date format in them
$date =(Get-Date).AddDays(-1).ToString('yyyyMMdd')    # -1 means backup of one day before, -2 means backup of 2 days before. 0 means backup of today. so on and so forth

 
#This key is the name of the backup file with 
$Key =(Get-S3Object -BucketName $bucket -Keyprefix $folderPathInS3).Key | Select-String -Pattern $DBName+"_"+$date -CaseSensitive -SimpleMatch 


if ($Key)
{
    
    Write-Host "Downloading the latest backup file: $Key" -ForegroundColor DarkYellow
    copy-s3object -BucketName $bucket -Key $Key -LocalFile $BackupDownloadPath 
    Write-Host "Download completed. The backup file is saved to: $BackupDownloadPath" -ForegroundColor DarkGreen
}

 else 
{
    Write-Host "No SQL backup files found in the S3 bucket."
}