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

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 http://stackoverflow.com/questions/2510295/fully-automated-sql-server-restore/2510790#2510790 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.

Answer 2

Select into is nice because you don't have to define the table columns. However, it doesn't support exec. Insert into supports exec, but requires the table to be defined. Below is the table definition for SQL 2008 (taken from http://msdn.microsoft.com/en-us/library/ms173778.aspx)

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,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32)
)
insert into @fileListTable exec('restore filelistonly from disk = ''YourBackupFile.bak''')
select * from @fileListTable