Azure SQL Backup & Retention Report

A PowerShell 7 script that automatically extracts backup and retention policies for all SQL databases across all Azure subscriptions.

What is extracted

The script collects the following data per database:

Understanding the values

PT0S — no LTR configured

PT0S is an ISO 8601 duration string meaning "Period: 0 Seconds" — the field is not configured.

PT0S  → No backup
P7D   → 7 days
P4W   → 4 weeks
P12M  → 12 months
P1Y   → 1 year

Databases with PT0S on all LTR fields are protected by PITR only (typically 7 days).

Requirements

# PowerShell 7+
pwsh --version

# Az module
Install-Module -Name Az -Scope CurrentUser -Force

Script

#Requires -Version 7.0

param(
    [string]$OutputCsv = "sql_backup_report_$(Get-Date -Format 'yyyyMMdd_HHmm').csv",
    [switch]$Debug
)

Connect-AzAccount -ErrorAction Stop | Out-Null

$subscriptions = Get-AzSubscription | Where-Object { $_.State -eq 'Enabled' }
Write-Host "✅ Found $($subscriptions.Count) subscription(s)" -ForegroundColor Green

$report = [System.Collections.Generic.List[PSCustomObject]]::new()

foreach ($sub in $subscriptions) {
    Write-Host "`n📦 [$($sub.Name)]" -ForegroundColor Yellow
    Set-AzContext -SubscriptionId $sub.Id -ErrorAction Stop | Out-Null

    $servers = Get-AzSqlServer -ErrorAction SilentlyContinue
    if (-not $servers) { Write-Host "   ↳ No SQL servers" -ForegroundColor DarkGray; continue }

    foreach ($server in $servers) {
        $rg         = $server.ResourceGroupName
        $serverName = $server.ServerName
        Write-Host "   🖥  $serverName [$rg]" -ForegroundColor White

        $databases = Get-AzSqlDatabase -ServerName $serverName -ResourceGroupName $rg -ErrorAction SilentlyContinue |
                     Where-Object { $_.DatabaseName -ne 'master' }

        foreach ($db in $databases) {
            Write-Host "      └─ $($db.DatabaseName)" -ForegroundColor DarkCyan

            # PITR + Differential frequency
            $pitrDays     = 'N/A'
            $diffInterval = 'N/A'
            try {
                $pitr = Get-AzSqlDatabaseBackupShortTermRetentionPolicy `
                    -ServerName $serverName `
                    -ResourceGroupName $rg `
                    -DatabaseName $db.DatabaseName `
                    -ErrorAction Stop

                $pitrDays     = $pitr.RetentionDays
                $diffInterval = "$($pitr.DiffBackupIntervalInHours) Hours"

                if ($Debug) {
                    Write-Host "         PITR: $($pitr | ConvertTo-Json -Compress)" -ForegroundColor DarkMagenta
                }
            }
            catch {
                Write-Host "         ⚠️  PITR error: $_" -ForegroundColor DarkYellow
            }

            # LTR
            $ltr = $null
            try {
                $ltr = Get-AzSqlDatabaseLongTermRetentionPolicy `
                    -ServerName $serverName `
                    -ResourceGroupName $rg `
                    -DatabaseName $db.DatabaseName `
                    -ErrorAction Stop
            }
            catch {
                Write-Host "         ⚠️  LTR error: $_" -ForegroundColor DarkYellow
            }

            $report.Add([PSCustomObject]@{
                SubscriptionName        = $sub.Name
                SubscriptionId          = $sub.Id
                ResourceGroup           = $rg
                ServerName              = $serverName
                Location                = $server.Location
                DatabaseName            = $db.DatabaseName
                Edition                 = $db.Edition
                ServiceObjective        = $db.CurrentServiceObjectiveName
                Status                  = $db.Status
                MaxSizeGB               = [math]::Round($db.MaxSizeBytes / 1GB, 2)
                PITR_RetentionDays      = $pitrDays
                PITR_DiffBackupInterval = $diffInterval
                LTR_WeeklyRetention     = $ltr?.WeeklyRetention  ?? 'PT0S'
                LTR_MonthlyRetention    = $ltr?.MonthlyRetention ?? 'PT0S'
                LTR_YearlyRetention     = $ltr?.YearlyRetention  ?? 'PT0S'
                LTR_WeekOfYear          = $ltr?.WeekOfYear       ?? 'N/A'
                BackupStorageRedund     = $db.BackupStorageRedundancy ?? 'Unknown'
                CreationDate            = $db.CreationDate?.ToString('yyyy-MM-dd') ?? ''
                EarliestRestoreDate     = $db.EarliestRestoreDate?.ToString('yyyy-MM-dd HH:mm') ?? ''
                ReportedAt              = (Get-Date -Format 'yyyy-MM-dd HH:mm')
            })
        }
    }
}

$sorted = $report | Sort-Object SubscriptionName, ServerName, DatabaseName
Write-Host "`n📊 Total: $($sorted.Count) databases" -ForegroundColor Cyan

$sorted | Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8
Write-Host "✅ Saved: $OutputCsv`n" -ForegroundColor Green

$sorted | Format-Table SubscriptionName, ServerName, DatabaseName,
    PITR_RetentionDays, PITR_DiffBackupInterval,
    LTR_WeeklyRetention, LTR_YearlyRetention,
    BackupStorageRedund -AutoSize

Usage

# Standard run
pwsh ./Get-AzSqlBackupReport.ps1

# With debug output (shows raw PITR object)
pwsh ./Get-AzSqlBackupReport.ps1 -Debug

# Custom output filename
pwsh ./Get-AzSqlBackupReport.ps1 -OutputCsv "my_report.csv"

Backup strategy guide

PITR (Point-in-Time Restore)

Automatically enabled on all Azure SQL databases. Default is 7 days, maximum is 35 days. Recommended minimum for production databases: 14 days.

LTR (Long-Term Retention)

Must be configured manually. Used for compliance and long-term storage for up to 10 years.

# Example: Set weekly LTR retention to 4 weeks
Set-AzSqlDatabaseBackupLongTermRetentionPolicy `
    -ServerName "my-server" `
    -ResourceGroupName "my-rg" `
    -DatabaseName "my-db" `
    -WeeklyRetention "P4W"

Backup Storage Redundancy

Troubleshooting

PITR returns N/A Verify that the service principal or user account has the role permission Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/read.

Run with -Debug to see the exact error object printed directly in the terminal.

Last updated: 2026-04-13