Getting Restore FileListOnly data into a table

Restore FileListOnly command is useful for determining the logical name, physical name, etc of a backedup database (*.bak) file.

Running “Restore FileListOnly” outputs a table and I needed to get the data into a table so as to be able to use it in an actual restore method.

Here is how you do it:

--First create a temp table with all the fields that Restore FileListOnly returns:
--list of fields and their definition pulled for Sql Server 2008 from (http://msdn.microsoft.com/en-us/library/ms173778.aspx)

DECLARE @restoreFileListData 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) 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,
    IsReadOnly bit,
    IsPresent bit,
    TDEThumbPrint varbinary(32));

--Now execute the method and put the data into the temp table:

declare @cmd varchar(max)
set @cmd = 'RESTORE FILELISTONLY FROM  DISK = ''complete path to the bak file'''
insert into @restoreFileListData exec (@cmd);
select *  from @restoreFileListData t; -- data is now available in the temp table