SQL Server Point-in-Time Restore with STOPAT

calendar Apr 5, 2026 / · 8 min read · sql server restore backup recovery transaction log msdb database maintenance DBA scripts point in time restore  ·
Share on: linkedin copy

Recover a SQL Server Database to a Specific Point in Time

SQL Server's RESTORE LOG ... WITH STOPAT lets you roll a database back to any exact moment covered by your backup chain. This guide walks through querying backup history in msdb, executing the restore sequence, and verifying the result.

Purpose and Overview

Data loss incidents — accidental deletes, bad deployments, runaway updates — rarely happen at a convenient backup boundary. Point-in-time restore solves this by replaying transaction logs only up to a target timestamp, leaving the database in a consistent state as of that moment.

The process requires three things: a Full backup taken before the target time, an optional Differential backup to reduce the number of log files needed, and every Transaction Log backup that spans from that baseline through the target timestamp. SQL Server enforces LSN (Log Sequence Number) continuity across the chain, so gaps will abort the restore.

The script below covers all three steps: querying msdb to identify the right backup files, running the restore sequence with NORECOVERY, and finishing with STOPAT to bring the database online at the exact recovery point.

Code Breakdown

The full script is shown here, followed by a section-by-section explanation.

 1-- =============================================
 2-- Step 1: Find the correct backup chain in msdb
 3-- for the target database and recovery time
 4-- =============================================
 5DECLARE @DatabaseName   SYSNAME  = N'YourDatabaseName';
 6DECLARE @TargetDateTime DATETIME = '2026-04-10 14:30:00'; -- Set your target recovery time
 7
 8-- Find the last Full backup BEFORE the target time
 9SELECT TOP 1
10    'Full' AS backup_type,
11    bs.backup_start_date,
12    bs.backup_finish_date,
13    bs.first_lsn,
14    bs.last_lsn,
15    bmf.physical_device_name AS backup_file
16FROM msdb.dbo.backupset bs
17JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
18WHERE bs.database_name = @DatabaseName
19  AND bs.type = 'D'
20  AND bs.backup_finish_date <= @TargetDateTime
21ORDER BY bs.backup_finish_date DESC;
22
23-- Find the last Differential backup AFTER the full, BEFORE the target time
24SELECT TOP 1
25    'Differential' AS backup_type,
26    bs.backup_start_date,
27    bs.backup_finish_date,
28    bs.first_lsn,
29    bs.last_lsn,
30    bmf.physical_device_name AS backup_file
31FROM msdb.dbo.backupset bs
32JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
33WHERE bs.database_name = @DatabaseName
34  AND bs.type = 'I'
35  AND bs.backup_finish_date <= @TargetDateTime
36ORDER BY bs.backup_finish_date DESC;
37
38-- Find all Log backups needed to reach the target time
39SELECT
40    'Log' AS backup_type,
41    bs.backup_start_date,
42    bs.backup_finish_date,
43    bs.first_lsn,
44    bs.last_lsn,
45    bmf.physical_device_name AS backup_file
46FROM msdb.dbo.backupset bs
47JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
48WHERE bs.database_name = @DatabaseName
49  AND bs.type = 'L'
50  AND bs.backup_start_date <= @TargetDateTime
51ORDER BY bs.backup_start_date ASC;
52
53-- =============================================
54-- Step 2: Execute the point-in-time restore
55-- Replace file paths from the query results above
56-- =============================================
57
58-- Restore Full backup with NORECOVERY
59RESTORE DATABASE [YourDatabaseName]
60FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
61WITH NORECOVERY, STATS = 10;
62
63-- Restore Differential backup with NORECOVERY (if applicable)
64RESTORE DATABASE [YourDatabaseName]
65FROM DISK = N'C:\Backups\YourDatabaseName_Diff.bak'
66WITH NORECOVERY, STATS = 10;
67
68-- Apply log backups in order, using NORECOVERY until the last one
69RESTORE LOG [YourDatabaseName]
70FROM DISK = N'C:\Backups\YourDatabaseName_Log1.bak'
71WITH NORECOVERY;
72
73-- Final log: use STOPAT to recover to the exact point in time
74RESTORE LOG [YourDatabaseName]
75FROM DISK = N'C:\Backups\YourDatabaseName_LogFinal.bak'
76WITH RECOVERY, STOPAT = '2026-04-10 14:30:00';
77
78-- =============================================
79-- Step 3: Verify the database is online
80-- =============================================
81SELECT name, state_desc, recovery_model_desc
82FROM sys.databases
83WHERE name = N'YourDatabaseName';

Step 1 Variables

@DatabaseName and @TargetDateTime are declared at the top so you set the recovery target in one place. All three discovery queries reference these same variables, reducing the risk of a mismatch between the backup chain query and the restore commands that follow.

Finding the Full Backup

The first SELECT TOP 1 query filters msdb.dbo.backupset for type 'D' (Full) backups that finished on or before the target time, ordered newest-first. The result gives you the physical_device_name — the backup file path — and the first_lsn / last_lsn values you can cross-check against the differential and log results to confirm the chain is intact.

Finding the Differential Backup

Type 'I' identifies Differential backups. This query is identical in structure to the Full query. If no differential exists between your full backup and the target time, skip this restore step and go directly to applying log backups. When a differential is available, using it reduces the number of log files that must be applied.

Finding the Log Backups

The Log query (type 'L') does not use TOP 1 — it returns all log backup entries whose backup_start_date falls at or before the target time, ordered oldest-first. You need every file in that list applied in sequence. Any gap in LSN continuity will cause the restore to fail with an error pointing to the missing sequence.

Restore Full with NORECOVERY

NORECOVERY leaves the database in a restoring state so additional backups can be applied. STATS = 10 prints a progress message every 10% of completion — useful for large backups. Do not use RECOVERY here or on any intermediate step.

Restore Differential with NORECOVERY

The same NORECOVERY rule applies. Restore the differential only if the Step 1 query returned a result. The differential must come from the same full backup base — confirm this by comparing first_lsn values.

Applying Log Backups

Each intermediate log restore uses WITH NORECOVERY. Apply them in the exact order returned by the Step 1 log query. If you have many log files, scripting a loop or using a third-party tool to generate the restore sequence reduces manual error.

STOPAT — The Final Log Restore

The last log file is restored with WITH RECOVERY, STOPAT = '2026-04-10 14:30:00'. RECOVERY brings the database online. STOPAT tells SQL Server to stop applying log records at that exact timestamp, rolling back any uncommitted transactions that started before but extended past the target time. After this statement completes, the database is accessible and consistent as of the specified moment.

Verification Query

The final SELECT against sys.databases confirms state_desc = 'ONLINE' and shows the recovery_model_desc. If the database is still in RESTORING state, the RECOVERY step did not complete — check for errors in the restore output.

Key Benefits and Use Cases

Undoing accidental data changes. When a developer runs a DELETE or UPDATE without a WHERE clause, point-in-time restore lets you recover to the minute before the statement ran, without losing all work that occurred before that moment.

Post-deployment rollback. If a schema migration corrupts data, you can restore to the exact timestamp before the deployment began rather than to the last scheduled backup, which may be hours earlier.

Forensic investigation. Restoring to a read-only copy at a specific time lets you inspect the state of the data at that point without affecting the production database. This is useful for audits and compliance reviews.

Minimizing data loss window. Combined with frequent transaction log backups (every 5–15 minutes), STOPAT recovery can reduce data loss to a very small window even in environments without log shipping or Always On.

Performance Considerations

Restore duration scales with backup size and log volume. A full backup of 500 GB takes significantly longer to restore than one of 50 GB. Differentials exist precisely to reduce the number of log files needed, cutting total restore time.

STATS = 10 has low overhead. The progress reporting does not slow the restore meaningfully and is worth keeping in production scripts for visibility.

Disk I/O is the primary bottleneck. Reading large backup files from slow storage (spinning disk, network share) dominates restore time. Where possible, stage backup files on local fast storage before starting the restore sequence.

Log backup frequency affects precision. If log backups run every hour, STOPAT can only recover to within the last log backup's coverage window. More frequent log backups give finer-grained recovery points.

Tail-log backup for live databases. When recovering a database that is still online (rather than a restored copy), take a tail-log backup first with WITH NORECOVERY, NO_TRUNCATE to capture log records not yet in a backup file. This extends the recovery chain to the moment just before the database goes offline.

Practical Tips

Always run the Step 1 queries before starting the restore. Confirm the file paths exist on disk. A missing backup file discovered mid-restore means starting over.

Check LSN continuity manually if uncertain. Compare last_lsn of the full backup against first_lsn of the differential; compare last_lsn of the differential against first_lsn of the first log backup. Any gap means the chain is broken.

Restore to a separate database name first. Use WITH MOVE to restore the files to a different database name on the same instance, verify the data, then either rename databases or copy the recovered data back to production. This avoids taking production offline during verification.

Document the target timestamp precisely. Store your @TargetDateTime value in an incident ticket before starting. If you need to adjust the recovery point, you will need to repeat the full restore sequence from the beginning.

msdb history must be intact. If backup history has been purged from msdb (via sp_delete_backuphistory) or the database was backed up on a different server, the Step 1 queries will return no rows. In that case, use RESTORE HEADERONLY directly against the backup files to read backup metadata.

Test your restore procedure regularly. A backup that has never been tested in a restore is an untested backup. Schedule periodic point-in-time restore drills on a non-production server.

Conclusion

Point-in-time restore with STOPAT is one of the most precise recovery tools available in SQL Server. The three-step pattern — query msdb to identify the backup chain, apply the full and differential backups with NORECOVERY, then apply logs ending with RECOVERY, STOPAT — gives you exact control over where in time the database lands. Keeping frequent transaction log backups and verifying LSN chain continuity before starting are the two practices that make this technique reliable under pressure.

References

Posts in this series