SQL Server "RESTORE FILELISTONLY" Resultset

Question

I'm trying to write an automated backup and restore T-SQL scripts. I've done BACKUP part but I'm struggling on RESTORE.

When I run following statement on SS Management Studio;

EXEC('RESTORE FILELISTONLY FROM DISK = ''C:\backup.bak''')

I get a result set in a grid and also I can use

INSERT INTO <temp_table> 
EXEC('RESTORE FILELISTONLY FROM DISK = ''C:\backup.bak''')

to populate a temp table. However I get syntax error, when I try to select from that resultset. e.g

SELECT * FROM 
EXEC('RESTORE FILELISTONLY FROM DISK = ''C:\backup.bak''')

The resultset metadata should be stored somewhere in SQL Server Dictionary. I found other band-aid formula to get my automated restore working, but if can get to the resultset, I would create more elegant solution. Also please note that resultset is different in 2008 than 2005.

Thanks in advance...

Answer 1

Dead-end: SELECT INTO is nice because you don't have to define the table columns but it doesn't support EXEC.

Solution: INSERT INTO supports EXEC, but requires the table to be defined. Using the SQL 2008 definition provided by MSDN I wrote the following script:

DECLARE @fileListTable TABLE (
 [LogicalName] NVARCHAR(128),
 [PhysicalName] NVARCHAR(260),
 [Type] CHAR(1),
 [FileGroupName] NVARCHAR(128),
 [Size] NUMERIC(20,0),
 [MaxSize] NUMERIC(20,0),
 [FileID] BIGINT,
 [CreateLSN] NUMERIC(25,0),
 [DropLSN] NUMERIC(25,0),
 [UniqueID] UNIQUEIDENTIFIER,
 [ReadOnlyLSN] NUMERIC(25,0),
 [ReadWriteLSN] NUMERIC(25,0),
 [BackupSizeInBytes] BIGINT,
 [SourceBlockSize] INT,
 [FileGroupID] INT,
 [LogGroupGUID] UNIQUEIDENTIFIER,
 [DifferentialBaseLSN] NUMERIC(25,0),
 [DifferentialBaseGUID] UNIQUEIDENTIFIER,
 [IsReadOnly] BIT,
 [IsPresent] BIT,
 [TDEThumbprint] VARBINARY(32), -- remove this column if using SQL 2005
 [SnapshotURL] NVARCHAR(360) -- remove this column if using less than SQL 2016 (13.x)
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = ''YourBackupFile.bak''')
SELECT * FROM @fileListTable

• Paul Chernoch: For SQL Server 2005, use the same table definition with one difference: drop the last column (TDEThumbprint varbinary(32)).
• JonBrave: After SQL Server 2012 going to need to add a new column, SnapshotURL nvarchar(360), e.g. as per msdn.microsoft.com/en-us/library/ms173778.aspx, but I am unsure whether that is for SQL Server 2014 or 2016 (I think it starts in 2016...)
• Ben Gripka: Per the msdn link: SnapshotURL nvarchar(360) applies to: SQL Server (SQL Server 2016 (13.x) (CU1) through current version.

Answer 2

This is a code working with all version between SQL 2005 and SQL 2017 :

CREATE TABLE #FileListHeaders ( 
 LogicalName nvarchar(128)
 ,PhysicalName nvarchar(260)
 ,[Type] char(1)
 ,FileGroupName nvarchar(128) NULL
 ,Size numeric(20,0)
 ,MaxSize numeric(20,0)
 ,FileID bigint
 ,CreateLSN numeric(25,0)
 ,DropLSN numeric(25,0) NULL
 ,UniqueID uniqueidentifier
 ,ReadOnlyLSN numeric(25,0) NULL
 ,ReadWriteLSN numeric(25,0) NULL
 ,BackupSizeInBytes bigint
 ,SourceBlockSize int
 ,FileGroupID int
 ,LogGroupGUID uniqueidentifier NULL
 ,DifferentialBaseLSN numeric(25,0) NULL
 ,DifferentialBaseGUID uniqueidentifier NULL
 ,IsReadOnly bit
 ,IsPresent bit
)
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(4)) as float) > 9 -- Greater than SQL 2005 
BEGIN
 ALTER TABLE #FileListHeaders ADD TDEThumbprint varbinary(32) NULL
END
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(2)) as float) > 12 -- Greater than 2014
BEGIN
 ALTER TABLE #FileListHeaders ADD SnapshotURL nvarchar(360) NULL
END
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')
SELECT * FROM #FileListHeaders
DROP TABLE #FileListHeaders

Answer 3

You can't SELECT from EXEC. You can only INSERT into a table (or table variable) the result set of an EXEC.

As for automating restore, the answer at Fully automated SQL Server Restore already gives you everything you need to build a solution. Whether automated restore of databases with unknown file list is something to be attempted, that is a different topic.

• mevdiven: Thanks for the reply. But I puzzled that EXEC comment finds fields from the RESTORE command from somewhere (dictionary, metadata etc.) Why select can not access the same resource to extract resultset fields.
• Remus Rusanu: EXEC gets the fields from the result set. There is no dictionary nor metadata involved.