The recovery request concerns last year's database, well beyond the routine restore window. Long-term backup retention keeps selected older backups for that requirement. Configure the policy early, then verify actual retained backups rather than trusting the policy alone.
Distinguish a Retained Backup From Point-in-Time Recovery
Azure SQL Database creates automated backups for routine recovery. Short-term retention defines the available point-in-time recovery window for supported configurations. That window is separate from a policy selecting backups for longer preservation.
LTR retains selected full backups as discrete recovery points. It does not provide every possible transaction time throughout several years. Choose the retained backup representing the historical state you actually need.
I ask whether the requirement means an exact incident time or a periodic historical copy. I also check how far back that requirement extends. Those questions distinguish routine recovery from an archive of selected database states.
A historical restore creates another database rather than rewinding the production database in place. Plan how to inspect that copy and recover selected information. Do not make replacing production part of an otherwise ordinary retention test.
Define Weekly, Monthly, and Yearly Long-Term Backup Retention
The supported retention policy can preserve weekly, monthly, and yearly backups, for up to ten years. Each frequency has its own duration. The yearly policy also specifies which week supplies the yearly retained copy.
A weekly duration of twelve weeks does not mean one backup every twelve weeks. It means selected weekly copies remain for that duration. Similarly, monthly and yearly durations describe preservation rather than how long creating a backup takes.
Choose those values from a documented recovery requirement and approved storage budget. More retained copies produce more recovery choices and continuing storage obligations. The oldest available backup matters more than an impressive number written into the policy.
In the Azure portal, open the logical server's Backups page and its Retention policies tab. Select the intended database, open Configure policies, and set the approved durations. Review the selected databases before applying a change to several databases at once.
Portal steps are management actions, not T-SQL statements. Record the previous policy and the applied result. A change to the desired policy is not evidence that all desired historical backups already exist.
Inspect and Apply the Policy From Windows PowerShell
These blocks require the supported Az.Sql module and an authenticated Azure PowerShell session on Windows. Confirm the intended subscription before using them. Replace the resource names with your approved test database's identifiers.
# PowerShell
$resourceGroup = 'SqlRecoveryTest'
$serverName = 'sql-recovery-test'
$databaseName = 'AppDb'
Get-AzContext | Select-Object Subscription, Account, Tenant
$server = Get-AzSqlServer -ResourceGroupName $resourceGroup `
-ServerName $serverName -ErrorAction Stop
Get-AzSqlDatabaseBackupLongTermRetentionPolicy `
-ResourceGroupName $resourceGroup -ServerName $serverName `
-DatabaseName $databaseName -ErrorAction StopKeep inspection separate from changing the policy. The next block intentionally configures sample weekly, monthly, and yearly retention values. They illustrate the syntax, not a recommendation that every database should preserve these durations.
# PowerShell
Set-AzSqlDatabaseBackupLongTermRetentionPolicy `
-ResourceGroupName $resourceGroup -ServerName $serverName `
-DatabaseName $databaseName -WeeklyRetention P12W `
-MonthlyRetention P12M -YearlyRetention P7Y -WeekOfYear 26 `
-ErrorAction Stop
Get-AzSqlDatabaseBackupLongTermRetentionPolicy `
-ResourceGroupName $resourceGroup -ServerName $serverName `
-DatabaseName $databaseName -ErrorAction StopThe duration strings express periods, with W for weeks, M for months, and Y for years. Inspect all returned durations after the change. Do not assume an omitted setting preserves an older value without checking the command's resulting policy.
Management permissions belong to Azure role-based access control rather than a database user's SELECT permission. Grant the capabilities required for listing and restoring retained backups. Deleting retained backups is a separate authority that deserves narrower ownership.
List the Long-Term Backup Retention Copies That Exist
Use the portal's Available LTR backups entry to open the list for the database. Review backup time and expiration before choosing Restore. An empty list requires investigation even when the retention policy is enabled.
The next PowerShell block lists the actual retained backups for the selected region, server, and database. Keep their resource identifiers with the test evidence. Resource identifiers distinguish actual backup objects from a filename or an informal description.
# PowerShell
$backups = @(Get-AzSqlDatabaseLongTermRetentionBackup `
-Location $server.Location -ServerName $serverName `
-DatabaseName $databaseName -ErrorAction Stop)
$backups | Sort-Object BackupTime | Format-List *Initial availability is not immediate after enabling the policy. The service documents a wait of up to seven days for the first retained backup to appear. Alert on an unexpected gap using the expected retention cadence rather than demanding an instant copy.
Changing or removing a policy does not manufacture missing historical backups. Existing retained copies also have their own expiration behavior. Inspect both the future policy and the existing inventory when reviewing a change.
Restore One Selected Backup Into a Separate Database
In the portal, select the intended retained backup and choose Restore. Supply a new database name and review the target configuration before Create. Observe the restore operation until completion, then connect to the new database through SSMS.
For PowerShell, select one exact resource identifier from the listed backups. The example deliberately asks for that choice instead of automatically picking the newest copy. It checks the target name against existing databases before requesting restoration.
# PowerShell
$chosenResourceId = Read-Host 'Paste the reviewed backup ResourceId'
$selected = @($backups | Where-Object ResourceId -eq $chosenResourceId)
if ($selected.Count -ne 1) { throw 'Choose one exact listed backup.' }
$targetDatabase = 'AppDbLtrTest'
$existing = @(Get-AzSqlDatabase -ResourceGroupName $resourceGroup `
-ServerName $serverName -ErrorAction Stop)
if ($existing.DatabaseName -contains $targetDatabase) {
throw 'Choose an unused target database name.'
}
Restore-AzSqlDatabase -FromLongTermRetentionBackup `
-ResourceId $selected[0].ResourceId -ServerName $serverName `
-ResourceGroupName $resourceGroup -TargetDatabaseName $targetDatabase `
-ServiceObjectiveName P1 -ErrorAction StopP1 is an explicit example service objective with billing consequences. Choose a supported target objective matching the selected backup's service-tier constraints and your approved test budget. Hyperscale and other tiers have restrictions that prevent treating every backup as interchangeable.
The restored database needs a correctness and access review after provisioning. Verify expected historical records, database users, and application connectivity. Restoring a database object is not the same as demonstrating that the desired recovery operation works.
Review Storage Cost and Recovery Evidence Together
Retained backup storage has a continuing cost, affected by retained volume and the supported storage configuration. Restored test databases also incur their own compute and storage charges. Review actual billing and inventory rather than publishing a guessed cost per policy.
Use the Azure SQL pricing information for the selected region and configuration when budgeting. Avoid assuming that identical retention durations imply identical stored volume. Database growth and changes alter the volume being preserved.
Keep a recovery drill's selected backup identifier, restore outcome, verification result, and cleanup decision. Remove the test database through the approved cleanup process after preserving evidence. Do not delete the retained source backup merely because its test succeeded.
I inspect long-term backup retention inventory on a schedule and after policy changes. I also restore an older selected point rather than testing only the newest one. An archive should survive a real question, not just look reassuring on its settings page.
Which historical recovery point can you actually restore today? Answer from the available inventory and a completed drill. Long-term backup retention becomes useful when its preserved copies have an owner, a budget, and a tested recovery path.
Related reading on this blog: Backup Retention: How Long to Keep What and Azure SQL Database for the On-Premises DBA.
A retention policy is not recovery evidence, it is a promise that available backups must verify.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
