Database Backup & DR
SQL PITR · LTR · Geo-restore · Failover groups · Geo-replication · Cosmos DB · SQL on VMs
Practice questions for this topic →
Azure SQL recovery options
| Option | Retention / RPO | Use for |
|---|---|---|
| Point-in-time restore (PITR) | 1–35 days | Undo recent mistakes (restores to a new database) |
| Long-term retention (LTR) | Up to 10 years (weekly, monthly, yearly + week of year); immutable option in SQL Database only | Compliance |
| Geo-restore | From geo-redundant backups; RPO about 1 hour and RTO up to about 12 hours (quoted from the docs in a Microsoft Q&A answer; Learn confirms the most recent replicated backups and that large restores can take hours) | Cheapest regional DR |
| Active geo-replication | Async, readable secondaries (Microsoft's 2016 announcement says up to 4), manual failover, connection string changes | Per-database DR and readable replicas (SQL Database only) |
| Failover group | Async, listeners don't change, automatic failover with a grace period, many databases together | Regional DR with an unchanged connection string; SQL Database and Managed Instance |
Backups: full weekly, differential every 12–24 hours, log about every 10 minutes. Backup storage is geo-redundant by default. Hyperscale uses snapshot-based backups.
Other databases
| Database | Backup / DR |
|---|---|
| Cosmos DB continuous backup | 7, 30 or 35-day tiers; self-service point-in-time restore into a new account; periodic to continuous is one-way; backups taken in every region without using RUs |
| Cosmos DB periodic backup | Default for existing accounts; restore needs a support request; at most about a month; minimum interval 1 hour |
| Cosmos DB multi-region | Add regions plus service-managed failover; multi-region writes for near-zero RTO |
| SQL Server on Azure VMs | Always On availability groups (HA + DR), failover cluster instance (HA), log shipping (DR), Azure Backup, ASR |
| PostgreSQL / MySQL flexible | Zone-redundant HA, geo-redundant backup, cross-region read replicas |
Facts to know
- Hyperscale: if the primary fails, failover to an HA replica is automatic and quick, the connection string doesn't change, and apps may see dropped connections (use retry logic). Named replicas are separate read resources, not failover targets.
- Read-only workloads on General Purpose or Standard have no HA replicas: use a geo-replica, or the failover group read-only listener.
- LTR on Managed Instance can't be made immutable; copy-only backups to your own immutable storage are the workaround. Synapse Link analytical store data isn't included in Cosmos backups.
- SQL on VMs: use 3 or more odd quorum votes and give no votes to DR-region nodes; deploy across multiple subnets to avoid depending on a load balancer or distributed network name.
Exam Tips
Tick each tip once you can answer it without looking.
“Connection string must not change after regional failover” → Failover group. Trap: Active geo-replication has no listener.
“Readable secondaries in three other regions for one database” → Active geo-replication (up to 4). Trap: Failover groups have one partner.
“Regional DR for SQL Managed Instance” → Failover group. Trap: Active geo-replication isn't available for MI.
“Keep monthly SQL backups for 7 years” → Long-term retention.
“Developer dropped a table 3 hours ago” → Point-in-time restore to a new database.
“Cheapest DR for SQL; RPO of about 1 hour acceptable” → Geo-restore from geo-redundant backups. Trap: The one-hour RPO is quoted from the docs in a Microsoft Q&A answer; Learn confirms it uses the most recent replicated backups and can take hours.
“Cosmos DB self-service restore to any point in last 7 days” → Continuous backup (7-day tier). Trap: Periodic mode needs a support ticket.
“SQL Server on VMs: automatic failover across zones and readable secondaries” → Always On availability group across zones.
“Failover group triggers automatically after an outage” → Microsoft-managed policy with grace period (default 60 min), or trigger customer-managed failover.
“Hyperscale primary fails and HA replicas exist” → Automatic, quick failover to an HA replica; connection string unchanged; apps should retry dropped connections. Trap: Named replicas are separate resources, not HA failover targets.
“Read-only workload on General Purpose or Standard” → Use a geo-replica (SQL Database) or a failover group read-only listener. Trap: Those tiers have no HA replicas for read scale-out.