Point-in-Time Restore, LTR, and Cloud Backup

Recovery options

Simple explanation

Point-in-time restore (PITR) is like having a time machine for your database. Someone accidentally deleted all orders at 2:47 PM? Restore to 2:46 PM — every order is back.

Long-term retention (LTR) is like keeping annual photo albums. Azure keeps backups for up to 35 days — but regulations might require you to keep monthly or yearly snapshots for 10 years.

Cloud backup is storing backups in Azure Blob Storage instead of local disk — accessible from anywhere, durable, and cost-effective.

Point-in-time restore (PITR)

Azure SQL Database and MI

Azure’s automated backups (full + diff + log) enable PITR to any second within the retention window:

# Restore Azure SQL Database to a point in time
Restore-AzSqlDatabase -ResourceGroupName "HarbourHealthRG" `
  -ServerName "harbourhealth-sql" `
  -TargetDatabaseName "PatientsDB_Restored" `
  -FromPointInTimeBackup `
  -PointInTime "2026-04-21T14:46:00Z" `
  -ResourceId "/subscriptions/.../databases/PatientsDB"

# Azure CLI
az sql db restore --resource-group HarbourHealthRG \
  --server harbourhealth-sql \
  --name PatientsDB \
  --dest-name PatientsDB_Restored \
  --time "2026-04-21T14:46:00Z"

Key points:

PITR creates a new database (doesn’t overwrite the existing one)
Available for the full retention period (1-35 days, default 7)
Works with any backup redundancy (LRS, ZRS, GRS)
For geo-restore: restore from geo-redundant backup to a different region

SQL Server on VMs

PITR on VMs requires a complete log chain:

-- Restore full + diff + logs up to the target time
RESTORE DATABASE NorthStarERP
FROM URL = '...Full.bak' WITH NORECOVERY;

RESTORE DATABASE NorthStarERP
FROM URL = '...Diff.bak' WITH NORECOVERY;

RESTORE LOG NorthStarERP
FROM URL = '...Log1.trn' WITH NORECOVERY;

-- Restore the final log to the exact point in time
RESTORE LOG NorthStarERP
FROM URL = '...Log2.trn'
WITH RECOVERY, STOPAT = '2026-04-21T14:46:00';

Long-term retention (LTR)

LTR extends backup retention beyond the 35-day PITR window — up to 10 years.

How it works

Configure an LTR policy on the database
Azure automatically copies backups to separate LTR storage
LTR backups are full backups (not incremental)
Stored in the same or different region based on backup redundancy

LTR policy settings

Setting	What It Means	Example
Weekly (W)	Keep one backup per week for N weeks	`W=4` → keep 4 weekly backups
Monthly (M)	Keep one backup per month for N months	`M=12` → keep 12 monthly backups
Yearly (Y)	Keep one backup per year for N years	`Y=10` → keep 10 yearly backups
Week of year	Which week’s backup becomes the yearly backup	`WeekOfYear=1` → first week of January

# Configure LTR policy
Set-AzSqlDatabaseBackupLongTermRetentionPolicy `
  -ResourceGroupName "HarbourHealthRG" `
  -ServerName "harbourhealth-sql" `
  -DatabaseName "PatientsDB" `
  -WeeklyRetention "P4W" `     # 4 weeks
  -MonthlyRetention "P12M" `   # 12 months
  -YearlyRetention "P10Y" `    # 10 years
  -WeekOfYear 1                # first week = yearly backup

Restoring from LTR

# List available LTR backups
Get-AzSqlDatabaseLongTermRetentionBackup `
  -Location "EastUS" `
  -ServerName "harbourhealth-sql" `
  -DatabaseName "PatientsDB"

# Restore from LTR backup
Restore-AzSqlDatabase -ResourceGroupName "HarbourHealthRG" `
  -ServerName "harbourhealth-sql" `
  -TargetDatabaseName "PatientsDB_LTR_Restore" `
  -FromLongTermRetentionBackup `
  -ResourceId "/subscriptions/.../longTermRetentionBackups/..."

Backup to Azure Blob Storage

For MI and SQL VMs, BACKUP TO URL stores backups directly in Blob Storage:

Setting up credentials

-- Create a credential using a SAS token
CREATE CREDENTIAL AzureBackupCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=b&srt=co&sp=rwdlacup&...';

-- Or using managed identity (SQL Server 2022+ on Azure VMs)
-- No explicit credential needed if VM has managed identity with Storage Blob Data Contributor role

Backup to URL

-- Backup to Blob Storage
BACKUP DATABASE NorthStarERP
TO URL = 'https://northstarstorage.blob.core.windows.net/backups/NorthStarERP.bak'
WITH CREDENTIAL = 'AzureBackupCredential', COMPRESSION, CHECKSUM;

Storage tier considerations

Storage Tier	Cost	Access Latency	Best For
Hot	Highest	Instant	Recent backups, frequently accessed
Cool	Medium	Instant	Backups aged 30-90 days
Archive	Lowest	Hours to rehydrate	Long-term compliance backups

Exam tip: PITR creates a new database

A common exam trap: PITR on Azure SQL Database does NOT restore in-place. It always creates a NEW database. After restore:

Verify the restored data is correct
Rename the old database (or delete it)
Rename the restored database to the original name
Update connection strings if needed

Question

What is the maximum PITR retention period for Azure SQL Database?

Click or press Enter to reveal answer

Answer

35 days. Configurable from 1-35 days (default 7). For retention beyond 35 days, use Long-Term Retention (LTR) policies.

Click to flip back

Question

How long can LTR backups be retained?

Click or press Enter to reveal answer

Answer

Up to 10 years. Policies are set as weekly (W), monthly (M), and yearly (Y) retention periods. LTR backups are full backups stored separately from PITR backups.

Click to flip back

Question

Does PITR on Azure SQL Database overwrite the existing database?

Click or press Enter to reveal answer

Answer

No. PITR always creates a new database. You must rename or swap databases after verifying the restore was successful.

Click to flip back

Knowledge Check

Harbour Health regulations require keeping a monthly backup for 7 years. The database is on Azure SQL Database with default 7-day PITR. What should Amara configure?

🎬 Video coming soon

Next up: Geo-Replication and Failover Groups — configure cross-region disaster recovery for Azure SQL.