Proving the Restore, Part 3: The Error That Blames the Wrong Statement
A scheduled job that had run for months started failing after a server was upgraded. The message in the job history:
|
1 2 |
Msg 3013, Level 16, State 1, Line 1 RESTORE HEADERONLY is terminating abnormally. |
Read that as a DBA and you go looking at the backup file: corruption, truncation, a share that went away, a service account that lost permission.
The backup file was fine, and so were the share and the permissions. The problem was a temp table three lines further up, and part 2 explains why it appeared when it did.
Why the message points at the wrong thing
The statement was this:
|
1 2 |
INSERT INTO #header_results EXEC (N'RESTORE HEADERONLY FROM DISK = N''D:\Backups\Sales_FULL.bak'''); |
RESTORE HEADERONLY opened the file, read the headers, and produced 59 columns because the upgraded engine was SQL Server 2022. #header_results had been declared with 56, because it was written when the instance was 2019.
The INSERT couldn’t accept the result set. That aborts the batch inside EXEC, and the abort surfaces as the outermost statement failing. Msg 3013 is the general message for a restore that ended before completing, so it appears for causes unrelated to the media.[1]
Depending on version and context you may also see a companion message naming the real cause:
|
1 2 |
Msg 213, Level 16, State 7, Line 1 Column name or number of supplied values does not match table definition. |
When both appear, 213 is the one to act on. When only 3013 appears, there’s nothing in the message pointing at the temp table.
Establishing that the file is fine
Before rewriting anything, separate the two possibilities. Run the restore statement on its own, with no INSERT wrapped around it:
|
1 2 |
RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_FULL.bak'; |
If rows come back, the file is readable, the path resolves, and the service account has access. That rules out the media, and puts the fault in the capture.
Counting what you actually got
Once you know the file is fine, find out how many columns the engine is returning. Rather than counting across a wide grid by eye, let SQL Server count for you:
|
1 2 3 4 5 6 7 8 9 |
SELECT [column_count] = COUNT(*) FROM sys.dm_exec_describe_first_result_set ( N'RESTORE HEADERONLY FROM DISK = N''D:\Backups\Sales_FULL.bak''', NULL, 0 ); |
sys.dm_exec_describe_first_result_set returns one row per column of the first result set a batch would produce, without running it.[2] Drop the aggregate and you get names, types, and ordinal positions.
The DMV can’t describe every batch, and it returns an error rather than a row set when the metadata isn’t statically determinable. Try it on your version before building anything on it. If it doesn’t cooperate with RESTORE statements, the documented result set in Microsoft’s reference is the fallback, and counting rows in that table is the same exercise by hand.
Compare that count against your temp table:
|
1 2 3 4 5 6 |
SELECT [column_count] = COUNT(*) FROM [tempdb].[sys].[columns] WHERE [object_id] = OBJECT_ID(N'tempdb..#header_results'); |
The near miss that looks different
Getting the count right and a type wrong fails differently.
TimeZone is a good candidate for this. The name suggests an offset, so smallint looks reasonable. It’s documented as nvarchar(32) and holds a time zone name.[3] Declare it numerically and the failure looks something like:
|
1 2 3 |
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'Central Standard Time' to data type smallint. |
That message names the value, the source type, and the target type. A column count mismatch names none of them, which is why it takes longer to diagnose.
A third case raises nothing at all. INSERT ... EXEC matches by position, so if your table has the right number of columns but two adjacent ones are transposed, and each type will accept the other’s values, the insert succeeds and the data lands in the wrong columns. You find that out later, through a comparison that returns results you can’t explain. Generating the table definition from the documented column list, in order, rather than typing it from memory is what avoids this one.
Making the failure explain itself
When this runs unattended, the useful thing is for the job history to say what actually happened. Capture the error number alongside the message:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
BEGIN TRY INSERT INTO #header_results EXEC (@restore_command); END TRY BEGIN CATCH DECLARE @error_number int = ERROR_NUMBER() , @error_message nvarchar(4000) = ERROR_MESSAGE(); RAISERROR ( N'Header capture failed for %s. Msg %d: %s' , 16 , 1 , @backup_path , @error_number , @error_message ); END CATCH; |
Msg 213 in the job history points at the table definition. Msg 3013 on its own doesn’t.
Two practical notes on that pattern. ERROR_NUMBER() and ERROR_MESSAGE() are only populated inside the CATCH block, so capture them into variables before anything else runs.[4] And raising at severity 16 is what makes a SQL Server Agent job step report failure, which is the behaviour you want here.
Next
With the header reliably in a table, the next question is where the backup files are in the first place. Enumerating them from inside the engine has more options than it first appears, and the one that works best isn’t documented at all. That’s part 4.
Have you chased an error message that pointed confidently at the wrong component? I collect these. Bluesky or LinkedIn.
References
- MSSQLSERVER_3013 – Microsoft Learn. Describes Msg 3013 as the general indication that a backup or restore operation ended abnormally, which is why it appears for causes unrelated to the media. ↩
- sys.dm_exec_describe_first_result_set – Microsoft Learn. Returns metadata for the first result set of a batch without executing it, giving column names, types, and ordinals. ↩
- RESTORE HEADERONLY (Transact-SQL) – Microsoft Learn. Documents
TimeZoneas nvarchar(32) holding the time zone of the server the backup was taken from. ↩ - ERROR_NUMBER (Transact-SQL) – Microsoft Learn. Confirms the error functions return NULL outside a CATCH block, which is why the values are captured into variables first. ↩