TSQL Error : Column name or number of supplied values does not match table definition.

Question

I am getting this error message when I am executing the below query:-enter code here

USE XYZ
go
-- to restore db from the backup file
SET NOCOUNT on;
DECLARE @level varchar(1000)
DECLARE @commandDBName varchar(1000)
DECLARE @Command_sa varchar(1000)
DECLARE @commandFile varchar(1000)
DECLARE @Default_Data_Path VARCHAR(512)   
DECLARE @Default_Log_Path VARCHAR(512) 
DECLARE @RowCnt int
DECLARE @MaxRows int;

DECLARE @Table TABLE (rownum int IDENTITY (1, 1) Primary key NOT NULL,LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar(5), [FileGroupName] varchar(128), [Size] varchar(128), 
            [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), 
            [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)
)

Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
DECLARE @Path varchar(1000)

Select @BKFolder = 'G:\SQL\MSSQLSERVER\Backup\XYZ\'

set @FileName = null
set @cmdText = null

Declare @FileList table  (
FileNames varchar(255),
DepthFlag int,
FileFlag int
)


--get all the files and folders in the backup folder and put them in temporary table
insert into @FileList exec xp_dirtree @BKFolder,0,1
--select * from #filelist

--get the latest backup file name
select top 1 @FileName = @BKFolder + FileNames from @FileList where Filenames like 'Server1_XYZ_FULL%.bak' order by FileNames desc
--select @filename

declare @backupfile as Varchar(255)

DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)

SELECT @RowCnt = 1

SELECT @level = [compatibility_level]
FROM sys.databases
WHERE name = 'master';


INSERT INTO @table
EXEC('
RESTORE FILELISTONLY 
   FROM DISK=''' +@filename+ '''
   ');


SELECT * from @Table


Msg 213, Level 16, State 7, Line 4
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 4
RESTORE FILELIST is terminating abnormally.

Answer 1

Try adding one more column to @Table variable: SnapshotURL nvarchar(360). Check and adjust the type and order of the columns according to https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql.