Skip to article
Databases Azure SQL

Point-in-time restore

Point-in-time restore means choosing a time before something went wrong and restoring a database from backup history to that moment. It is not a magic undo button for every application issue; it usually creates a new database or restored instance that teams must verify, reconnect, or copy from. Operators use it after accidental deletes, bad migrations, data corruption, or failed releases. The important parts are the retention window, the restore target, the timestamp, and the validation work after restore.

Source: Microsoft Learn - Recovery using backups - Azure SQL Database Reviewed 2026-05-19

Exam trap
Choosing the restore timestamp from memory instead of using logs, deployment times, and application telemetry to bracket the corruption window.
Production check
Can you identify the source database, logical server, retention window, and latest safe timestamp from evidence rather than guesswork?
Practice this topic Microsoft Learn
Article details and learning context
Aliases
None listed
Difficulty
intermediate
CLI mappings
5
Last verified
2026-05-19
Compare related concepts Search related resources Browse the glossary

Understand the concept

Why it matters

Point-in-time restore matters because the most common data incidents are not regional disasters; they are human mistakes, failed schema changes, destructive scripts, or application defects that corrupt good data. Without a rehearsed restore path, teams waste critical time debating which backup exists, who can restore it, where it should land, and how to prove the recovered data is usable. A well-understood restore process gives developers a safer migration path, gives operators a recovery playbook, and gives auditors evidence that retention settings match the business recovery objective. It turns panic recovery into a planned operational motion. It also clarifies ownership when recovery requires application, database, and security teams.

Official wording and source

Point-in-time restore uses service-managed backup history to create a recoverable database copy at a selected time inside the configured retention window. In Azure SQL and similar database services, it is used after deletion, corruption, failed releases, or operator mistakes without overwriting the original database.

Open Microsoft Learn

Technical context

In Azure architecture, point-in-time restore sits in the database protection and recovery layer. For Azure SQL Database, automated backups create the restore history, and the restore operation is scoped to the logical server, database, region, and configured retention period. The result is normally a separate restored database, not an in-place rewind of production. CLI, ARM, and portal restore operations depend on resource group scope, database name, server identity, edition, backup storage redundancy, and permissions to create the target resource.

Exam context

Compare with

Long-term backup retention Cross-region restore Instant restore

Where it is used

Where you see it

  1. In the Azure SQL database Restore blade, operators select a timestamp, target database name, server, region, compute tier, and backup storage option before creating the recovered copy.
  2. In Azure CLI output from az sql db restore, the restored database shows status, edition, service objective, creation time, source database, and resource group for change evidence.
  3. In Activity Log and deployment records, restore operations appear as database create or restore events with caller identity, timestamps, target resource ID, and any authorization failures.

Common situations

  • Recover a database copy after a bad deployment, destructive script, accidental delete, or data corruption event inside the retention window.
  • Create a validated recovery database before deciding whether to repair selected rows, reconnect an application, or perform a controlled cutover.
  • Run restore drills that prove backup retention, restore duration, RBAC, private networking, and cleanup steps meet the business recovery objective.
  • Compare current data with a restored point to identify when corruption began and limit repair work to the affected records.
  • Support audit or compliance evidence by documenting the source database, restore timestamp, target database, approvals, and post-restore validation results.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Recovering port booking data after a failed release Scenario, objectives, solution, measured impact, and takeaway.
Scenario

HarborLine Systems ran an Azure SQL-backed booking service for container yard appointments. A release changed slot-status logic and overwrote thousands of future appointments before the defect was caught.

Goals
  • Recover valid appointment data from the hour before the release.
  • Avoid disconnecting carriers from unaffected live bookings.
  • Prove exactly which records needed repair before merging data.
  • Remove temporary recovery resources after incident closure.
Approach using Point-in-time restore

The database team used point-in-time restore to create a separate Azure SQL database from the timestamp fifteen minutes before the release. They kept the restored copy behind the same private endpoint pattern, enabled auditing, and scaled it temporarily for comparison queries. Application engineers compared appointment IDs, carrier references, and status transitions between production and the restored copy. Instead of pointing the application at the restored database, they generated a controlled repair script for only the corrupted future appointments. Azure CLI captured the restore command, database SKU, activity log, and cleanup record for the incident report.

Potential outcomes
  • Customer booking access stayed online while the repair set was prepared.
  • The team recovered 8,400 appointment records with no full database cutover.
  • Comparison queries reduced the suspected corruption window from four hours to 38 minutes.
  • The temporary restored database was deleted within 24 hours under a tracked cleanup task.
What to learn

Point-in-time restore is most valuable when it gives teams a safe recovery copy they can validate before touching production data.

Scenario 02 Protecting research grant records during schema migration Scenario, objectives, solution, measured impact, and takeaway.
Scenario

Lakeshore University managed sponsored research budgets in Azure SQL Database. A planned schema migration would rewrite award milestones, cost-share records, and department approval flags across several schools.

Goals
  • Rehearse restore before the production migration weekend.
  • Validate that backup retention covered the full approval freeze period.
  • Keep finance analysts out of the production database during testing.
  • Document recovery evidence for the research compliance office.
Approach using Point-in-time restore

The platform team created a restore drill two weeks before the migration. Using point-in-time restore, they generated a temporary database from a pre-freeze timestamp and connected it only to a secured validation subnet. Analysts checked award balances, milestone dates, and approval flags against exported reports while engineers measured restore duration and validation query time. The final runbook included the exact Azure CLI restore syntax, naming convention for the restored database, required RBAC roles, firewall checks, and deletion steps. During the real migration, the team captured a fresh restore point and kept the practiced process ready as the rollback option.

Potential outcomes
  • The restore drill completed in 52 minutes, giving the team a realistic recovery expectation.
  • Retention settings were increased before the freeze after the first review found a gap.
  • No analysts needed direct access to production during validation.
  • Compliance reviewers accepted CLI output and audit records as recovery evidence.
What to learn

Rehearsed point-in-time restore turns a risky database migration into a change with measurable recovery evidence.

Scenario 03 Restoring inventory analytics after bad supplier data Scenario, objectives, solution, measured impact, and takeaway.
Scenario

FreshRoute Foods loaded supplier availability feeds into an Azure SQL reporting database used by buyers. One supplier sent malformed inventory records that caused purchasing forecasts to overstate refrigerated stock.

Goals
  • Identify the last clean reporting state before malformed supplier data arrived.
  • Keep the purchasing dashboard online during investigation.
  • Recover only affected supplier rows instead of rolling back all analytics.
  • Show buyers when corrected forecast data would be available.
Approach using Point-in-time restore

Engineers used point-in-time restore to create a database copy from the hour before the supplier batch landed. They compared supplier IDs, product categories, and forecast deltas between the restored database and the current reporting database. The restored copy was scaled up for validation, locked behind approved analyst access, and tagged with incident ownership. Data engineers then rebuilt only the affected supplier segment, reran forecast procedures, and validated the dashboard output before the morning buying cycle. CLI and activity log evidence showed the exact restore time and temporary database lifecycle.

Potential outcomes
  • Forecast corrections were published before the next buying window opened.
  • Only 3 percent of reporting rows required rebuild instead of a full rollback.
  • The dashboard stayed available, with a banner explaining the affected supplier feed.
  • The post-incident review added supplier feed validation before future loads.
What to learn

Point-in-time restore gives data teams a precise comparison baseline when current data is wrong but not everything needs rollback.

Azure CLI

As an Azure engineer with ten years of recovery work behind me, I use Azure CLI for point-in-time restore because incidents need repeatable evidence, not screenshots. CLI commands capture the source database, restore timestamp, target name, service objective, region, and caller context in a form that can be pasted into a change record or rerun in a drill. The portal is fine for learning, but CLI makes recovery runbooks testable, lets teams compare settings before and after restore, and supports automation for cleanup. It also reduces confusion when several people are coordinating database, application, network, and security tasks during a real data incident.

Useful for

  • List candidate Azure SQL databases and confirm the exact logical server before restore.
  • Run az sql db restore with a reviewed timestamp and target database name.
  • Export restored database properties, SKU, status, and resource ID for the incident record.
  • Compare dev, test, and production retention settings before approving migration risk.

Before you run a command

  • Confirm tenant, subscription, resource group, logical server, source database, target region, and restore timestamp; a wrong timestamp can waste hours during recovery.
  • Verify the caller has permission to read the source database and create the restored database, including any required provider registration and policy allowances.
  • Treat restore as cost-impacting and operationally sensitive; pick a clear target name, avoid overwriting assumptions, and choose JSON output for evidence capture.
  • Check private endpoint, firewall, Microsoft Entra authentication, and diagnostic requirements before applications or analysts connect to the restored copy.

What the output tells you

  • The restored database ID, name, server, region, status, SKU, and creation timestamp confirm where the recovery copy landed and whether it is ready for validation.
  • The restore point and source database fields show whether the selected recovery time matches the approved incident timeline.
  • Errors usually distinguish authorization, policy, server scope, name collision, unsupported region, or service-tier problems, which guides the next safe recovery step.
  • SKU and status fields help decide whether the restored copy can support validation queries or needs temporary scaling before comparison work starts.

Mapped commands

Azure SQL restore and recovery commands

direct 
az sql db list --server <sql-server> --resource-group <resource-group> --output table
az sql dbdiscoverDatabases
read-only Microsoft Learn 
az sql db show --name <database> --server <sql-server> --resource-group <resource-group> --output json
az sql dbdiscoverDatabases
read-only Microsoft Learn 
az sql db restore --dest-name <restored-database> --edition <edition> --name <database> --resource-group <resource-group> --server <sql-server> --time <utc-restore-time>
az sql dbprotectDatabases
mutating-cost-impacting Microsoft Learn 
az sql db show --name <restored-database> --server <sql-server> --resource-group <resource-group> --output json
az sql dbdiscoverDatabases
read-only Microsoft Learn 
az monitor activity-log list --resource-group <resource-group> --offset 7d --output table
az monitor activity-logdiscoverDatabases
read-only Microsoft Learn

Architecture context

A seasoned Azure architect treats point-in-time restore as part of the database operating model, not as a button hidden in the portal. The design starts with retention, recovery objectives, access control, naming for restored copies, networking, and validation steps. In Azure SQL, the restore usually creates a new database on a logical server, so the architecture must handle connection string changes, private endpoint reachability, firewall rules, monitoring, and cleanup. For production systems, PITR should be tested before a real incident, especially after major schema migrations. The restore target also needs enough compute and storage to validate data quickly without accidentally becoming a shadow production environment.

Security
Security impact is direct because a restored database contains the same sensitive data that existed at the selected time. The restore operation should require tightly scoped RBAC, change approval, and clear ownership of the restored copy. Network rules, private endpoints, Microsoft Entra authentication, auditing, encryption settings, and data classification should be checked immediately after restore. A restored copy used for investigation can become an exposure path if it is placed on a weaker server, left with public access, or forgotten after the incident. Secrets in applications and pipelines must not be changed casually until the recovered target is approved and protected.
Cost
Cost impact is direct once the restored database exists. The restore operation can create a billable database with compute, storage, backup, monitoring, and possibly private networking costs. Longer retention, higher service tiers, geo-redundant backup storage, and multiple validation copies all increase spend. During incidents, teams sometimes over-provision the restored copy to speed validation, which can be justified but should be tracked. FinOps ownership matters because abandoned restored databases look like normal resources. Cost reviews should include restore drills, retained backups, temporary databases, log ingestion, cleanup automation, and the business cost of missed recovery objectives. Tagging restored copies helps chargeback and prevents forgotten recovery resources.
Reliability
Reliability impact is direct because point-in-time restore is the practical recovery path for many database failures that do not require full regional failover. It depends on automated backups, retention settings, service health, the selected timestamp, restore duration, and the team’s ability to validate data before routing traffic. A restore that has never been tested is only an assumption. Reliable operations include scheduled restore drills, documented RPO expectations, clear rollback decisions after migrations, and monitoring for failed restore jobs. The blast radius is reduced when restored copies are isolated and validated before production dependencies change or data is merged.
Performance
Performance impact is mostly operational, but it still affects recovery. Restore duration depends on database size, backup history, service tier, region, and current platform conditions. The restored database may also need scaling before validation queries, consistency checks, exports, or application smoke tests complete quickly. PITR does not make the original workload faster, and it should not be treated as an online rollback mechanism. Poorly planned validation can become the bottleneck, especially when teams run heavy comparison queries on an undersized restored copy or move large datasets across network boundaries during recovery and reconciliation work. Sizing should match the validation workload rather than default assumptions.
Operations
Operators manage point-in-time restore by checking backup retention, confirming the database and server scope, selecting the restore timestamp, creating a restored database, and validating data before any cutover. Azure CLI helps capture the command, target name, region, edition, and timestamps for the change record. After restore, teams inspect activity logs, database status, connection rules, diagnostic settings, and application dependency maps. Good runbooks define who approves the restore, how recovered data is compared, how applications reconnect, and when temporary restored databases are deleted to avoid confusion, excess cost, and unauthorized access. Change records should include commands, approvals, evidence links, and cleanup ownership.

Common mistakes

  • Choosing the restore timestamp from memory instead of using logs, deployment times, and application telemetry to bracket the corruption window.
  • Leaving restored databases online after investigation, creating unmanaged cost and an unnecessary copy of sensitive production data.
  • Routing applications to the restored database before validating schema, security settings, connection rules, and data completeness.
  • Assuming point-in-time restore replaces a tested high-availability or regional disaster recovery strategy.