This article guides,
How to resolve restore error in SQL Server 2022? or Column name or number of supplied values does not match table definition.
Why below error occurred?
Sometime we get request to restore database from SQL Server 2012 or 2014 to SQL Server 2022 or
we have database refresh job on old server like SQL 2012,014 and we upgraded SQL server to 2022. Moved all the SQL jobs as well. But when we start using new server this restore jobs get failed due to below error.
Msg 213, Level 16, State 7, Line 12
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 12
RESTORE FILELIST is terminating abnormally.This job get backup details and insert into temp table. But when SQL Version change number of columns in below command also changed which error out the SQL Job.
Restore filelistonly from disk ='path'
When we need to to restore database backup periodically we need to create storage procedure and get those logical and physical file name periodically. for that we need to get restore filelistonly output in table.
when we try to insert rows from restore filelistonly we need to review the column details as per SQL version. Like if we are using it for SQL Server 2019 there are 21 columns which need to be inserted. If you run insert script with less column you will get below error
Msg 213, Level 16, State 7, Line 12
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 12
RESTORE FILELIST is terminating abnormally.How to resolve this error?
Step 1 :we need to first run Restore filelistonly on SQL version you are using and count number of columns it have in that version.
Restore filelistonly from disk ='path'
Step2 : Then you need to update your script as per number of column. You need to add or remove columns from create table script to get your script work.