SQL DB Backups to Azure Storage with Powershell

Automating database backups has to be among the most common problems in DevOps. I'm sure there are hundreds of different solutions for this, but the way you choose to do it will depend on your environment and the specific tools at your disposal. In my case, I needed to create daily backups of SQL Server databases, and push them to an Azure Blob Storage Account. Assuming we are running SQL Server on a Windows machine, we can automate this process using Powershell. For the purpose of this post, I'm going to assume that we already have a Blob Storage Container set up in Azure, and an SQL database to back up.

1. Retrieve the Database Objects

Get the database objects of the specific instance of SQL Server you want to back up. Here I am targeting the default instance. Note that I ignore tempdb as it doesn't make much sense to back this up.

$dbPath = "SQLServer:\SQL\$env:COMPUTERNAME\DEFAULT\databases"  
$databases = Get-ChildItem -Force -Path $dbPath | Where-Object {$_.name -ne "tempdb"}

2. Backup Databases

Powershell makes it easy for us to do this by giving us a single command that does the job. We only need to provide it with the relevent database and an appropriate file name for each database.

foreach($database in $databases){
  $databaseName = $database.Name
  $filePath = "C:\Backups\" + $databaseName + ".bak"
  $database | Backup-SqlDatabase -BackupFile $filePath -Initialize -FormatMedia -SkipTapeHeader
}

3. Compress

I recommend compressing the backups to save space. Here we append today's date to the file name. This allows us to have a unique file name per day, as well as making it easy for us to identify at a glance when the backup was taken.

$backupNameZip = $today.ToString("yyyy-MM-dd") + "_Backup.zip"
Compress-Archive -Path "C:\Backups\*.bak" -DestinationPath "C:\Backups\$backupNameZip" -CompressionLevel "Optimal"

4. Copy to the Cloud

Now that we have our backups zipped, we can copy our file to an Azure Storage Container. Note that we used a Shared Access Signiature to grant ourselves access to the container. Also, you will need to set yourself up with AzCopy to use the command below. AzCopy is the command-line tool used for many of the blob manipulation commands used with storage containers.

    $storageAccount = "myStorageAccountName" 
    $storageContainer = "myStorageContainerName" 
    $sasToken = "mySasToken"
$blobUrl = "https://$storageAccount.blob.core.windows.net/$storageContainer/$backupNameZip" + "?$sasToken"
    azcopy cp "C:\Backups\$backupNameZip" $blobUrl

Final Thoughts

Combine the code segments above into a handy Powershell script and we are on our way to fully automated backups to the cloud. Now there are of course a lot more sophisticated things we can pull off from here, such as running the Powershell script as a daily scheduled task, deleting older backups after a certain amount of time, and even adding the script to your CI/CD pipeline to backup your databases before a big deployment, but those topics can be covered in future posts.