LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon