Study guide
8.4 Database Backup & Recovery Design
Exam AZ-305: Designing Microsoft Azure Infrastructure Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Orientation
1.1 AZ-305 Current Exam Facts & Format 1.2 Candidate Profile, Prerequisites & the AZ-104 Requirement 1.3 Skills Outline, Domain Weights & How to Use This Guide
2 Chapter 2: Logging, Monitoring & Diagnostics Design
2.1 Logging Solution Design 2.2 Log Routing Design 2.3 Azure Monitor Metrics & Alerting Design 2.4 Application Insights & Diagnostic Monitoring Design
3 Chapter 3: Authentication & Authorization Design
3.1 Authentication Solution Design 3.2 Identity Management Design 3.3 Authorization for Azure Resources 3.4 Authorization for On-Premises Resources 3.5 Secrets, Certificates & Key Management Design
4 Chapter 4: Governance, Compliance & Identity Governance Design
4.1 Management Group & Subscription Hierarchy Design 4.2 Resource Group Organization & Tagging Strategy Design 4.3 Compliance Management Design 4.4 Identity Governance Design
5 Chapter 5: Relational Data Storage Design
5.1 Relational Data Storage Selection 5.2 Database Service & Compute Tier Design 5.3 Database Scalability Design 5.4 Relational Data Protection Design
6 Chapter 6: Semi-Structured & Unstructured Data Storage Design
6.1 Semi-Structured Data Storage Design 6.2 Unstructured Data Storage Design 6.3 Storage Tier & Cost-Performance Trade-off Design 6.4 Unstructured Data Protection & Durability Design
7 Chapter 7: Data Integration & Analytics Design
7.1 Data Integration Pipeline Design 7.2 Event-Based & Real-Time Data Integration Design 7.3 Data Analytics Platform Design
8 Chapter 8: Backup & Disaster Recovery Design
8.1 Recovery Objectives Design: Calculating and Meeting RTO/RPO 8.2 Azure Site Recovery Architecture for Hybrid Workloads 8.3 Compute Backup & Recovery Design 8.4 Database Backup & Recovery Design 8.5 Unstructured Data Backup & Recovery Design
9 Chapter 9: High Availability Design
9.1 Compute High Availability: Availability Sets & Availability Zones 9.2 Compute High Availability: Load Balancing & VM Scale Set Design 9.3 Relational Data High Availability Design 9.4 Semi-Structured & Unstructured Data High Availability Design
10 Chapter 10: Compute Solution Design
10.1 Compute Requirements Analysis & Virtual Machine-Based Solution Design 10.2 Container-Based Solution Design (AKS, Container Apps, Azure Container Instances) 10.3 Serverless-Based Solution Design (Azure Functions, Logic Apps) 10.4 Batch Processing Solution Design (Azure Batch, HPC Workloads)
11 Chapter 11: Application Architecture Design
11.1 Messaging Architecture Design (Service Bus, Queue Storage) 11.2 Event-Driven Architecture Design (Event Grid, Event Hub) 11.3 API Integration & Application Caching Design (API Management, Azure Cache for Redis, CDN) 11.4 Application Configuration Management & Automated Deployment Design (App Configuration, DevOps Pipelines, Deployment Slots)
12 Chapter 12: Migration Design
12.1 Migration Assessment: Cloud Adoption Framework & On-Premises Evaluation 12.2 IaaS & PaaS Workload Migration Design 12.3 Database Migration Design (Data Migration Service, Database Migration Assistant) 12.4 Unstructured Data Migration Design (AzCopy, Data Box, Storage Migration Service)
13 Chapter 13: Network Solution Design
13.1 Internet Connectivity Design 13.2 Hybrid Connectivity Design 13.3 Network Security Optimization Design 13.4 Network Performance Optimization & Load-Balancing/Routing Design
14 Chapter 14: Final Review, Exam Strategy & Career Path
14.1 Cross-Domain Scenario Practice: Combining Identity, Data, Continuity & Infrastructure Decisions 14.2 Common AZ-305 Decision Traps 14.3 Final Study Plan & Practice Assessment Strategy 14.4 After AZ-305: Retake Policy, Renewal & Career Path
8.4 Database Backup & Recovery Design
Key Takeaways Explain This Topic AI
Automated backups combine weekly full, ~12-24-hour differential, and ~5-10-minute transaction log backups to support point-in-time restore (PITR), which always creates a new database rather than overwriting the original.
PITR retention defaults to 7 days and is configurable up to 35 days on Standard/Premium/vCore tiers; Basic tier is fixed at 7 days.
Long-Term Retention (LTR) keeps full backups for up to 10 years with independently configurable weekly/monthly/yearly policies -- the correct answer for multi-year compliance requirements that exceed the PITR window.
Geo-restore requires geo-redundant (GRS/RA-GRS) backup storage redundancy, recovers into a new database in any region, and carries an RPO of up to one hour with an RTO of several hours.
Geo-restore is a backup-recovery mechanism, not a high-availability feature -- near-zero-RPO automatic failover requirements need auto-failover groups or active geo-replication instead.
Last updated: July 2026
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
- Service tier Default PITR retention Configurable range
- Basic tier 7 days Fixed at 7 days
- Standard/Premium/vCore tiers 7 days Configurable up to 35 days
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
- Requirement Correct mechanism
- "Recover from an accidental data deletion 3 days ago" Point-in-time restore
- "Keep audited backups for regulatory review 5 years from now" Long-term retention
"Primary region is down; recover the database anywhere with RPO around 1 hour" Geo-restore (requires GRS backup storage redundancy)
"Automatic failover with near-zero data loss" Not a backup mechanism at all -- this needs auto-failover groups/active geo-replication (a high-availability feature, covered elsewhere)
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.