Backup & Recovery from First Principles, Part 5: Restore Sequences and the STOPAT Rewind
The first four parts of this series were about taking backups. This one is about the operation they all exist for. A restore sequence is a small program you write under pressure, and its grammar has exactly three verbs worth internalizing: NORECOVERY, RECOVERY, and STANDBY. Get those right and the rest is bookkeeping.
This is part 5 of the backup and recovery series (part 1, part 4). Demos ran on SQL Server 2019 (CU32) and behave identically on SQL Server 2025 (RTM), with one small 2025 note: passing a full-precision datetime2 string (seven decimal places) to STOPAT failed with Msg 3217 “Invalid value specified for STOPAT parameter”; truncating to milliseconds worked.
The Three Verbs
Every RESTORE statement ends the database in one of three states:[1]
NORECOVERY– “I have more to apply.” The database stays in the restoring state; uncommitted transactions are not rolled back, because a later log backup might commit them. Every restore in a sequence except the last uses this.RECOVERY– “I am done.” Redo finishes, undo rolls back whatever never committed, and the database comes online. This is a one-way door: once recovered, you cannot apply more log. It is also the default, which is why forgetting to typeNORECOVERYon step one is the classic way to restart a four-hour restore.STANDBY– “let me peek.” The database comes up read-only, with the undo work saved to a standby file so the restore can still continue afterward. Invaluable when you are hunting for the exact moment something bad happened: restore, peek, apply another log backup, peek again.
The Sequence
The restore order is always: the most recent full, then the most recent differential based on it (skipping every log backup in between), then every log backup after that differential, in order. Our demo timeline, with a “mistake” event we will rewind past:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\seq_full.bak' WITH INIT, COMPRESSION; INSERT [dbo].[timeline] ([note]) VALUES ('event 1 - after full'); BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\seq_diff.bak' WITH DIFFERENTIAL, INIT, COMPRESSION; INSERT [dbo].[timeline] ([note]) VALUES ('event 2 - after diff'); BACKUP LOG [bkrec_demo] TO DISK = N'C:\temp\bkrec\seq_log1.trn' WITH INIT, COMPRESSION; INSERT [dbo].[timeline] ([note]) VALUES ('event 3 - after log1'); /* good point in time captured here */ SELECT SYSDATETIME() AS [good_point_in_time]; INSERT [dbo].[timeline] ([note]) VALUES ('event 4 - the mistake we will rewind past'); BACKUP LOG [bkrec_demo] TO DISK = N'C:\temp\bkrec\seq_log2.trn' WITH INIT, COMPRESSION; |
|
1 2 3 |
good_point_in_time --------------------------- 2026-07-21 13:43:10.4620000 |
Note the mistake (event 4) happened before the last log backup was taken. That is normal: in real incidents you take a tail-log backup after discovering the problem (part 3), precisely so the bad moment is inside a log backup you can stop short of.
Restoring to the Moment Before the Mistake
Restore into a new database (side-by-side, as in part 4) or over the original; the sequence is identical. STOPAT goes on the log restores:[2]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
RESTORE DATABASE [bkrec_demo_pit] FROM DISK = N'C:\temp\bkrec\seq_full.bak' WITH MOVE N'bkrec_demo' TO N'C:\temp\bkrec\bkrec_pit.mdf' , MOVE N'bkrec_demo_log' TO N'C:\temp\bkrec\bkrec_pit.ldf' , NORECOVERY; RESTORE DATABASE [bkrec_demo_pit] FROM DISK = N'C:\temp\bkrec\seq_diff.bak' WITH NORECOVERY; RESTORE LOG [bkrec_demo_pit] FROM DISK = N'C:\temp\bkrec\seq_log1.trn' WITH NORECOVERY, STOPAT = '2026-07-21T13:43:10.462'; RESTORE LOG [bkrec_demo_pit] FROM DISK = N'C:\temp\bkrec\seq_log2.trn' WITH RECOVERY, STOPAT = '2026-07-21T13:43:10.462'; SELECT [timeline_id], [note] FROM [bkrec_demo_pit].[dbo].[timeline]; |
|
1 2 3 4 5 6 |
timeline_id note ----------- ------------------------ 1 event 1 - after full 2 event 2 - after diff 3 event 3 - after log1 (3 rows affected) |
Events 1 through 3 survived; event 4, logged two seconds after our stop time, never happened in the restored copy. Details that matter:
- STOPAT on every log restore, not just the last one. Log backups whose range ends before the stop time apply fully and ignore the clause; the one that spans the stop time stops there. Putting it on all of them means you do not need to know in advance which file contains the target moment.
- If the stop time falls after a log backup’s range, SQL Server restores it
NORECOVERYand warns you to apply the next one, rather than recovering early. Safe by default. - The differential is an accelerator, not a requirement. Full + all log backups reaches the same point; the differential just lets you skip replaying log. If the differential is damaged, fall back to the log-only path.
- Skipping the diff but needing its log backups anyway produces the Msg 4305 “too recent to apply” error from part 3: it means there is a gap between what you restored and the log you are applying.
Generating the Sequence Instead of Writing It
At 3 AM, against a database with one full, one differential, and 96 log backups, nobody should be typing 98 RESTORE statements by hand. msdb has everything needed to generate them: I published a script that builds the full restore command sequence from backup history. Generate, review, run. If you take nothing else from this post: practice this before you need it. A restore sequence you have never rehearsed is a hypothesis, not a plan.
The next two parts cover restores that are less than the whole database: piecemeal and filegroup restores, and single-page restores.
When did you last rehearse a point-in-time restore? Comments below, or find me on Bluesky or LinkedIn.
References
- RESTORE Statements (Transact-SQL) – Microsoft Learn. Full syntax including NORECOVERY, RECOVERY, STANDBY, and STOPAT. ↩
- Restore a SQL Server Database to a Point in Time (Full Recovery Model) – Microsoft Learn. Requirements and procedure for STOPAT restores. ↩