Performing a Point-in-Time Restore of an Azure SQL Database

Desired outcome

Restore an Azure SQL Database to a specific timestamp within its configured backup retention window, creating a new database that can be used for recovery, testing, or forensic analysis.

Prerequisites

• The source database must be in a service tier that supports automated backups (Basic, Standard, Premium, General Purpose, or Business Critical).
• Backup retention must be configured for the server/database (default 7‑35 days depending on tier).
• Your Azure AD user or service principal needs the following RBAC actions on the source server:
  • Microsoft.Sql/servers/databases/read
  • Microsoft.Sql/servers/databases/restore/action
• If you plan to use Azure CLI or PowerShell, ensure the latest Azure CLI (az) or Azure PowerShell module is installed and you are logged in (az login).

Focused procedure

You can perform the restore either through the Azure portal or via Azure CLI. Both methods achieve the same result; the CLI example is shown for repeatability.

Portal method

1. In the Azure portal, navigate to SQL databases and select the source database.
2. Under the Operations section, choose Point-in-time restore.
3. Set the Restore point to the desired timestamp (e.g., 2026-09-30T14:20:00Z). Use the calendar and time picker to ensure the time falls within the displayed retention window.
4. Choose a Target server. You can restore to the same logical server or a different one (including a server in another region if geo‑backup is enabled).
5. Provide a Target database name that does not already exist on the target server (e.g., ContosoSales_Restored).
6. Review the summary, then click OK to start the restore operation.

Azure CLI method

Run the following commands in a terminal where you have the required permissions. Replace placeholders with your actual values.

# Variables – edit these for your environment
SOURCE_SERVER="mysqldbserver"
SOURCE_DB="ContosoSales"
RESTORE_TIME="2026-09-30T14:20:00Z"   # ISO‑8601 UTC
TARGET_SERVER="mysqldbserver"          # can be same or different
TARGET_DB="ContosoSales_Restored"

# Initiate the point‑in‑time restore
az sql db restore \
  --resource-group myResourceGroup \
  --server $SOURCE_SERVER \
  --name $SOURCE_DB \
  --dest-name $TARGET_DB \
  --dest-server $TARGET_SERVER \
  --time $RESTORE_TIME

The command returns immediately; the restore runs asynchronously. You can monitor progress in the portal under SQL databases → Operations → Restore history or by querying:

az sql db show --resource-group myResourceGroup \
  --server $TARGET_SERVER --name $TARGET_DB \
  --query "status" -o tsv

When the status changes to Online, the restore is complete.

Expected checks

1. Confirm the database is online

   Run the following T‑SQL query against the restored database (you can use Azure Data Studio, SSMS, or the portal query editor).

   SELECT DATABASEPROPERTYEX('ContosoSales_Restored', 'Status') AS Status;
   

   Expected result: Online.

2. Run integrity check

   DBCC CHECKDB ('ContosoSales_Restored') WITH NO_INFOMSGS;
   

   Look for 0 allocation errors and 0 consistency errors in the output. Any non‑zero count indicates corruption and warrants further investigation.

3. Application‑level verification

   Update your application connection string to point to ContosoSales_Restored (or create a test connection string). Execute a simple query that reflects a known data point at the chosen timestamp, for example:

   SELECT COUNT(*) FROM Sales.OrderHeader WHERE OrderDate < '2026-09-30T14:20:00Z';
   

   Compare the returned count to the value you expect from the source database at that time (you may have recorded it before the incident). If the numbers match, the restore has recovered the data correctly.

Recovery options and rollback

The point‑in‑time restore operation creates a new database; it does not alter the source database. If the restored database does not meet your needs, you can:

• Delete the restored database to stop incurring compute and storage charges:

  az sql db delete --resource-group myResourceGroup \
    --server $TARGET_SERVER --name $TARGET_DB --yes
  

• Retry with a different timestamp (still within the retention window) by repeating the restore steps.
• Geo‑restore (if geo‑replication is enabled) to recover from a regional outage.
• Long‑term backup retention (if configured) to recover points beyond the standard window.

Limitations and practical considerations

• Retention window: Point‑in‑time restore is only possible within the configured backup retention period (default 7‑35 days). Attempting to restore outside this window fails with an error like Requested restore point is outside the backup retention range.
• Performance impact: The restore operation consumes DTUs/vCores on the target server. Schedule restores during off‑peak periods if the target server hosts other workloads.
• Cost: The restored database incurs the same compute and storage charges as any active database until you delete it or scale it down (e.g., to a Basic tier).
• Naming constraints: The target database name must be unique on the target server; Azure will reject the operation if a database with that name already exists.

Verification checklist

• Portal shows Restore succeeded or Azure CLI returns a JSON with state: "Online".
• SELECT DATABASEPROPERTYEX('<targetDb>', 'Status') = 'Online'.
• DBCC CHECKDB reports zero errors.
• Application query returns expected row counts or values matching the source at the chosen timestamp.