When designing a business continuity and disaster recovery (BCDR) strategy in Microsoft Azure, protecting your data from corruption, human error, and full-scale regional outages is non-negotiable.
Whether you are running Azure SQL Database (SQL DB) or Azure SQL Managed Instance (SQL MI), Azure provides built-in, automated backup mechanisms to safeguard your workloads.
Table of Contents
- What is Point-in-Time Restore (PITR)?
- What is Geo-Restore?
- PITR vs Geo-restore in SQL MI or SQL DB
- When to Use Point-in-Time Restore (PITR)?
- When to Use Geo-Restore?
There are three methods to protect your data backups in Azure SQL (SQL DB and SQL MI):
- Point in time restore (PITR)
- Geo-restore
- Long term retention
Understanding when to deploy PITR versus Geo-Restore is critical to meeting your organization’s Recovery Point Objective (RPO) and Recovery Time Objective (RTO). Let’s break down exactly when and how to use each tool.
What is Point-in-Time Restore (PITR)?
Point-in-Time Restore is your primary defense against localized data corruption and administrative mistakes. PITR leverages a continuous stream of transaction log backups alongside weekly full and bi-weekly differential backups. This granular architecture enables administrators to roll a database back to any exact millisecond within a pre-configured retention window using Azure Portal or Azure CLI or PowerShell.
When to Use PITR?
PITR is designed for operational recovery within a healthy, fully functioning Azure region. You should choose PITR in the following scenarios:
- Accidental Human Error: A developer or database administrator accidentally executes a destructive, unconstrained DELETE and UPDATE statement on a production table.
- Application Code Failures: A buggy software deployment or data migration script corrupts application data or introduces logical inconsistencies across relational tables.
- Malicious Injections or Ransomware: A security breach compromises a live database, requiring you to roll back the system to the precise minute before the unauthorized modifications took place.
- Database Seeding for Dev/Test: You need to spin up a duplicate database as of a specific historical timestamp (e.g., yesterday morning) to debug an application error without affecting production.
Key Metrics to Know
According to Microsoft’s official documentation on Azure SQL backup capabilities, PITR supports a configurable short-term retention period of 1 to 35 days for active databases. The default retention is 7 days. Because backups are continuous, the RPO for a PITR is virtually zero (typically 5-10 minutes or seconds of data loss), though the RTO depends heavily on database size, as transaction logs must be replayed sequentially.
Single-Region Deployment Strategy
If your application can tolerate downtime during an Azure regional failure, a single-region deployment is the most cost-effective choice.
- How PITR works here: You configure your PITR short-term retention anywhere between 1 to 35 days using Local (LRS) or Zone-Redundant (ZRS) backup storage. (Note: GRS backup redundancy will become Geo-restore)
- The Critical Catch: If the entire Azure region goes down, you cannot perform a PITR recovery. The backup infrastructure resides in that same compromised region. As you noted, your data is not lost, but it is completely inaccessible. You must wait for Microsoft to bring the region back online before you can access or roll back your data.
- Best Used For: Non-production environments, development/testing stages, or internal business tools where temporary downtime does not impact revenue or violate SLAs.
What is Geo-Restore?
Geo-Restore is a disaster recovery mechanism designed to protect your organization from catastrophic infrastructure failures. It relies entirely on geo-redundant storage (RA-GRS or GZRS), which automatically replicates your database backups from your primary Azure region to a designated secondary paired region (e.g., from East US to West US).
Compared to the three types of backups in PITR, Geo-restore simply replicates copies of PITR backups to a secondary paired region.
Please note that database backups are only replicated to the paired secondary region. If you create a SQL MI in two non-paired regions, you can still recover the backup in another region (paired or non-paired) but not in another subscription.
Read the official documentation about this restore capabilities in Azure SQL MI or SQL DB Automatic, Geo-Redundant Backups – Azure SQL Managed Instance | Microsoft Learn
Geo-Restore is strictly reserved for high-severity emergency scenarios where your primary infrastructure is completely inaccessible. You should use Geo-Restore when:
- A Full Regional Outage Occurs: A natural disaster, widespread power failure, or major data center disruption knocks out the entire primary Azure region housing your SQL DB or SQL MI deployment.
- The Primary Instance is Permanently Destroyed: The underlying hardware or instance hosting your active database is unrecoverable, and you must re-establish operations in a secondary geographic location.
- Cost-Effective BCDR is Preferred over High Availability: Your business requires a cross-region disaster recovery plan, but you do not have the budget to maintain an expensive, active-passive secondary instance using Failover Groups.
Key Metrics to Know
Because geo-replication of backups happens asynchronously, Microsoft outlines strict BCDR parameters for Geo-Restore. It offers a Recovery Point Objective (RPO) of up to 1 hour, meaning you could lose up to 60 minutes of data from the time the last backup was replicated. Additionally, the Recovery Time Objective (RTO) is estimated at up to 12 hours, as Azure must provision new infrastructure resources in the secondary region to host the restored data.
PITR vs Geo-restore in SQL MI or SQL DB
| Feature / Metric | Point-in-Time Restore (PITR) | Geo-Restore |
| Primary Use Case | Human error, data corruption, deployment failure | Severe regional data center outages (Disaster Recovery) |
| Target Destination | Same region (same or different instance/subscription) | Geographically paired secondary Azure region. Can be restored to any Azure region. |
| Recovery Point Objective (RPO) | Milliseconds to minutes | Up to 1 hour |
| Recovery Time Objective (RTO) | Minutes to hours (depends on DB size/log size) | Up to 12 hours |
| Storage Dependency | Local (LRS) or Zone-Redundant (ZRS) Storage | Geo-Redundant Storage (GRS/GZRS) |
When to Use Point-in-Time Restore (PITR)?
1. Single-Region Architecture
If cross-region disaster recovery (RPO/RTO) is not a critical requirement for your application, you can deploy SQL Managed Instance in a single region.
- Backup Strategy: Configure your backup storage redundancy as Locally Redundant (LRS) or Zone-Redundant (ZRS) to minimize storage costs.
- Retention: Set the PITR window anywhere between 1 and 35 days based on your specific business compliance and operational recovery needs.
2. Multi-Region Architecture (Failover Groups)
For High Availability and Disaster Recovery (HADR) scenarios requiring a strict RPO (near zero) and rapid RTO, you should set up an auto or customer managed Failover Group between a primary and secondary region.
- Backup Strategy: Because the failover group actively replicates your data to a secondary instance, you do not need to pay extra for geo-redundant backups. You can configure your PITR backup redundancy as LRS or ZRS to save on costs.
- Cost Optimization (Failover Rights): Running a secondary instance incurs compute and storage costs. However, by applying Azure SQL Failover Rights, you receive a 100% discount on the vCore compute licensing for the standby replica.
- Important Constraint: To legally maintain this discount, the secondary instance must remain purely passive; you cannot route production read-only workloads to it.
When to Use Geo-Restore?
1. Single-Instance Deployment (Cost-Effective DR)
If your business can tolerate an RPO of up to 1 hour and an RTO of up to 12 hours during a regional outage, you do not need the expense of a running secondary instance.
- Backup Strategy: Enable Geo-Restore by selecting Geo-Redundant (GRS) or Geo-Zone-Redundant (GZRS) storage for your backups.
- Recovery Execution: If the primary region or instance goes offline, you simply provision a brand-new SQL Managed Instance in a different region and use Geo-Restore to recover the database from the replicated backups. Since the RPO for Geo-restore is at least 1 hour, you can set up the SQL MI in another region in the meantime and restore the database once the backup becomes available.
Read Also:
Different database restore methods in Azure SQL MI
Step-by-Step Guide: Creating an Azure SQL Managed Instance (Pay-As-You-Go & Free Offer)
2. Multi-Region Architecture (Failover Group Configured)
If you have already configured a multi-region Auto or Customer managed Failover Group, you do not need to configure Geo-Restore (GRS/GZRS backups).
Why? Paying for geo-redundant backup storage is completely redundant in this scenario. Your Auto-Failover Group already handles regional outages with vastly superior RPO and RTO metrics. You can rely on the secondary instance to take over during a disaster, relying on standard LRS/ZRS backups for local point-in-time recoveries.
Citation Reference: For the most up-to-date configuration limits, commands, and SLA architectures regarding backup retention, please consult the official Microsoft Azure documentation on Automated backups – Azure SQL Database & SQL Managed Instance.
If you have any questions about the when to use PITR and Geo-restore, feel free to reach out to me on LinkedIn or leave a comment below, and I’ll respond as quickly as I can. For more technical blog posts, check out cloudnerchuko.in.