Azure SQL Database Backups: Point-in-Time Restore, LTR and Geo-Restore

Azure SQL Database backs up every database automatically: full backups weekly, differential backups every 12 or 24 hours, and transaction log backups roughly every 10 minutes, kept for 7 days by default and configurable up to 35 days. From those backups you can restore to any point in time on the same server, restore a deleted database, restore to another region with geo-restore, or keep weekly, monthly and yearly full backups for up to 10 years with a long-term retention (LTR) policy. Every restore creates a new database; nothing is restored in place.

Who this is for and what you will have at the end

This guide is for administrators and DBAs who run Azure SQL Database (single databases or elastic pools) and need a backup configuration that matches a real recovery requirement, plus a tested restore procedure. Notes for Azure SQL Managed Instance are included where the commands differ.

At the end you will have:

• PITR retention and differential backup frequency set deliberately rather than left at the defaults.
• Backup storage redundancy chosen with its effect on geo-restore understood.
• An LTR policy for compliance retention.
• Tested commands for point-in-time, deleted-database, LTR and geo-restores, and a way to swap a restored database into place.

If you need cross-region recovery measured in seconds rather than hours, backups are not enough on their own; see the companion guide on Azure SQL failover groups.

How Azure SQL Database backups work

You can't change the backup schedule, disable backups or download the automatic backups. The service decides exact timing, and the backups are only usable through restore operations. The first full backup is scheduled right after you create or restore a database and usually finishes within 30 minutes. Point-in-time restore becomes available once the first transaction log backup after that initial full backup exists.

Hyperscale is different: it uses storage snapshots instead of full, differential and log backups, and it doesn't support configuring the differential backup frequency. Restores between Hyperscale and other service tiers aren't supported.

The three recovery options compared

Microsoft's RTO and RPO guidance puts geo-restore at "typically minutes or hours" for both recovery time and data loss, compared with "typically less than 60 seconds" RTO for failover groups or active geo-replication. Use geo-restore for small or non-critical databases and failover groups when you need guaranteed capacity and low data loss.

Backup storage redundancy

The redundancy setting applies to both short-term and LTR backups. Changes on an existing database apply only to future backups and can take up to 48 hours to take effect. For Hyperscale you choose redundancy only at creation time.

Prerequisites

• Azure CLI signed in with az login, or the Az PowerShell module. Microsoft notes Az.Sql 2.11.0 or later for using -BackupStorageRedundancy with restore and copy operations.
• To restore, you need the Contributor or SQL Server Contributor role on the subscription or resource group that contains the logical server, or Owner. Restores can't be done with T-SQL.
• To view and restore LTR backups: Owner, Contributor or SQL Server Contributor. To delete LTR backups you need Owner or Contributor; SQL Server Contributor can't delete them.

The examples use resource group rg-sql-prod, server sql-contoso-prod and database contosodb.

Step 1: Set PITR retention and differential frequency

Decide the retention from your recovery requirement: how far back might you need to go to recover from a bad deployment or an accidental delete that went unnoticed? The maximum is 35 days.

In the portal, open the logical server, select Backups, then the Retention policies tab. Select the databases, choose Configure policies, use the slider under Point-in-time restore, and choose 12 Hours or 24 hours under Differential backup frequency.

With the Azure CLI:

az sql db str-policy set \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --retention-days 28 \
  --diffbackup-hours 12

With PowerShell:

Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName "rg-sql-prod" `
  -ServerName "sql-contoso-prod" -DatabaseName "contosodb" `
  -RetentionDays 28 -DiffBackupIntervalInHours 12

Two behaviours to know before you change this:

• Reducing retention deletes backups that are no longer needed, and you immediately lose the ability to restore to those older points.
• Increasing retention does not give you older restore points straight away; they accumulate over time.

A 24-hour differential frequency can make restores take longer than 12 hours. In the vCore model the default is 12 hours; in the DTU model it is 24 hours.

For Azure SQL Managed Instance, use az sql midb short-term-retention-policy set with --managed-instance (the command is marked preview in the CLI reference) or Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -InstanceName. Managed Instance also lets you set retention on deleted databases, from 0 to 35 days, but only lower than the remaining days.

Step 2: Choose backup storage redundancy

Geo-redundant storage is the default and is what makes geo-restore possible. If data residency requires backups to stay in one region, switch to zone-redundant or locally redundant storage and accept that geo-restore is disabled.

To change an existing database (not Hyperscale or Basic) in the portal, open the database, select Compute & storage under Settings, and pick the redundancy option. With the CLI:

az sql db update \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --backup-storage-redundancy Zone

With PowerShell:

Set-AzSqlDatabase -ResourceGroupName "rg-sql-prod" -ServerName "sql-contoso-prod" `
  -DatabaseName "contosodb" -BackupStorageRedundancy Zone

Accepted values for these two commands are Geo, Zone and Local; restore commands also accept GeoZone. To enforce a residency rule across subscriptions, assign the built-in Azure Policy definition "Azure SQL DB should avoid using GRS backup". Policies aren't enforced for databases created with T-SQL, so in that case use BACKUP_STORAGE_REDUNDANCY with LOCAL or ZONE in CREATE DATABASE.

On Managed Instance the setting is per instance: use az sql mi update or Set-AzSqlInstance with the backup storage redundancy parameter. In the portal the change applies only to PITR backups; LTR backups keep the old redundancy type.

Step 3: Configure long-term retention

An LTR policy has four parts: weekly retention (W), monthly retention (M), yearly retention (Y) and the week of the year used for the yearly backup. Values use ISO 8601 durations such as P12W, P12M or P5Y. Some examples from Microsoft's documentation:

In the portal: server, Backups, Retention policies, select the databases, Configure policies, set weekly, monthly or yearly retention, then Apply. A value of 0 means no LTR for that cadence.

With the CLI:

az sql db ltr-policy set \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --weekly-retention "P12W" \
  --monthly-retention "P12M" \
  --yearly-retention "P5Y" \
  --week-of-year 16

With PowerShell:

Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName "rg-sql-prod" `
  -ServerName "sql-contoso-prod" -DatabaseName "contosodb" `
  -WeeklyRetention P12W -MonthlyRetention P12M -YearlyRetention P5Y -WeekOfYear 16

Points that catch people out:

• The first LTR backup can take up to seven days to appear. You can't create an LTR backup on demand or choose its timing.
• Policy changes apply only to future backups; existing LTR backups keep their original expiry.
• If the week of the year is already in the past when you set the policy, the first yearly backup is created the following year.
• If you use failover groups or geo-replication, set the same LTR policy on the secondary. Secondaries don't generate LTR backups until they become primary, so this costs nothing extra.
• LTR backups survive deletion of the database and even of the logical server, which makes them the only recovery path after a server is deleted.
• To make LTR backups immutable, az sql db ltr-policy set accepts --make-backups-immutable Enabled, with --tb-immutability-mode Locked or Unlocked. Immutability is supported for Azure SQL Database LTR backups but not for Managed Instance.

Step 4: Restore to a point in time

Point-in-time restore creates a new database on the same server. Cross-server, cross-subscription and cross-region PITR aren't supported, and you can't run PITR on a geo-secondary.

First check the earliest available restore point, then restore. The --time value uses the format YYYY-MM-DDTHH:MM:SS and must be on or after the database's earliestRestoreDate.

az sql db show \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --query earliestRestoreDate
 
az sql db restore \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --dest-name contosodb-restored \
  --time "2026-10-10T08:30:00" \
  --service-objective S3

In PowerShell:

$db = Get-AzSqlDatabase -ResourceGroupName "rg-sql-prod" -ServerName "sql-contoso-prod" -DatabaseName "contosodb"
Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime "2026-10-10T08:30:00Z" `
  -ResourceGroupName $db.ResourceGroupName -ServerName $db.ServerName `
  -TargetDatabaseName "contosodb-restored" -ResourceId $db.ResourceId `
  -Edition "Standard" -ServiceObjectiveName "S3"

In the portal, open the database overview and select Restore on the toolbar.

Restores are resource-intensive, and Microsoft notes that the target database might need a service tier of S3 or greater; you can scale it down after the restore completes. If the source uses In-Memory OLTP objects in Business Critical or Premium, the restored database must use the same tier.

Swap the restored database into place

If the restored copy should replace the original, connect to the master database and rename both:

ALTER DATABASE [contosodb] MODIFY NAME = [contosodb-old];
ALTER DATABASE [contosodb-restored] MODIFY NAME = [contosodb];

Database restores don't carry over the original's tags, and server-level settings such as firewall rules are unaffected because the database stays on the same server. If you only need some rows back, keep both databases and copy the data across with a script instead.

Step 5: Restore a deleted database

You can restore a deleted database to its deletion time, or an earlier point, on the same server as long as the server still exists. In the portal, open the server overview and select Deleted databases; it can take several minutes for a recently deleted database to appear there.

az sql db list-deleted \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --output table
 
az sql db restore \
  --resource-group rg-sql-prod \
  --server sql-contoso-prod \
  --name contosodb \
  --dest-name contosodb \
  --deleted-time "2026-10-10T14:05:12"

The --deleted-time must match the deletion time shown for that database. In PowerShell, get the backup with Get-AzSqlDeletedDatabaseBackup and pass its DeletionDate and ResourceID to Restore-AzSqlDatabase -FromDeletedDatabaseBackup.

Step 6: Restore from a long-term retention backup

List the backups by region, server and database, then restore one by its resource ID:

az sql db ltr-backup list \
  --location westeurope \
  --server sql-contoso-prod \
  --database contosodb \
  --output table
 
backup_id=$(az sql db ltr-backup show \
  --location westeurope \
  --server sql-contoso-prod \
  --database contosodb \
  --name "<backup-name-from-list>" \
  --query id --output tsv)
 
az sql db ltr-backup restore \
  --dest-database contosodb-2025-audit \
  --dest-server sql-contoso-prod \
  --dest-resource-group rg-sql-prod \
  --backup-id "$backup_id"

The PowerShell equivalents are Get-AzSqlDatabaseLongTermRetentionBackup -Location -ServerName -DatabaseName and Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId. Don't use Copy-AzSqlDatabaseLongTermRetentionBackup; Microsoft has deprecated the API behind it.

If the original server or resource group has been deleted, use the CLI or PowerShell rather than the portal, grant permissions at subscription scope, and omit the resource group parameter for the source.

Step 7: Geo-restore to another region

Geo-restore creates a new database on any existing server in any region from the most recent geo-replicated backup. Because GRS replication is asynchronous, the newest changes may be missing.

az sql db geo-backup list \
  --server sql-contoso-prod \
  --resource-group rg-sql-prod
 
az sql db geo-backup restore \
  --dest-database contosodb \
  --dest-server sql-contoso-dr \
  --resource-group rg-sql-dr \
  --geo-backup-id "/subscriptions/<sub-id>/resourceGroups/rg-sql-prod/providers/Microsoft.Sql/servers/sql-contoso-prod/databases/contosodb/geoBackupPolicies/Default" \
  --service-objective S3

In PowerShell, use Get-AzSqlDatabaseGeoBackup followed by Restore-AzSqlDatabase -FromGeoBackup. In the portal, start Create SQL Database, open Additional settings, set Use existing data to Backup, and pick the geo-restore backup.

Create and configure the DR server (firewall rules, Microsoft Entra admin, logins) in advance; doing it during an outage adds directly to your recovery time. During a regional outage many customers may be geo-restoring at once, and capacity in the target region isn't guaranteed.

On Managed Instance, the equivalent is az sql midb recover with a recoverable database ID from the geo-paired instance, and PITR uses az sql midb restore --time, which can target a different instance with --dest-mi.

Verification

• Run az sql db str-policy show and az sql db ltr-policy show for each database and compare with your recovery requirement.
• Check earliestRestoreDate regularly; it should move forward as expected and match your retention.
• A week after enabling LTR, confirm backups appear in az sql db ltr-backup list.
• Run a restore drill per quarter: PITR to a test name, run DBCC CHECKDB, check row counts for key tables, then delete the test database.
• In Cost Management, filter Meter subcategory for PITR and LTR backup storage to watch backup costs.

Troubleshooting

"Configuring backup storage account type to 'Standard_RAGRS' failed during Database create or update." An Azure Policy assignment blocks geo-redundant backups. Specify --backup-storage-redundancy Local or Zone on the create or restore, or get the policy exemption you need.

Geo-restore option missing. The database uses LRS or ZRS backup storage. Change it to Geo, then wait: only backups taken after the change are geo-replicated.

Restore fails on a GZRS source. If you don't specify redundancy, a restore inherits GZRS from the source and fails in regions that don't support it. Pass a supported --backup-storage-redundancy value explicitly.

"An error has occurred while enabling Long-term backup retention for this database. Please reach out to Microsoft support to enable long-term backup retention." This appears for some Hyperscale databases where LTR isn't yet enabled; open a support request.

LTR backups stop appearing. LTR depends on successful full backups. A full transaction log, change data capture or anything else that blocks log truncation can delay them.

Restore is slow. Recovery time depends on database size, compute size, log volume to replay and concurrent restores. A subscription can process 30 concurrent single-database restore requests, and an elastic pool four.

Checklist

• PITR retention set per database, between 1 and 35 days.
• Differential frequency chosen with restore time in mind.
• Backup redundancy chosen; geo-restore implications accepted and documented.
• LTR policy configured on primaries and matching secondaries.
• Restore permissions assigned and DR server pre-built.
• Quarterly restore drill scheduled and recorded.

For the wider context of moving workloads into Azure, see the enterprise Azure cloud migration playbook.

References

• https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview
• https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-change-settings
• https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
• https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview
• https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure
• https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview
• https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/automated-backups-change-settings
• https://learn.microsoft.com/en-us/cli/azure/sql/db
• https://learn.microsoft.com/en-us/cli/azure/sql/db/geo-backup
• https://learn.microsoft.com/en-us/cli/azure/sql/db/ltr-policy
• https://learn.microsoft.com/en-us/cli/azure/sql/midb
• https://learn.microsoft.com/en-us/powershell/module/az.sql/restore-azsqldatabase