Proving the Restore, Part 10: Can You Reach That Point in Time?
Someone asks you to restore a database to 14:30 yesterday, just before a bad deployment. The answer is either a time estimate or “the closest I can get is 14:15”, and which one it is depends on backups that already exist or don’t.
That question can be answered from the headers in seconds, without starting anything.
What STOPAT needs
STOPAT recovers to a specific point in time, and in a restore sequence it’s the log restore that lands on the target.[1] For it to work, a log backup has to contain the target moment.
Concretely, you need a log backup whose range spans your target time, restored last in the sequence with STOPAT and RECOVERY. Every log backup before it restores normally with NORECOVERY.
The requirement people run into is that STOPAT can’t invent log records. If the last log backup finished at 14:15 and the next one hadn’t run when the server was lost, 14:30 isn’t reachable from those files. Nothing about the syntax changes that.
Checking before you start
The reachability test is whether a log backup covers the target:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @stopat datetime2(3) = '2026-08-17T14:30:00'; SELECT [h].[Position] , [h].[BackupStartDate] , [h].[BackupFinishDate] , [h].[FirstLSN] , [h].[LastLSN] FROM #header_results AS [h] WHERE [h].[BackupType] = 2 /* transaction log */ AND [h].[BackupStartDate] <= @stopat AND [h].[BackupFinishDate] >= @stopat ORDER BY [h].[Position]; |
A row means the target sits inside that backup, and it’s the one to finish on. No rows means the target isn’t covered, and the reason matters.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @stopat datetime2(3) = '2026-08-17T14:30:00'; SELECT [earliest_log] = MIN([h].[BackupStartDate]) , [latest_log] = MAX([h].[BackupFinishDate]) , [verdict] = CASE WHEN @stopat < MIN([h].[BackupStartDate]) THEN N'Before the available log range' WHEN @stopat > MAX([h].[BackupFinishDate]) THEN N'After the last log backup' ELSE N'Inside the range, check for a gap at the target' END FROM #header_results AS [h] WHERE [h].[BackupType] = 2; |
Three answers, three different conversations. Before the range means the backups you have don’t go back far enough, and older files may exist elsewhere. After the last backup means the target is later than anything captured, so the best available point is MAX(BackupFinishDate). Inside the range with no covering backup means a log backup is missing from your set, which is the gap check from part 8.
Reporting the best reachable time alongside the verdict is more useful than reporting failure. “The closest recoverable point is 14:15:32” answers the actual question.
Where the boundaries are approximate
BackupStartDate and BackupFinishDate describe when the backup operation ran, and the recovery point is governed by log records rather than by those timestamps. Treat the range check as a close approximation for planning rather than a guarantee down to the second.
Two details make that worth stating. A log backup covers records written up to the point the backup began reading, so activity during the backup may land in the following one. And STOPAT recovers to the last committed transaction at or before the time given, so the effective recovery point is a transaction boundary rather than the exact instant you asked for.[1]
For a target near a backup boundary, include the following log backup in the sequence as well and let STOPAT decide where to stop. Restoring one backup further than strictly needed costs time; restoring one fewer produces a database that stops short.
The differential can overshoot
Part 6 covered picking a differential that finished at or before the target. It’s worth repeating here because it interacts with STOPAT in a way that isn’t obvious.
STOPAT lands the recovery point within a log restore. A full or differential restores in its entirety, so a differential that finished after your target puts the database past the point you want before any log restore begins. Log restores only move forward, so there’s no recovering from that within the same sequence.
The rule is to pick the newest differential finishing at or before the target, or to skip the differential and replay logs from the full.
STOPATMARK and STOPBEFOREMARK
Two related options exist for recovering to a marked transaction rather than a time. STOPATMARK recovers to and including the mark, STOPBEFOREMARK recovers to just before it.[1]
They require the mark to have been created deliberately, with BEGIN TRANSACTION ... WITH MARK, before the event you want to recover around.[2] That makes them useful for planned operations like a schema deployment, where marking the transaction beforehand gives you a precise recovery point that doesn’t depend on clock times at all.
msdb.dbo.logmarkhistory records marks the instance has seen, which is where to look before assuming one exists.[3]
For unplanned recovery, marks are rarely available and time-based STOPAT is what you have.
Reporting it
A validation routine that answers reachability should return the verdict, the best reachable time, and the position of the final log backup, since all three feed the generated script:
| Output | Use |
|---|---|
| Verdict | whether the target is reachable at all |
| Best reachable time | what to tell whoever asked |
| Final log position | which backup gets STOPAT and RECOVERY |
| Count of logs in the sequence | how long the restore will take, roughly |
Part 11 turns that into the actual restore script.
Have you had to tell someone their target time wasn’t reachable? I’d be interested in how the conversation went. Bluesky or LinkedIn.
References
- RESTORE Arguments (Transact-SQL) – Microsoft Learn. Documents
STOPAT,STOPATMARK, andSTOPBEFOREMARK, including that recovery stops at the last committed transaction at or before the specified time. ↩ - Use Marked Transactions – Microsoft Learn. Covers
BEGIN TRANSACTION ... WITH MARKand recovering to a mark, which requires the mark to have been created in advance. ↩ - logmarkhistory – Microsoft Learn. One row per marked transaction committed, which is where to check whether a usable mark exists. ↩