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...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With