SQL Server“仅恢复文件列表"结果集 [英] SQL Server "RESTORE FILELISTONLY" Resultset

问题描述

我正在尝试编写自动备份和恢复 T-SQL 脚本.我已经完成了 BACKUP 部分，但我在 RESTORE 上挣扎.

I'm trying to write an automated backup and restore T-SQL scripts. I've done BACKUP part but I'm struggling on RESTORE.

当我在 SS Management Studio 上运行以下语句时；

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''')

结果集元数据应该存储在 SQL Server 字典中的某个位置.我找到了其他创可贴公式来让我的自动恢复工作，但如果可以得到结果集，我会创建更优雅的解决方案.另请注意，2008 年的结果集与 2005 年不同.

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.

提前致谢...

推荐答案

Dead-end: SELECT INTO 很好，因为您不必定义表列但它不支持EXEC.

Dead-end: SELECT INTO is nice because you don't have to define the table columns but it doesn't support EXEC.

解决方案:INSERT INTO 支持EXEC，但需要定义表.使用 MSDN 提供的 SQL 2008 定义，我编写了以下脚本:

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
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = ''YourBackupFile.bak''')
SELECT * FROM @fileListTable

这篇关于SQL Server“仅恢复文件列表"结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！