8.4 Database Backup & Recovery Design

Why This Topic Matters

This section covers the official skill "Recommend a backup and recovery solution for databases" -- the business-continuity half of database protection, focused on recovery mechanics, retention windows, and cross-region restore behavior. (Data-protection concerns like Transparent Data Encryption and auditing are covered elsewhere in the data storage domain; here the emphasis is purely on backup, point-in-time restore, and disaster recovery for Azure SQL Database and SQL Managed Instance.) Exam scenarios in this area almost always give you a required retention window or an RPO/RTO pair and ask you to choose between point-in-time restore, long-term retention, or geo-restore.

Automated Backup Mechanics

Azure SQL Database and SQL Managed Instance take automated backups with no administrative action required:

• Full backups -- taken roughly weekly.
• Differential backups -- taken roughly every 12-24 hours, capturing changes since the last full backup.
• Transaction log backups -- taken frequently, roughly every 5-10 minutes, enabling fine-grained point-in-time recovery.

These three backup types combine to support point-in-time restore (PITR): restoring the database to any point within the retention window, not just to a backup boundary.

Point-in-Time Restore (PITR) Retention

PITR restores create a new database (with a new name, on the same or a different server) rather than overwriting the original -- this is a frequently tested detail, since candidates sometimes assume PITR performs an in-place rollback.

Long-Term Retention (LTR)

For compliance or audit requirements beyond the PITR window, Long-Term Retention (LTR) keeps full backups for up to 10 years, stored in Azure Blob Storage, with independently configurable weekly, monthly, and yearly retention policies (for example: keep every weekly backup for 12 weeks, every monthly backup for 12 months, and every yearly backup for 7 years). A scenario stating "must retain financial records for 7 years for regulatory audit" is describing LTR, not an extended PITR window -- PITR tops out at 35 days regardless of tier.

Geo-Restore and Backup Storage Redundancy

• Backup storage redundancy determines where automated backups (both short-term/PITR and LTR) are physically stored: locally redundant (LRS), zone-redundant (ZRS), or geo-redundant (GRS/RA-GRS). Geo-redundant storage is the default for most new deployments.
• Geo-restore recovers a database from geo-redundant backups into a new database in any region, used when the primary region is completely unavailable. Its documented RPO is up to one hour because backup replication to the paired region can lag, and its RTO can extend to several hours since it must provision a new database and copy data before the database is usable.
• If backup storage redundancy is set to LRS or ZRS instead of GRS, geo-restore is not available at all -- a scenario that requires geo-restore as a DR mechanism must configure GRS/RA-GRS backup storage redundancy from the start.

Choosing Between the Three Recovery Mechanisms

That last row is the most commonly tested trap: candidates recommend geo-restore for a near-zero-RPO automatic-failover requirement, when geo-restore's up-to-one-hour RPO and manual, multi-step recovery process cannot meet that bar. Backup-based recovery (PITR, LTR, geo-restore) is fundamentally different from replication-based high availability -- backup mechanisms always involve creating a new database from stored data, never an already-synchronized standby that can fail over in seconds.

Worked Scenario

A healthcare company must keep monthly database backups for 6 years for regulatory compliance, must be able to undo an accidental bulk-delete from earlier today, and wants the ability to recover the database in a different region if the primary region fails entirely, accepting up to one hour of data loss in that disaster case. The design: configure backup storage redundancy to GRS, enable PITR (default 7-35 day window handles the same-day bulk-delete recovery), configure LTR with a 6-year monthly retention policy, and rely on geo-restore for the full-region-failure case. Each of the three stated needs maps to exactly one of PITR, LTR, or geo-restore -- no single feature covers all three.

Common Decision Traps

• Assuming PITR alone satisfies multi-year compliance retention -- PITR caps out at 35 days; only LTR reaches years.
• Forgetting that geo-restore requires GRS/RA-GRS backup storage redundancy -- an LRS-configured database cannot use it.
• Treating geo-restore as a high-availability feature with fast, automatic failover -- it is a backup-recovery mechanism with hours-scale RTO and up to an hour of RPO, provisioned on demand.
• Assuming a PITR restore overwrites the original database -- it always creates a new database that must then be renamed or repointed.