Backup, Restore, PITR & LTR
Backup and restore is not the same as HA/DR — it's your last line of defense when everything else fails. HA/DR keeps you running during failures. Backup/restore recovers you from data corruption, accidental deletion, compliance needs, and disasters where HA/DR can't help.
Why Backup ≠ HA/DR
| Scenario | HA/DR Helps? | Backup/Restore Helps? |
|---|---|---|
| Server hardware failure | ✅ | ❌ (too slow) |
| Region outage | ✅ (Failover Groups) | ✅ (Geo-restore) |
Accidental DELETE / DROP TABLE | ❌ (replicates the mistake) | ✅ (PITR) |
| Data corruption | ❌ (corruption replicates) | ✅ (restore pre-corruption point) |
| Ransomware / malicious change | ❌ (encrypted data replicates) | ✅ (restore clean backup) |
| Compliance (retain 7 years) | ❌ | ✅ (LTR) |
| Dev/test environment clone | ❌ | ✅ (restore copy) |
DP-300 trap: Many exam questions describe a corruption or accidental deletion scenario and offer Failover Groups as an option. Failover Groups replicate corruption too — they protect against infrastructure failure, not data-level mistakes. The answer is always PITR or backup restore.
What Microsoft Manages vs What You Manage
| Aspect | Azure SQL DB | SQL MI | SQL on VM |
|---|---|---|---|
| Full backups | ✅ Auto (weekly) | ✅ Auto (weekly) | ❌ You configure |
| Differential backups | ✅ Auto (12-24h) | ✅ Auto (12-24h) | ❌ You configure |
| Log backups | ✅ Auto (5-10 min) | ✅ Auto (5-10 min) | ❌ You configure |
| Backup storage | ✅ Managed | ✅ Managed | ❌ You configure |
| Encryption at rest | ✅ Auto (TDE) | ✅ Auto (TDE) | ❌ You configure |
| Retention period | You set (7-35 days) | You set (7-35 days) | You manage entirely |
| LTR policies | You configure | You configure | You manage (backup to Blob) |
| Backup verification | ✅ Auto | ✅ Auto | ❌ You run RESTORE VERIFYONLY |
| Point-in-time restore | ✅ Built-in | ✅ Built-in | ❌ You restore manually |
The biggest PaaS advantage: On Azure SQL DB and MI, you will never lose a backup and never need to write a backup script. Microsoft takes full, differential, and log backups automatically. Your only job is to set the retention period and LTR policy.
Automated Backup Schedule
| Backup Type | Frequency | You Configure? | Purpose |
|---|---|---|---|
| Full | Weekly | ❌ Automatic | Base for all restores |
| Differential | Every 12-24 hours | ❌ Automatic | Changes since last full |
| Transaction Log | Every 5-10 minutes | ❌ Automatic | Point-in-time granularity |
How PITR Uses These Backups
To restore to a specific point in time, Azure:
- Restores the most recent full backup before that point
- Applies the most recent differential after that full
- Replays transaction log backups up to the exact second you specified
This is why log backups every 5-10 minutes give you RPO of seconds — you can restore to any point within the retention window.
Point-in-Time Restore (PITR)
PITR is the most commonly used restore method for Azure SQL DB and MI.
Key Facts
| Aspect | Detail |
|---|---|
| Retention | 7-35 days (configurable, default 7) |
| Granularity | Any second within the retention window |
| Result | Creates a NEW database (never overwrites original) |
| Speed | Minutes to hours (depends on database size) |
| Cost | New database billed at the same tier |
| Cross-region | ❌ Same region only (use Geo-restore for cross-region) |
PITR creates a NEW database — it never overwrites the existing one. This catches many exam takers. After PITR, you have both the original (possibly corrupted) and the restored copy. You must manually swap them (rename, update connection strings).
PITR Scenarios
| Scenario | Action |
|---|---|
| "Restore to 3 hours ago" | PITR to GETDATE() - 3 hours → new DB |
| "Undo accidental DELETE" | PITR to just before the DELETE → extract missing rows → insert back |
| "Clone for dev/test" | PITR to current time → rename for dev use |
| "Corrupted stored procedure" | PITR to before corruption → script the correct proc → apply to production |
Long-Term Retention (LTR)
For compliance and regulatory requirements beyond 35 days:
| Aspect | Detail |
|---|---|
| Max retention | Up to 10 years |
| Storage | Azure Blob Storage (RA-GRS by default) |
| Policy | Weekly (W), Monthly (M), Yearly (Y) retention |
| Cost | Per GB stored (separate from DB billing) |
| Restore | Creates a new database from LTR backup |
LTR Policy Examples
| Policy | What It Keeps | Use Case |
|---|---|---|
W=4 | Last 4 weekly backups | General disaster recovery |
M=12 | Last 12 monthly backups | Quarterly compliance audits |
Y=7, WeekOfYear=1 | Last 7 yearly (first week of year) | 7-year regulatory retention |
W=4, M=12, Y=10 | Weekly + Monthly + Yearly | Full compliance coverage |
LTR stores full backups only — no differentials or log backups. You cannot do point-in-time restore from an LTR backup. LTR gives you the database state at the time of that weekly backup snapshot.
Backup Storage Redundancy
| Option | Copies | Protection | Cost | Use Case |
|---|---|---|---|---|
| LRS | 3 in one datacenter | Disk/rack failure | Lowest | Dev/test |
| ZRS | 3 across availability zones | Zone failure | Medium | Production (same region) |
| GRS | 6 (3 local + 3 paired region) | Region failure | Higher | Production (cross-region DR) |
| RA-GRS | 6 + read access to secondary | Region failure + read | Highest | Critical compliance |
Backup storage redundancy can only be set at database creation time (or within a short window). You cannot change from LRS to GRS after the fact. Choose based on your DR requirements from day one.
Restore Options by Platform
Azure SQL Database
| Restore Type | RPO | RTO | Creates New DB? | Cross-Region? |
|---|---|---|---|---|
| PITR | Seconds | Minutes-hours | ✅ Yes | ❌ Same region |
| Deleted DB restore | To deletion time | Minutes | ✅ Yes | ❌ Same region |
| Geo-restore | Up to 1 hour | Hours | ✅ Yes | ✅ Any region |
| LTR restore | Weekly snapshot | Hours | ✅ Yes | ✅ Any region |
| Copy | Current point | Minutes | ✅ Yes | ❌ Same server |
SQL Managed Instance
| Restore Type | RPO | RTO | Notes |
|---|---|---|---|
| PITR | Seconds | Minutes-hours | Same as SQL DB |
| Cross-instance restore | Seconds | Minutes | Restore to different MI |
| Geo-restore | Up to 1 hour | Hours | From GRS backup |
| LTR restore | Weekly | Hours | Same as SQL DB |
| Native backup to URL | You control | You control | Manual backup to Azure Blob |
SQL Server on VM
| Restore Type | RPO | RTO | Notes |
|---|---|---|---|
| Native BACKUP/RESTORE | You control | You control | Full control, full responsibility |
| Backup to Azure Blob | You control | Hours | BACKUP TO URL |
| Automated Backup (IaaS Agent) | Configurable | Hours | If SQL IaaS Agent configured |
| Azure Backup for SQL VM | 15 min | Hours | Managed backup vault |
| File-level backup (Azure Backup) | Daily | Hours | VM-level backup (not SQL-aware) |
Azure Backup for SQL Server in Azure VM is a separate service from SQL Server's native backup. It provides centralized backup management, 15-minute RPO, long-term retention in Recovery Services Vault, and no backup scheduling scripts needed.
RPO and RTO Explained
| Term | What It Means | Business Question |
|---|---|---|
| RPO (Recovery Point Objective) | How much data can you afford to lose | "How old can the restored data be?" |
| RTO (Recovery Time Objective) | How long can you be down | "How fast do we need to be back online?" |
RPO/RTO by Solution
| Solution | RPO | RTO | Cost |
|---|---|---|---|
| Automated backups (PITR) | 5-10 min | Hours | Free (included) |
| Geo-restore | Up to 1 hour | Hours | Free (included with GRS) |
| Active Geo-Replication | < 5 seconds | ~30 seconds | 💰 (secondary DB cost) |
| Failover Groups | < 5 seconds | Grace period + ~30s | 💰 (secondary DB cost) |
| BC tier local replica | 0 (synchronous) | < 10 seconds | 💰💰 (BC tier cost) |
| LTR backup | Weekly | Hours | 💰 (storage cost) |
The cost-RPO tradeoff: Automated backups (free) give you hours of RTO and minutes of RPO. Failover Groups (extra DB cost) give you seconds of both. Choose based on your business requirements — not every database needs sub-second RPO.
Common Misunderstandings
| Misconception | Reality |
|---|---|
| "PITR overwrites my database" | ❌ PITR creates a NEW database. Original is untouched. |
| "I can restore to any point from LTR" | ❌ LTR only stores weekly full backups. No point-in-time granularity. |
| "Geo-replication protects against accidental DELETE" | ❌ Deletion replicates instantly. Use PITR instead. |
| "I can change backup redundancy anytime" | ❌ Only at creation (or within a short window). |
| "Azure SQL DB backups are my responsibility" | ❌ Fully automated. You only set retention + LTR policy. |
| "Backup retention of 7 days means 7 recovery points" | ❌ You can restore to ANY SECOND within those 7 days. |
| "PITR can restore to a different region" | ❌ Same region only. Use Geo-restore for cross-region. |
Backup Strategy Decision Matrix
| Requirement | Solution |
|---|---|
| "Recover from accidental deletion" | PITR (within 7-35 day retention) |
| "Recover from data corruption" | PITR (restore to before corruption) |
| "Keep backups for 5+ years" | LTR policy (W/M/Y) |
| "Cross-region disaster recovery" | Geo-restore (hours RTO) or Failover Groups (seconds) |
| "Clone database for dev/test" | PITR to current time or database copy |
| "Recover a deleted database" | Deleted database restore (within retention) |
| "Restore to on-premises" | Export BACPAC or native backup (VM/MI only) |