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...

score 47 · Accepted Answer · edited Jan 30 '22 at 18:03

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

For SQL Server 2005, use the same table definition with one difference: drop the last column (TDEThumbprint varbinary(32)). — Paul Chernoch, Sep 25 '12 at 18:46
After SQL Server 2012 going to need to add a new column, `SnapshotURL nvarchar(360)`, e.g. as per https://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...) — JonBrave, Aug 25 '16 at 14:23
Per the msdn link: SnapshotURL nvarchar(360) applies to: SQL Server (SQL Server 2016 (13.x) (CU1) through current version. — Ben Gripka, Jan 30 '22 at 17:59

score 11 · Answer 2 · answered Dec 24 '18 at 10:10

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

score 9 · Answer 3 · edited May 23 '17 at 11:45

9

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.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 24 '10 at 23:53

Remus Rusanu

288,378
40
442
569

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. – mevdiven Mar 25 '10 at 12:58
1

EXEC gets the fields from the result set. There is no dictionary nor metadata involved. – Remus Rusanu Mar 25 '10 at 23:18

SQL Server "RESTORE FILELISTONLY" Resultset

3 Answers3

Linked