Automating Azure SQL long-term backup retention (LTR) with scheduled PowerShell
Apply the LTR policy idempotently, produce a verification report and test restores automatically.
Azure SQL long-term retention (LTR) keeps weekly, monthly or yearly full backups for years, beyond point-in-time restore (PITR). The service does the heavy lifting, but two things remain our job: making sure every database really has the right policy, and proving that we can restore. I automate both with a scheduled PowerShell script.
Principle
flowchart LR
A["Scheduler (pipeline or runbook)"] --> B["PowerShell script (managed identity)"]
B --> C["1. Apply the LTR policy"]
B --> D["2. Verification report"]
B --> E["3. Restore test"]
C --> F["Azure SQL (servers and databases)"]
D --> G["HTML mail + CSV"]
E --> H["Disposable test database"]
The scheduler can be an Azure DevOps pipeline, an Azure Automation runbook or an Azure Batch task. The script authenticates with a managed identity: no secret to store.
1. The policy, applied idempotently
I describe the expected policy once and the script fixes any drift, including databases created since the last run.
Connect-AzAccount -Identity | Out-Null
$expected = @{ Weekly = 'P4W'; Monthly = 'P12M'; Yearly = 'P5Y'; WeekOfYear = 1 }
foreach ($srv in Get-AzSqlServer) {
$dbs = Get-AzSqlDatabase -ServerName $srv.ServerName -ResourceGroupName $srv.ResourceGroupName |
Where-Object DatabaseName -ne 'master'
foreach ($db in $dbs) {
$p = Get-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $srv.ServerName `
-DatabaseName $db.DatabaseName -ResourceGroupName $srv.ResourceGroupName
if ($p.WeeklyRetention -ne $expected.Weekly -or $p.MonthlyRetention -ne $expected.Monthly -or
$p.YearlyRetention -ne $expected.Yearly) {
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $srv.ServerName `
-DatabaseName $db.DatabaseName -ResourceGroupName $srv.ResourceGroupName `
-WeeklyRetention $expected.Weekly -MonthlyRetention $expected.Monthly `
-YearlyRetention $expected.Yearly -WeekOfYear $expected.WeekOfYear | Out-Null
}
}
}
Durations use ISO 8601 (P4W, P12M, P5Y). For a Managed Instance, equivalent cmdlets exist at instance-database level.
2. The verification report
A correct policy does not prove that backups exist. For each database I read the latest LTR backup and its age.
$ltb = Get-AzSqlDatabaseLongTermRetentionBackup -Location westeurope `
-ServerName sql-contoso-prd -DatabaseName AppDb |
Sort-Object BackupTime -Descending
$last = $ltb | Select-Object -First 1
[pscustomobject]@{
Database = 'AppDb'
Count = @($ltb).Count
LastLtb = $last.BackupTime
AgeDays = if ($last) { [int]((Get-Date) - $last.BackupTime).TotalDays } else { $null }
}
The report (CSV + HTML mail) sorts each database into three states: OK, non-compliant policy fixed, backup too old or missing. Only the abnormal cases deserve an alert.
3. The restore test
A backup you have never restored is a hypothesis. Every week I restore the latest LTR backup of a randomly picked database to a test server, run a few checks, then delete the copy.
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $last.ResourceId `
-ServerName sql-contoso-test -ResourceGroupName rg-dba-test `
-TargetDatabaseName AppDb_ltrtest -ServiceObjectiveName S0
# checks: DBCC CHECKDB, row count of a key table...
Remove-AzSqlDatabase -ServerName sql-contoso-test -ResourceGroupName rg-dba-test `
-DatabaseName AppDb_ltrtest -Force
The test also measures the restore duration, which is valuable when someone asks "how long to recover?".
Pitfalls and lessons learned
- Initial delay: after a policy is created, the first LTR backup does not show up immediately. The report must tolerate a recent database.
- Permissions: the identity needs a role able to modify SQL servers for the policy, and to create databases on the test server for the restore. I split the two across two identities when possible.
- The restore test costs money and time: smallest service objective, systematic deletion, even on failure (
finallyblock). - Leftover test databases: a failure midway leaves a database behind. A prior cleanup by name prefix avoids it.
- LTR is not an export: backups stay inside Azure and can only be restored to Azure SQL. For a portable copy (BACPAC file in separate storage), I add a scheduled export on top, taken from a copy of the database to keep transactional consistency.
- Policy as code: versioning the expected policy in the repository is the best way to evolve it without surprises.