Proving the Restore, Part 14: Getting the Script Out of the Procedure
The generator from part 11 builds a restore script into a variable. Getting it back out so someone can read it before running it seems like the easy part.
PRINT @script returns the beginning of it. How much of the beginning depends on the data type, and the part that doesn’t arrive isn’t reported as missing.
The limits
A message string passed to PRINT can be up to 8,000 characters if it’s non-Unicode and 4,000 if it’s Unicode. Longer strings are truncated. varchar(max) and nvarchar(max) are truncated to varchar(8000) and nvarchar(4000) respectively.[1]
Declaring the variable as nvarchar(max) doesn’t help. The truncation happens on output, and no error or warning accompanies it.
4,000 characters is not much for a restore script. A single RESTORE LOG statement with a full path, a FILE clause, and WITH NORECOVERY, CHECKSUM runs to roughly 150 characters. Around twenty-five of those fills the budget, and a week of fifteen-minute log backups produces several hundred.
The output stops mid-statement. A reader who doesn’t know about the limit sees a script that appears complete apart from an odd ending, and the most likely conclusion is that the generator has a bug.
Splitting the output
The fix is to emit the script a line at a time rather than as one string.
The line breaks are already there, since the generated script separates statements with carriage return and line feed. Splitting on them and printing each piece keeps every piece well under the limit.
STRING_SPLIT is the first thing to reach for, with a caveat. Until SQL Server 2022 it returned no ordering information, and its output order is not guaranteed.[2] A restore sequence depends on order, so that matters here. SQL Server 2022 added the enable_ordinal argument, which returns an ordinal column you can sort by, but code that has to run on 2019 or earlier can’t rely on it.
A cursor over the split positions is unglamorous and works on every version:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DECLARE @remaining nvarchar(max) = @script , @line nvarchar(max) , @break int; WHILE LEN(@remaining) > 0 BEGIN SET @break = CHARINDEX(NCHAR(10), @remaining); IF @break = 0 BEGIN SET @line = @remaining; SET @remaining = N''; END ELSE BEGIN SET @line = LEFT(@remaining, @break - 1); SET @remaining = SUBSTRING(@remaining, @break + 1, LEN(@remaining)); END; PRINT REPLACE(@line, NCHAR(13), N''); END; |
Splitting on line feed and stripping any carriage return handles both CRLF and bare LF without a separate branch.
One caution on LEN: it ignores trailing spaces, so a line consisting only of spaces measures zero and can end the loop early. DATALENGTH counts bytes instead and doesn’t have that behaviour, which is worth using if the generated text might contain such a line.
Returning a result set instead
Printing is convenient for a human at a query window and awkward for anything else. A result set is easier to consume, and it has no length limit per row beyond the data type.
|
1 2 3 4 5 6 7 |
SELECT [step] = ROW_NUMBER() OVER (ORDER BY [h].[FirstLSN]) , [statement] = [generated_statement] FROM #restore_steps AS [h] ORDER BY [step]; |
That copies cleanly out of a grid, feeds a calling application, and can be inserted into a logging table for a record of what was generated. If both forms are useful, a parameter that selects between printing and returning rows costs little.
For long output specifically, the SSMS results grid has its own limits on characters retrieved for XML and text, configured in query options rather than in T-SQL. Results to text has a maximum characters per column setting that also truncates. Neither is a server-side limit, but both catch people who have worked around PRINT and assumed the problem was solved.
RAISERROR as an alternative
RAISERROR with severity 0 and WITH NOWAIT returns a message immediately rather than at batch end, which is useful for progress output during a long operation.[3]
|
1 |
RAISERROR (N'Restoring step %d of %d...', 0, 1, @step, @total) WITH NOWAIT; |
PRINT output can be buffered until the batch completes, so a procedure that prints progress may deliver all of it at the end. For a restore that runs for twenty minutes, that difference matters.
RAISERROR has its own message length limit of 2,047 characters, so it’s a better fit for progress lines than for emitting a script.[3]
Where this leaves the series
Across fourteen posts the checks have gone from asking whether a backup job succeeded to proving that a specific set of files can restore a specific database to a specific moment.
Every one of them reads metadata: msdb tables, backup headers, and file lists. None requires restoring anything, and all of them can run on a schedule against production without touching it.
The check that would have caught the incident in part 1 is an age threshold on the newest full backup. The rest exist because that one only catches the failure you already thought of.
What does your restore validation cover today, and what did it miss that you added afterwards? Bluesky or LinkedIn.
References
- PRINT (Transact-SQL) – Microsoft Learn. A message string can be up to 8,000 characters non-Unicode and 4,000 Unicode; longer strings are truncated, and the max types are truncated to varchar(8000) and nvarchar(4000). ↩
- STRING_SPLIT (Transact-SQL) – Microsoft Learn. Documents the
enable_ordinalargument added in SQL Server 2022 and states that output order is not guaranteed without it. ↩ - RAISERROR (Transact-SQL) – Microsoft Learn. Covers
WITH NOWAITfor immediate delivery and the 2,047 character message limit. ↩