I'm trying to wr开发者_开发知识库ite 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
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
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.
精彩评论