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