Proving the Restore, Part 11: Generating the Restore Script
Everything so far establishes that a set of backups is coherent: complete stripes, a matching differential base, an unbroken log chain, one lineage, and a reachable target time.
Writing the restore statements from that point is mechanical, which makes it a good candidate for generating rather than typing. A weekly full plus a differential plus a hundred and twenty log backups is a hundred and twenty-two statements, and the cost of a typo in any of them is finding out partway through.
The shape of the sequence
Three rules govern the order.
The full backup restores first, naming every stripe in one statement. Any differential restores second. Log backups restore in ascending LSN order after that.
Every statement except the last uses NORECOVERY, which leaves the database able to accept more. The last statement uses RECOVERY, plus STOPAT when recovering to a point in time. Getting RECOVERY onto an intermediate step ends the sequence early and means starting over.
CHECKSUM belongs on every statement. It verifies backup checksums during the restore where they exist, and it costs little.[1]
Where the file layout comes from
Restoring under a different name, or onto a server with different drive letters, needs a MOVE clause for every file in the database. RESTORE FILELISTONLY lists them.[2]
|
1 2 3 |
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales_FULL_S00.bak' , DISK = N'D:\Backups\Sales_FULL_S01.bak'; |
One row per file. The columns that drive generation are LogicalName, PhysicalName, and Type.
Type is char(1), and its values decide how each file is handled:
| Value | Meaning |
|---|---|
D |
SQL Server data file |
L |
SQL Server log file |
F |
Full Text Catalog |
S |
FileStream, FileTable, or In-Memory OLTP container |
D and L are ordinary files. F and S are containers, and they need different treatment, which is part 12.
Capturing FILELISTONLY uses the same INSERT ... EXEC pattern as the header, with the same requirement that the table match the result set. The good news after part 2 is that this result set has been more stable: SnapshotURL was added in SQL Server 2016 CU1, and there’s been no change at the 2022 boundary.[2]
Building the MOVE clauses
Each row becomes one MOVE, mapping the logical name to a new physical path:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @restore_as sysname = N'Sales_test' , @data_path nvarchar(260) = N'E:\SQLData\' , @log_path nvarchar(260) = N'F:\SQLLogs\'; SELECT [move_clause] = N' , MOVE N''' + [f].[LogicalName] + N''' TO N''' + CASE [f].[Type] WHEN N'L' THEN @log_path ELSE @data_path END + @restore_as + N'_' + [f].[LogicalName] + CASE [f].[Type] WHEN N'L' THEN N'.ldf' WHEN N'D' THEN N'.mdf' ELSE N'' /* containers take no extension */ END + N'''' FROM #filelist_results AS [f] ORDER BY [f].[FileID]; |
Prefixing the target filename with the restore name keeps a test restore from colliding with the original database’s files when both live on the same server. That collision is worth designing out rather than relying on the paths differing.
The ELSE N'' branch is deliberate, and part 12 is about why.
Assembling the statements
With the file list and the validated chain, the statements write themselves:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/* 1. Full, all stripes, with MOVE clauses */ SELECT N'RESTORE DATABASE ' + QUOTENAME(@restore_as) + NCHAR(13) + NCHAR(10) + N'FROM ' + @stripe_list + NCHAR(13) + NCHAR(10) + N'WITH NORECOVERY, CHECKSUM' + NCHAR(13) + NCHAR(10) + @move_clauses + N';'; /* 2. Differential, if one validated */ SELECT N'RESTORE DATABASE ' + QUOTENAME(@restore_as) + NCHAR(13) + NCHAR(10) + N'FROM DISK = N''' + @diff_file + N'''' + NCHAR(13) + NCHAR(10) + N'WITH FILE = ' + CAST(@diff_position AS nvarchar(10)) + N', NORECOVERY, CHECKSUM;'; /* 3. Each log backup in LSN order, RECOVERY on the last */ SELECT [statement] = N'RESTORE LOG ' + QUOTENAME(@restore_as) + NCHAR(13) + NCHAR(10) + N'FROM DISK = N''' + [h].[source_file] + N'''' + NCHAR(13) + NCHAR(10) + N'WITH FILE = ' + CAST([h].[Position] AS nvarchar(10)) + CASE WHEN [h].[Position] = @final_position THEN N', RECOVERY, CHECKSUM' + CASE WHEN @stopat IS NULL THEN N'' ELSE N', STOPAT = N''' + CONVERT(nvarchar(30), @stopat, 126) + N'''' END ELSE N', NORECOVERY, CHECKSUM' END + N';' FROM #header_results AS [h] WHERE [h].[BackupType] = 2 AND [h].[LastLSN] > @anchor_lsn ORDER BY [h].[FirstLSN]; |
Format 126 for the STOPAT literal gives ISO 8601, which avoids the regional interpretation problems that DATETIME string literals otherwise invite.
QUOTENAME on the database name matters more than it looks.[3] A restore-as name is often supplied by a caller, and it ends up concatenated into executable text.
Guardrails worth building in
Generating a script that could overwrite a live database deserves more care than generating a report.
Refuse to generate on a failed validation. If the chain check reported an error, producing a script anyway invites someone to run it.
Require an explicit overwrite flag. Default to refusing when the target database already exists, and make overwriting a deliberate parameter rather than a side effect.
Generate SET SINGLE_USER when overwriting. A restore over an existing database fails if connections are present, so the script should include the step that clears them, WITH ROLLBACK IMMEDIATE and all its implications.
Separate generating from executing. A procedure that prints the script by default and executes only when asked is easier to review, and the printed script is a record of what was intended.
A hundred-statement script is longer than PRINT will emit, which part 14 covers.
Next
Before the printing problem, there’s the file type that broke the generated MOVE clauses. FILESTREAM containers and full-text catalogs aren’t files, and treating them as files produces a restore that fails in a way the error message doesn’t explain. That’s part 12.
Do you generate restore scripts or keep runbooks? I’d like to hear which has held up better. Bluesky or LinkedIn.
References
- RESTORE Arguments (Transact-SQL) – Microsoft Learn. Documents
NORECOVERY,RECOVERY,CHECKSUM,FILE,MOVE, andSTOPAT, which together define the generated statements. ↩ - RESTORE FILELISTONLY (Transact-SQL) – Microsoft Learn. The full result set, including
LogicalName,PhysicalName, and theTypevalues D, L, F, and S.SnapshotURLcarries an “Applies to: SQL Server 2016 (13.x) CU1 through current version” note. ↩ - QUOTENAME (Transact-SQL) – Microsoft Learn. Delimits an identifier so that a supplied name can’t alter the surrounding statement. ↩