I had a need to take a full SQL Database backup from a virtual machine with SQL Server hosted on Azure. This is done via an Azure Automation account, executing a runbook on a hybrid worker. This is a great way to take a offline copy of your production SQL and store it someplace safe.
To accomplish this we will use the PowerShell module ‘sqlps‘ that should be installed with SQL Server and run the command Backup-SqlDatabase.
Backup-SqlDatabase (SqlServer) | Microsoft Docs
Store SQL Storage Account Credentials
Before we can run the Backup-SqlDatabase command we must have a saved credential stored in SQL for the Storage Account using New-SqlCredential.
New-SqlCredential (SqlServer) | Microsoft Docs
Import-Module sqlps
# set parameters
$sqlPath = "sqlserver:\sql\$($env:COMPUTERNAME)"
$storageAccount = "<storageAccountName>"
$storageKey = "<storageAccountKey>"
$secureString = ConvertTo-SecureString $storageKey -AsPlainText -Force
$credentialName = "azureCredential-"+$storageAccount
Write-Host "Generate credential: " $credentialName
#cd to sql server and get instances
cd $sqlPath
$instances = Get-ChildItem
#loop through instances and create a SQL credential, output any errors
foreach ($instance in $instances) {
try {
$path = "$($sqlPath)\$($instance.DisplayName)\credentials"
New-SqlCredential -Name $credentialName -Identity $storageAccount -Secret $secureString -Path $path -ea Stop | Out-Null
Write-Host "...generated credential $($path)\$($credentialName)." }
catch { Write-Host $_.Exception.Message } }
Backup SQL Databases with an Azure Runbook
The runbook below works on the DEFAULT instance and excludes both tempdb and model from backup.
Import-Module sqlps
$sqlPath = "sqlserver:\sql\$($env:COMPUTERNAME)"
$storageAccount = "<storageAccount>"
$blobContainer = "<containerName>"
$backupUrlContainer = "https://$storageAccount.blob.core.windows.net/$blobContainer/"
$credentialName = "azureCredential-"+$storageAccount
$prefix = Get-Date -Format yyyyMMdd
Write-Host "Generate credential: " $credentialName
Write-Host "Backup database: " $backupUrlContainer
cd $sqlPath
$instances = Get-ChildItem
#loop through instances and backup all databases (excluding tempdb and model)
foreach ($instance in $instances) {
$path = "$($sqlPath)\$($instance.DisplayName)\databases"
$databases = Get-ChildItem -Force -Path $path | Where-object {$_.name -ne "tempdb" -and $_.name -ne "model"}
foreach ($database in $databases) {
try {
$databasePath = "$($path)\$($database.Name)"
Write-Host "...starting backup: " $databasePath
$fileName = $prefix+"_"+$($database.Name)+".bak"
$destinationBakFileName = $fileName
$backupFileURL = $backupUrlContainer+$destinationBakFileName
Write-Host "...backup URL: " $backupFileURL
Backup-SqlDatabase -Database $database.Name -Path $path -BackupFile $backupFileURL -SqlCredential $credentialName -Compression On
Write-Host "...backup complete." }
catch { Write-Host $_.Exception.Message } } }
NOTE: You will notice a performance hit on the SQL Server so schedule this runbook in a maintanence window.
