Sql Server "restore Filelistonly" Resultset

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; EXE

Solution 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@fileListTableTABLE (
    [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
)
INSERTINTO@fileListTableEXEC('RESTORE FILELISTONLY FROM DISK = ''YourBackupFile.bak''')
SELECT*FROM@fileListTable

Solution 2:

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

CREATETABLE #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') aschar(4)) asfloat) >9-- Greater than SQL 2005 BEGINALTERTABLE #FileListHeaders ADD TDEThumbprint  varbinary(32) NULLEND
IF cast(cast(SERVERPROPERTY('ProductVersion') aschar(2)) asfloat) >12-- Greater than 2014BEGINALTERTABLE #FileListHeaders ADD SnapshotURL    nvarchar(360) NULLENDINSERTINTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')

SELECT*FROM #FileListHeaders

DROPTABLE #FileListHeaders

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