13.4 Point-in-Time Restore and Recovery Operations

PITR on Azure SQL Database

Azure SQL Database point-in-time restore (PITR) is a first-class platform operation. It is log-based: the platform reconstructs the database state at the target time by replaying the automated log backups on top of the closest full and differential backups. The operation creates a new database with a new name on the same logical server (cross-server PITR is not supported via the portal; you restore to the same server then move it). Key constraints the exam tests:

• The target time must fall within the configured PITR retention window (7 days by default, configurable from 1 to 35 days).
• The restore always creates a new database; you cannot overwrite the original in place. If you need the original name, you rename the source database, then rename the restored database to the original name.
• You trigger PITR via the Azure portal (select the database, Restore to a date/time), PowerShell (Restore-AzSqlDatabase -PointInTime), Azure CLI (az sql db restore), or REST API. T-SQL is not the primary path on Azure SQL Database.
• The pricing tier of the restored database can be the same or different from the source; you pay for the restored database while it exists.
• Dropped database restore is a variant: if you drop a database, you can restore it from the automated backups for the duration of the retention window after deletion (configurable, default 7-35 days depending on offering), selecting the dropped database's deletion time as the target.

A frequent scenario: a user accidentally ran DELETE without a WHERE clause. The answer is to restore the database to a point in time just before the deletion, validate the data in the restored copy, then either extract the missing rows with a cross-database query or repoint the application to the restored database after renaming.

PITR on SQL Managed Instance

SQL Managed Instance's automated backups are stored in a way that is not directly user-restorable via T-SQL, so a user-initiated PITR requires you to have taken your own copy-only full and log backups to URL (Azure Blob Storage) in advance. The restore then uses native T-SQL:

RESTORE DATABASE [SalesDB] FROM URL = 'https://acct.blob.core.windows.net/bk/SalesDB_full.bak'
  WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM URL = 'https://acct.blob.core.windows.net/bk/SalesDB_log1.trn'
  WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM URL = 'https://acct.blob.core.windows.net/bk/SalesDB_log2.trn'
  WITH RECOVERY, STOPAT = '2026-08-03T13:45:00';

The exam's trap on SQL MI: if you have not taken your own copy-only backups, T-SQL PITR is not available - you must use the portal/PowerShell platform operation that restores from the automated backup chain to a new database, similar to Azure SQL Database. Always check which path the scenario implies.

PITR on SQL Server on Azure VMs

On a VM, PITR is the classic native sequence: restore the most recent full backup WITH NORECOVERY, restore the most recent differential (if any) WITH NORECOVERY, then restore each log backup in sequence WITH NORECOVERY, and finally restore the last applicable log backup WITH RECOVERY and STOPAT:

RESTORE DATABASE [SalesDB] FROM DISK = '/backups/SalesDB_full.bak' WITH NORECOVERY;
RESTORE DATABASE [SalesDB] FROM DISK = '/backups/SalesDB_diff.bak' WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM DISK = '/backups/SalesDB_log1.trn' WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM DISK = '/backups/SalesDB_log2.trn'
  WITH RECOVERY, STOPAT = '2026-08-03T13:45:00';

The log chain must be unbroken from the full backup to the STOPAT time. If a log backup is missing or the chain was broken (e.g., a switch to SIMPLE recovery model), PITR cannot proceed past the gap. The exam frequently inserts a broken chain as the trap: the correct answer acknowledges that recovery is only possible up to the last intact log backup.

Geo-Restore

Geo-restore recovers a database from the geo-replicated backups stored in the paired region when the primary region is unavailable or as a DR option. It is available for Azure SQL Database and SQL MI. Differences from PITR:

• Geo-restore uses the most recent geo-replicated backup, so the RPO is longer (the lag of geo-replication, typically up to an hour).
• The RTO is longer because you restore to a new server in the paired region and repoint clients.
• It works when the source region is down, where PITR does not.

The exam contrast: PITR is for accidental data loss within a healthy region with low RPO/RTO; geo-restore is for regional disaster with longer RPO/RTO.

Recovery vs No-Recovery Sequencing

WITH RECOVERY brings the database online and completes the restore; no further backups can be applied. WITH NORECOVERY keeps the database in a restoring state so additional differential or log backups can be applied. The exam tests the ordering mistake: applying the final log with WITH RECOVERY before realizing another log backup exists. The fix is to re-run the restore sequence, or restore the missed log with WITH RECOVERY if the database is still in restoring state. If the database has already been brought online, the only option is to restart the entire restore from the full backup.

Restore Considerations by Feature

Several features require special handling during restore:

• TDE: a TDE-protected database restore requires the TDE protector (certificate or asymmetric key) to be available on the target server, especially when restoring across instances. On Azure SQL Database/MI with service-managed TDE this is transparent; with customer-managed keys (BYOK) the target must have access to the same key in Azure Key Vault or HSM. A missing protector is a common restore failure scenario.
• Memory-optimized tables (In-Memory OLTP): the restore includes the memory-optimized filegroup; the target instance must support In-Memory OLTP (correct service tier), and the memory footprint must be available or the database will not come online.
• FILESTREAM / FileTable: the filestream container path must be available on the target; cross-instance restore requires matching filestream configuration.
• Contained databases: a contained database user maps to a database-scoped principal, so it travels with the database; non-contained SQL logins do not and must be re-created on the target.
• Encrypted backups: the certificate or asymmetric key used to encrypt the backup must be present on the target instance with the correct password or master key.

Verifying the Recovery

Before declaring recovery complete, run verification:

• RESTORE VERIFYONLY during the restore chain confirms the backup media is readable before applying; it is a pre-restore check, not a post-restore check.
• DBCC CHECKDB on the recovered database (after WITH RECOVERY brings it online) checks logical and physical integrity. Run it on every restored database before allowing traffic.
• For a partial restore, validate the recovered filegroups with DBCC CHECKDB scoped to those filegroups.

A common pitfall scenario: an operator runs RESTORE ... WITH RECOVERY and declares success without DBCC CHECKDB; latent corruption in the backup media surfaces later under load. The exam's correct answer always includes the post-restore integrity check.

Common Restore Pitfalls

The exam recurs on a few classic mistakes:

1. Missing log in the chain: a log backup was deleted or never taken; PITR cannot bridge the gap.
2. Wrong STOPAT time: a time zone mismatch (UTC vs local) lands the restore at the wrong moment; Azure backups are in UTC.
3. Overwriting the source: trying to restore in place over a running database without WITH REPLACE or over a database in use; the restore fails. Always restore to a new name or take the source offline first.
4. Wrong MOVE paths: restoring to a Linux instance with Windows file paths (or vice versa) fails; match the target OS paths with WITH MOVE.
5. Forgetting the TDE protector: a BYOK-encrypted database restored to a server without key access fails to come online; provision the key first.
6. Capacity on the secondary: restoring a large database to a server without storage or compute headroom fails or runs slowly.