Recommend a Backup and Recovery Solution for Databases — Lesson

Database backup is the last line of defense for stateful workloads in Azure. When a developer drops the wrong table, a ransomware payload encrypts a production database, or a regional outage takes a primary down, the speed and granularity of your recovery determine whether the business loses minutes, days, or its reputation. This lesson teaches you to recommend the right backup and recovery solution across the database services you will see on AZ-305: Azure SQL Database, Azure SQL Managed Instance, Azure Cosmos DB, Azure Database for PostgreSQL Flexible Server, SQL Server on Azure VMs, and SAP HANA on Azure.

This lesson maps to the AZ-305 study guide's database backup material. The goal is decision-making, not memorisation — given an RPO, an RTO, a retention mandate, and a platform, you should be able to pick the right backup mechanism and configure it correctly.

Why This Matters

Imagine you are the cloud architect for a fictional fintech, Northwind Capital. At $2{:}47$ AM, the on-call engineer accidentally runs a DELETE FROM transactions without a WHERE clause on the production Azure SQL Database. By the time someone notices, $200{,}000$ rows are gone, and the compliance team is asking how far back you can restore. If you configured Point-in-Time Restore correctly, you can rewind to $2{:}46$ AM and keep the business open. If you did not — or if you assumed geo-replication covered logical corruption — you are explaining a seven-figure loss to leadership.

The AZ-305 exam tests this exact judgment. You will see scenarios where you must choose between PITR, Long-Term Retention, geo-restore, continuous backup, and Azure Backup, and the wrong choice changes the RPO from minutes to weeks or pushes monthly cost from $$50$ to $$5{,}000$. Cloud architects who internalise the backup landscape ship resilient designs the first time and never argue with auditors about whether 10-year retention is implied by replication. It is not.

Prerequisites

Before this lesson you should be comfortable with:

• Azure SQL family basics — the difference between Azure SQL Database (PaaS database), Azure SQL Managed Instance (instance-scope PaaS), and SQL Server on Azure VM (IaaS). Self-check: which of the three lets you run cross-database queries with three-part names?
• RPO and RTO — Recovery Point Objective (data loss tolerance) versus Recovery Time Objective (downtime tolerance). Self-check: if your RPO is 0 minutes, can a periodic backup ever satisfy it?
• Storage redundancy — LRS, ZRS, GRS, RA-GRS, GZRS. Self-check: which redundancy survives a full region loss?
• Resource hierarchy and RBAC — subscriptions, resource groups, management groups. Self-check: at which scope do you assign the SQL DB Contributor role to a backup operator?
• Azure Backup core concepts — Recovery Services Vault versus Backup Vault, policies, soft delete. Self-check: which vault type protects PostgreSQL Flexible Server?

Learning Objectives

By the end of this lesson you will be able to:

1. Recommend the correct backup mechanism (PITR, LTR, geo-restore, continuous backup, Azure Backup) for a given Azure database service.
2. Analyse RPO, RTO, and retention requirements and map them to a supported configuration on the chosen platform.
3. Design a multi-tier backup strategy that satisfies regulatory retention (for example, 7 or 10 years) without overpaying.
4. Evaluate the trade-offs between continuous and periodic backup modes on Azure Cosmos DB.
5. Recommend Azure Backup configuration for SQL Server on Azure VMs and SAP HANA on Azure, including vault selection, policy, and security features.
6. Design restore procedures appropriate to the failure mode (logical, regional, ransomware, accidental drop).

Building Blocks

Before the deep dive, anchor these terms.

Point-in-Time Restore (PITR) — Like a video timeline you can scrub backwards on. → A feature of Azure SQL Database and Azure SQL Managed Instance that uses full, differential, and transaction-log backups to restore to any second within the retention window. → It is your primary defense against accidental data corruption inside the database.

Long-Term Retention (LTR) — Like archiving photo albums in a safe deposit box. → Weekly, monthly, and yearly copies of database backups kept for up to 10 years in geo-redundant storage. → It satisfies regulatory mandates (HIPAA, SOX, GDPR) that demand multi-year retention but is not a substitute for PITR.

Geo-Restore — Like keeping a spare key at your sibling's house. → Restores a database from the most recent geo-replicated full plus differential backup into any Azure region. → It is the disaster-recovery option when an entire region is unavailable, but the RPO can be up to 1 hour because geo-backups are copied asynchronously.

Continuous Backup (Cosmos DB) — Like a time machine for a NoSQL database. → A mode in Azure Cosmos DB that retains all changes for 7 or 30 days, enabling restore to any second in that window. → It is the cosmos-native answer to PITR.

Periodic Backup (Cosmos DB) — Like nightly snapshots on a NAS. → The default Cosmos DB mode: full backups every 4 hours, retained for 8 hours by default (up to 720 hours). → It is cheaper than continuous but cannot self-serve restore; you open a support ticket.

Recovery Services Vault — Like the workplace safe specifically for backup artefacts. → The Azure Backup vault used for VMs, file shares, and SQL on VM. → It supports cross-region restore, soft delete, and Multi-User Authorization.

Backup Vault — Like the newer cousin to Recovery Services Vault. → A unified vault for newer workloads: Azure Database for PostgreSQL Flexible Server, Blob, Disk, and Azure Kubernetes Service. → It is the vault you select when the workload is not VM-based.

Soft Delete — Like the recycle bin for backups. → A feature that retains deleted backups for 14 days (configurable up to 180 days on Recovery Services Vault) so a malicious or accidental delete is recoverable. → It is the single most important anti-ransomware control on a backup vault.

Immutable Vault — Like welding the safe shut for a fixed period. → A Backup vault setting that prevents anyone — including a Global Administrator — from shortening retention or deleting recovery points once locked. → It is the audit-defensible choice for regulated industries.

Multi-User Authorization (MUA) — Like requiring two signatures on a cheque. → A control that forces a second approver (Resource Guard) before destructive operations on a vault succeed. → It defends against compromised admin credentials.

Deep Dive

Azure SQL Database and Managed Instance — PITR, LTR, Geo-Restore

Azure SQL Database and Azure SQL Managed Instance ship with automated backups enabled by default. You cannot turn them off. The platform takes full backups weekly, differential backups every 12 to 24 hours, and transaction-log backups every 5 to 10 minutes. These backups are stored in RA-GRS storage by default, configurable to LRS, ZRS, GRS, or GZRS.

The retention window for PITR defaults to 7 days and is configurable from 1 to 35 days. Inside that window, you can restore to any point in time at second-level precision. The restore creates a new database alongside the original — it does not overwrite — so you keep both and migrate data manually if needed.

[!IMPORTANT] PITR retention is the shortest safety net. It does not satisfy a 7-year compliance requirement. For that you need LTR, configured separately.

The storage redundancy you choose at server creation directly affects geo-restore behaviour and cost.

LTR captures the weekly full backup and retains it according to a policy you define. The policy has three independent dials.

LTR backups are billed separately from PITR and are stored in geo-redundant storage. Restoring from LTR creates a new database; the source DB does not need to exist.

For region-wide disaster, geo-restore uses the geo-redundant copy of the most recent full plus differential backup. Because that copy is replicated asynchronously, expect an RPO of up to 1 hour. Geo-restore is always available — even if the source region is fully down — and you can target any Azure region.

[!TIP] If your RPO requirement is tighter than 1 hour for regional failure, geo-restore alone is not enough. Pair it with an auto-failover group so a secondary replica in another region is continuously synchronised.

Azure SQL Managed Instance supports the same PITR/LTR model but adds COPY_ONLY backup to URL for ad-hoc backups to a storage account. That escape hatch is the only way to get a full .bak file out of Managed Instance, useful for migrations or off-Azure copies.

Cosmos DB — Continuous versus Periodic Backup

Azure Cosmos DB decouples backup from the data plane: you choose the mode at account creation, or migrate later from periodic to continuous (one-way).

The table below summarises the two modes.

[!WARNING] Once an account is on continuous mode, you cannot switch back to periodic. Choose with that in mind — pricing is roughly 2× to 4× higher.

Continuous mode is the right choice when the application demands self-serve recovery (developer-facing platforms, multi-tenant SaaS) or when RPO targets exceed what 4-hour snapshots can satisfy. Periodic mode is acceptable for analytics workloads where data is reproducible from upstream sources.

Azure Database for PostgreSQL and MySQL — Vault-Based Long-Term Retention

Azure Database for PostgreSQL Flexible Server and Azure Database for MySQL Flexible Server ship with built-in automated backups retained for 1 to 35 days (daily full plus transaction logs). That covers PITR and geo-restore.

For retention beyond 35 days, configure Azure Backup with a Backup Vault. The PostgreSQL workload uses a logical pg_dump-style backup, stored as compressed blobs in the vault, retained up to 10 years. The MySQL workload uses physical backups via Azure Backup integration.

[!NOTE] Geo-redundant backup on PostgreSQL Flexible Server must be enabled at server creation and cannot be turned on later. If the requirement is regional failover via geo-restore, design it in from day one.

SQL Server on Azure VMs — Azure Backup for SQL

For SQL Server on Azure VMs, the platform does not automate backups. You enable the SqlIaasExtension (or install the Azure Backup SQL Server agent), then create a backup policy in a Recovery Services Vault.

The big difference versus Azure SQL DB: you own the recovery model choice (Simple, Full, Bulk-Logged). PITR via log backups requires Full mode. Mixing modes across databases on the same instance is fine.

[!TIP] Enable soft delete (14-day default, up to 180 days) on the Recovery Services Vault. Combined with Multi-User Authorization on the vault's Resource Guard, this is the standard anti-ransomware posture for SQL on VM.

SAP HANA on Azure — Snapshot and Streaming Backup

SAP HANA on Azure (Large Instances) and HANA on VMs use a specialised path: Azure Backup supports HANA through a backint integration, taking streaming backups directly from HANA Studio or hdbsql.

Recovery components include snapshot backups of HANA data and log volumes for fast recovery, backint streaming to a Recovery Services Vault for off-host retention, and cross-region restore to the paired Azure region. Retention follows the same vault policy model as SQL on VM. For HANA the design point is the snapshot frequency — every 30 minutes is typical, with full backups daily and log backups every 15 minutes.

Worked Examples

Easy — Choosing PITR Retention for a Startup SaaS

Problem. Bluefin Analytics runs a multi-tenant SaaS on a single Azure SQL Database (General Purpose, Gen5, 4 vCore). They want to recover from accidental data loss up to 4 weeks back, but have no regulatory retention requirement. What do you recommend?

Step 1 — Map the requirement to a feature. Up to 4 weeks back implies a PITR window of 28 days, which fits inside the 1 to 35 day PITR range. LTR is not needed.

Step 2 — Set the property. Configure pitrRetentionDays = 28 on the database. Storage redundancy can stay at the default Geo (RA-GRS).

Step 3 — Verify cost. PITR backup is included in the database price up to 1× the provisioned DB size. Beyond that, charged at roughly $$0.10$/GB-month for RA-GRS. A 50 GB database at 28-day retention is well within free PITR.

[!NOTE] Key insight — PITR is effectively free within the data-size budget. Stretching retention from 7 days to 35 days rarely triggers excess charges for OLTP workloads.

Medium — Satisfying a 10-Year Compliance Retention

Problem. A healthcare provider runs Azure SQL Managed Instance for patient records. HIPAA mandates 10-year retention of weekly snapshots. The CISO also requires the backup be protected against admin compromise. Design the solution.

Step 1 — PITR is insufficient. Max PITR is 35 days. You need LTR.

Step 2 — Configure LTR. Set yearly retention to 10 years and weekly retention to 4 weeks for operational use. Anchor on weekOfYear = 1.

Step 3 — Protect against admin compromise. Enable soft delete on the LTR backups and configure the Managed Instance to use GZRS backup storage redundancy for zone and region resilience.

Step 4 — Lock retention. Because LTR for Managed Instance does not currently use a Backup Vault, use Azure Policy and deny change to backupLongTermRetentionPolicies from anyone outside the backup operator group. Pair with SQL Auditing on the server subscription.

[!NOTE] Key insight — LTR for Azure SQL stores backups inside the SQL service, not in a Backup Vault, so vault-level features like Immutable Vault do not apply. Use Azure Policy plus RBAC plus Activity Log alerts as the compensating control.

Hard — Cross-Region Recovery for a NoSQL E-Commerce Platform

Problem. Contoso Retail runs a global e-commerce site on Azure Cosmos DB for NoSQL with write regions in East US and West Europe. They need (a) developer-self-service point-in-time restore for 2 weeks, (b) ability to restore to a different region during a regional outage, and (c) protection against ransomware that mass-deletes containers. RTO target: 4 hours. Recommend a backup design.

Step 1 — Mode selection. Periodic gives 4-hour snapshots and no self-service. Continuous 7-day is too short. Continuous 30-day mode satisfies (a) and (b). The cost increase is acceptable for a revenue-critical platform.

Step 2 — Cross-region restore. With continuous mode, the Cosmos restore picker offers any region in the account's location list. Add a third read region (for example, Central US) if rolling restores in additional regions is part of the runbook.

Step 3 — Ransomware defence. Continuous backup retains every change for 30 days, so a mass-delete is recoverable as long as someone notices within that window. Pair with Microsoft Defender for Cosmos DB alerts on anomalous query patterns. Configure RBAC to restrict Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/delete to a break-glass principal protected by PIM plus MUA.

Step 4 — Restore drills. Mandate a quarterly restore-to-staging exercise. Document RTO measurements. Target restore time for a 500 GB account is 2 to 4 hours, fitting the 4-hour RTO.

[!IMPORTANT] Key insight — Cosmos continuous backup does not replace cross-region writes for HA. It is a recovery tool, not a failover tool. Auto-failover handles availability; continuous backup handles corruption.

Visual Explanations

Decision Tree — Which Backup Mechanism?

Caption: Service-first decision tree for backup mechanism. Walk top to bottom; the leaf is the recommended baseline. Pair every choice with the matching anti-ransomware controls described in the Deep Dive.

TikZ — Backup Data Flow for Azure SQL DB

Caption: How automated SQL backups flow from the primary database into the PITR window and, when LTR is configured, into long-term geo-redundant storage. The async-copy step is the source of the up-to-1-hour geo-restore RPO.

Comparison Table — Backup Features by Database Service

Caption: Quick reference for matching capability to service. Manual means you configure Azure Backup or equivalent yourself.

Common Mistakes

❌ Myth: Geo-replication of an Azure SQL Database means I do not need backup. ✅ Reality: Geo-replication copies every change — including the DELETE FROM transactions mistake. Backups protect against logical corruption; replication protects against infrastructure failure. You need both. Why it's tricky: High availability gets conflated with data protection in marketing copy. The two solve different problems.

❌ Myth: Enabling LTR in Azure SQL DB will retroactively start covering existing backups. ✅ Reality: LTR captures the next weekly full backup after you configure the policy. Backups taken before LTR was enabled are not retained beyond PITR. Why it's tricky: Auditors sometimes assume retention is retroactive. Document the LTR enable date and gap-fill via a manual COPY_ONLY backup if required.

❌ Myth: Cosmos DB continuous backup is the same thing as continuous data export to ADLS. ✅ Reality: Continuous backup is an internal restore mechanism — you cannot read the backup as files. Continuous export via the Analytical Store or Change Feed is the export option. Why it's tricky: Both sound continuous and both serve Cosmos DB, but only one restores the database; the other feeds analytics.

❌ Myth: Enabling soft delete on a Recovery Services Vault is enough to defeat ransomware. ✅ Reality: A compromised admin can disable soft delete before the malicious delete. Soft delete plus Multi-User Authorization via a Resource Guard in a separate subscription is the defensible posture. Why it's tricky: Soft delete is on by default and feels like a complete solution. The realistic threat model assumes the attacker already has the admin's credentials.

Practice Exercises

Exercise 1 🟢 — PITR Window Selection

A development team wants to rewind the database 48 hours after each weekly release for testing. The database is Azure SQL Database. What is the minimum PITR retention to safely support this?

▶💡 Hint

PITR window must be at least the rewind horizon plus a buffer for the test window.

▶✅ Solution

Configure PITR for 7 days (the default). A 48-hour rewind needs only a 2-day window, but 7 days is the default and incurs no extra cost; there is no reason to reduce below it.

Exercise 2 🟢 — Identifying the Right Vault

A team needs to back up Azure Database for PostgreSQL Flexible Server for 5-year retention. Which vault type do they configure?

▶💡 Hint

PostgreSQL Flex is one of the newer workloads.

▶✅ Solution

Backup Vault. The Backup Vault supports PostgreSQL, Disk, Blob, and Kubernetes workloads. Recovery Services Vault is for VMs, file shares, and SQL on VM.

Exercise 3 🟡 — RPO Failure Analysis

You configured Azure SQL Database with backupStorageRedundancy = Geo. The primary region fails and you initiate geo-restore. Users report 42 minutes of data loss. Is this within spec?

▶💡 Hint

What is the documented RPO upper bound for geo-restore?

▶✅ Solution

Yes. Geo-restore RPO is documented as up to 1 hour because backups are asynchronously copied to the paired region. 42 minutes is within spec. If the team needs sub-15-minute RPO across regions, add an auto-failover group with active geo-replication.

Exercise 4 🟡 — Choosing Cosmos Backup Mode

A multi-tenant SaaS on Cosmos DB for NoSQL requires that customers be able to recover their own deleted documents within a 7-day support SLA. The platform team wants the lowest-cost option that meets the SLA. Recommend the mode.

▶💡 Hint

Self-service restore narrows the choice.

▶✅ Solution

Continuous 7-day mode. It is the cheapest tier that allows self-service portal restore. Periodic does not satisfy self-service because restores require a support ticket. Continuous 30-day costs roughly 2× continuous 7-day, which is unjustified given the 7-day SLA.

Exercise 5 🔴 — Ransomware Design

A regulated bank needs the strongest practical ransomware defence for backups of SQL Server on Azure VM. Design the vault and policy configuration.

▶💡 Hint

Layer: storage redundancy, soft delete, immutability, MUA, RBAC, separation of duties.

▶✅ Solution

Use Recovery Services Vault with GZRS redundancy. Enable soft delete with 180-day retention (paid tier). Turn on Immutable Vault and lock the state. Configure Multi-User Authorization with the Resource Guard in a separate subscription owned by a separate team (separation of duties). Restrict vault management plane via Azure RBAC; require PIM just-in-time activation for Backup Contributor. Mandate Cross Region Restore to the paired region. Add Azure Monitor alerts on Disable soft delete, Stop protection, and Delete backup data activity-log signals.

Exercise 6 🔴 — Multi-Platform Retention Strategy

A pharmaceutical client runs Azure SQL MI (clinical records), Cosmos DB for MongoDB (telemetry), and PostgreSQL Flexible Server (research). Regulators require 7 years retention on clinical and research data, 90 days on telemetry. Design a unified backup strategy with clear ownership.

▶💡 Hint

Three different platforms means three different mechanisms — but a single retention policy framework.

▶✅ Solution

SQL MI: PITR 35 days plus LTR yearly 7 years, GZRS storage. Owner: DBA team. Cosmos DB telemetry: continuous 30-day mode (covers the 90-day window via account-level export to ADLS for the remaining 60 days). Owner: platform team. PostgreSQL Flex research: built-in PITR 35 days plus Backup Vault LTR 7 years with Immutable Vault. Owner: data platform team. Central governance: Azure Policy initiative enforcing the three retention floors; Activity Log workbook in a central Log Analytics workspace; quarterly restore-drill schedule. Vaults: separate Recovery Services Vault per platform team in dedicated subscriptions with MUA wired through a shared Resource Guard.

Exercise 7 🟡 — Geo-Restore versus Failover Group

A SaaS company asks: We already have an auto-failover group for our Azure SQL Database. Do we still need geo-redundant backups? Answer with rationale.

▶💡 Hint

What does each mechanism protect against?

▶✅ Solution

Yes, keep geo-redundant backups. Auto-failover groups protect against infrastructure loss in a region — they replicate every transaction including malicious ones. Geo-redundant backups (LTR plus PITR with Geo redundancy) protect against logical corruption and meet retention requirements. Removing geo-backups would leave a single point of failure for ransomware recovery.

Exercise 8 🔴 — Cost Optimisation

A team's Azure SQL Database (2 TB) has PITR retention set to 35 days and LTR weekly retention for 7 years on a single yearly anchor. Monthly backup cost is $$4{,}200$. Recommend optimisations without breaking compliance.

▶💡 Hint

Two big levers: storage redundancy and PITR length.

▶✅ Solution

Drop PITR from 35 days to 14 days if the operational rewind window allows; saves PITR storage above the free included 2 TB. Move backupStorageRedundancy from Geo to Zone (ZRS) for PITR if regional DR is already covered by a failover group — saves roughly 25% on PITR. Keep LTR at Geo for compliance. Audit LTR weekly frequency — if regulators require yearly only, drop weekly retention and keep yearly 7 years. Often a $\sim 70\%$ reduction in LTR cost. Estimated saving: $$1{,}600$ to $$2{,}500$ per month while keeping compliance.

Summary and Concept Map

• PITR is the default rewind; configure 1 to 35 days for Azure SQL, SQL MI, and PostgreSQL/MySQL Flex. It is included in service pricing within the data-size budget.
• LTR satisfies regulatory retention of up to 10 years; weekly, monthly, and yearly anchors stored in geo-redundant storage. Not retroactive.
• Geo-restore is the region-loss fallback for Azure SQL with an RPO of up to 1 hour; pair with auto-failover groups when you need a tighter RPO.
• Cosmos DB: choose continuous for self-service restore; choose periodic for cost. Once on continuous, you cannot go back.
• Azure Backup with Recovery Services Vault handles SQL on VM and SAP HANA; the newer Backup Vault handles PostgreSQL Flex, Disk, Blob, and Kubernetes.
• Anti-ransomware stack: soft delete plus Immutable Vault plus Multi-User Authorization plus Cross-Region Restore plus RBAC plus Activity Log alerts. Treat each as additive, not alternative.
• Verify with restore drills. Configured retention without a proven restore is theoretical.