How to automate Azure SQL Database backups with Automation Runbook
This tutorial shows how to automate backups of an Azure SQL Database using an Azure Automation Runbook. It is useful to ensure regular copies (.bacpac) outside the database, integrate with Blob Storage and apply automatic retention.
Prerequisites
- Azure account with permissions to create resources (Automation, Storage, Managed Identity).
- An existing Azure SQL Database and a Storage Account with a container for backups.
- Az PowerShell installed locally or use of Cloud Shell.
Step 1: Create an Automation Account
Creating an Automation Account is used to run Runbooks that perform the export process. Automation provides scheduling, logging and integration with Managed Identity.
# Exemplo PowerShell (Cloud Shell)
$rg = 'rg-backup'
$location = 'westeurope'
$aaName = 'aa-sql-backup'
New-AzAutomationAccount -ResourceGroupName $rg -Name $aaName -Location $location
Step 2: Enable Managed Identity on the Automation Account
The Runbook must authenticate to the Azure SQL Database and to Storage without secrets; for this we use the Automation Account's Managed Identity and assign appropriate permissions.
# Atribuir identidade gerida e permissões ao Automation Account
$aa = Get-AzAutomationAccount -ResourceGroupName $rg -Name $aaName
# Ativar System Assigned Identity (na maioria dos casos já disponível via portal; via CLI/PS pode requerer rota alternativa)
Step 3: Grant permissions to Storage and SQL
Grant the Managed Identity write access to the Storage container and minimal permissions on SQL (dbmanager or use SQL authentication for export). To export .bacpac via REST/PowerShell we typically export using SQL credentials; another option is to create credentials in SQL for AD.
# Exemplo para Storage: Atribuir role Storage Blob Data Contributor
$sp = (Get-AzAutomationAccount -ResourceGroupName $rg -Name $aaName).Identity.PrincipalId
$storage = Get-AzStorageAccount -ResourceGroupName $rg -Name 'stbackups'
New-AzRoleAssignment -ObjectId $sp -RoleDefinitionName 'Storage Blob Data Contributor' -Scope $storage.Id
# Para SQL: garantir login SQL com permissão para exportar
# (aqui assumimos utilização de SQL auth nas credenciais do Runbook)
Step 4: Create the PowerShell Runbook to export the database
Write a PowerShell Runbook that exports the Azure SQL Database to a .bacpac in the Storage Account. The example below uses the export REST API (Export bacpac) via the Az.Sql cmdlets.
param(
[string]$ResourceGroup = 'rg-backup',
[string]$ServerName = 'sqlserver-demo',
[string]$DatabaseName = 'db-demo',
[string]$StorageAccountName = 'stbackups',
[string]$ContainerName = 'backups',
[string]$StorageKey = '', # Se usar Managed Identity pode criar URL SAS em vez de key
[string]$SqlAdmin = 'sqladmin',
[string]$SqlPassword = ''
)
# Autenticar (no Automation use Run As ou Managed Identity conforme configurado)
Connect-AzAccount -Identity
$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroup -Name $StorageAccountName
$storageUri = "https://$($StorageAccountName).blob.core.windows.net/$ContainerName/$(Get-Date -Format 'yyyyMMdd-HHmmss')-$DatabaseName.bacpac"
$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $ResourceGroup -ServerName $ServerName -DatabaseName $DatabaseName \
-StorageKeytype 'StorageAccessKey' -StorageKey $StorageKey -StorageUri $storageUri -AdministratorLogin $SqlAdmin -AdministratorLoginPassword (ConvertTo-SecureString $SqlPassword -AsPlainText -Force)
# Opcional: aguardar conclusão
$job = $exportRequest
while ($true) {
$status = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $job.OperationStatusLink
Write-Output "Export status: $($status.Status)"
if ($status.Status -in @('Succeeded','Failed','Cancelled')) { break }
Start-Sleep -Seconds 15
}
Step 5: Schedule the Runbook
Publish the Runbook and create a schedule in the Automation Account to run daily or according to the desired retention.
# Exemplo resumido via PowerShell
$rb = New-AzAutomationRunbook -ResourceGroupName $rg -AutomationAccountName $aaName -Name 'Export-Sql-Database' -Type PowerShell -ScriptText (Get-Content -Raw './Export-Sql.ps1')
Publish-AzAutomationRunbook -ResourceGroupName $rg -AutomationAccountName $aaName -Name $rb.Name
# Criar schedule diário às 02:00
$sched = New-AzAutomationSchedule -AutomationAccountName $aaName -ResourceGroupName $rg -Name 'Daily-02' -StartTime (Get-Date).Date.AddDays(1).AddHours(2) -Frequency Day -Interval 1
Register-AzAutomationScheduledRunbook -AutomationAccountName $aaName -ResourceGroupName $rg -Name $rb.Name -ScheduleName $sched.Name
Verify the result
Confirm that the .bacpac files appear in the Storage container and that the Runbook completed successfully. Check jobs under Automation > Jobs and the container in the Storage Account. If there are errors, consult the job output for common messages: invalid credentials, Storage permissions, or export timeout.
Conclusion
You now have an automated backup process (export .bacpac) for an Azure SQL Database using an Azure Automation Runbook. Next steps: implement automatic retention (remove old files), use SAS instead of StorageKey, or integrate with Azure Logic Apps for alerts. Tip: test restoring the .bacpac on a development instance to validate the backup.