主页 / dba / 问题 / 234925
Accepted
Nishant
Asked: 2019-04-17 06:05:01 +0800 CST

有没有办法从备份文件中检索逻辑文件名？

  • 772

我想编写一个自动化脚本，从其备份文件中恢复 SQL Server DB。但是，这样做在 SQL 中并不是一个简单的过程，因为主查询需要额外的输入，而这些输入实际上可以使用另一个查询获得。我可以在一个查询中执行此操作吗？

SO上有一个问题；但解决方案不是很灵活。RESTORE FILELISTONLY的定义变化非常频繁。即便如此，该解决方案似乎也非常冗长。

没有更简单的方法将查询结果存储到变量中并使用它们吗？这是任何编程语言的小菜一碟。

获取逻辑名称：

RESTORE FILELISTONLY
FROM DISK = 'D:SourceBackUpFile.bak'
GO

恢复数据库：

RESTORE DATABASE DBName
FROM DISK = 'D:SourceBackUpFile.bak'
WITH RECOVERY
MOVE 'SourceMDFLogicalName' TO 'D:TargetMDFFile.mdf',
MOVE 'SourceLDFLogicalName' TO 'D:TargetLDFFile.ldf'
sql-server t-sql

3 个回答

  1. 取自这个 SO question。这似乎适用于 2019 年之前的所有版本，包括 2019 年。

    请注意，自 SQL 2000 以来，该定义已更改了两次——我个人认为 19 年中的两次更改并不属于频繁更改。从这个意义上说，只要您使用像下面这样的通用脚本，我就不会担心使用 RESTORE FILELISTONLY。

    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
    • 3
  2. Best Answer

    我在SQLServerScience.com上写了一篇博文，展示了如何获取您所追求的详细信息，并且与自 2008 年以来的所有 SQL Server 版本兼容

    这是该博客文章的主要代码：

    /*
    This script will generate a "RESTORE DATABASE" command with the correct "MOVE" clause, etc.

    By: Max Vernon
*/

SET NOCOUNT ON;
DECLARE @FileListCmd            nvarchar(max);
DECLARE @RestoreCmd             nvarchar(max);
DECLARE @cmd                    nvarchar(max);
DECLARE @BackupFile             nvarchar(max);
DECLARE @DBName                 sysname;
DECLARE @DataPath               nvarchar(260);
DECLARE @LogPath                nvarchar(260);
DECLARE @Version                decimal(10,2);
DECLARE @MaxLogicalNameLength   int;
DECLARE @MoveFiles              nvarchar(max);

SET @BackupFile     = N'D:\SQLServer\MyDatabaseBackup.bak'; --source backup file
SET @DBName         = N'MyDB'; --target database name
SET @DataPath       = N'C:\Database\Data'; --target data path
SET @LogPath        = N'C:\Database\Log'; --target log path

/* ************************************

    modify nothing below this point.

   ************************************ */
IF RIGHT(@DataPath, 1) <> '\' SET @DataPath = @DataPath + N'\';
IF RIGHT(@LogPath, 1) <> '\' SET @LogPath = @LogPath + N'\';
SET @cmd = N'';
SET @Version = CONVERT(decimal(10,2), 
    CONVERT(varchar(10), SERVERPROPERTY('ProductMajorVersion')) 
    + '.' + 
    CONVERT(varchar(10), SERVERPROPERTY('ProductMinorVersion'))
    );
IF @Version IS NULL --use ProductVersion instead
BEGIN
    DECLARE @sv varchar(10);
    SET @sv = CONVERT(varchar(10), SERVERPROPERTY('ProductVersion'));
    SET @Version = CONVERT(decimal(10,2), LEFT(@sv, CHARINDEX(N'.', @sv) + 1));
END

IF OBJECT_ID(N'tempdb..#FileList', N'U') IS NOT NULL
BEGIN
    DROP TABLE #FileList;
END
CREATE TABLE #FileList 
(
      LogicalName               sysname             NOT NULL
    , PhysicalName              varchar(255)        NOT NULL
    , [Type]                    char(1)             NOT NULL
    , FileGroupName             sysname             NULL
    , Size                      numeric(20,0)       NOT NULL
    , MaxSize                   numeric(20,0)       NOT NULL
    , FileId                    bigint              NOT NULL
    , CreateLSN                 numeric(25,0)       NOT NULL
    , DropLSN                   numeric(25,0)       NULL
    , UniqueId                  uniqueidentifier    NOT NULL
    , ReadOnlyLSN               numeric(25,0)       NULL
    , ReadWriteLSN              numeric(25,0)       NULL
    , BackupSizeInBytes         bigint              NOT NULL
    , SourceBlockSize           int                 NOT NULL
    , FileGroupId               int                 NULL
    , LogGroupGUID              uniqueidentifier    NULL
    , DifferentialBaseLSN       numeric(25,0)       NULL
    , DifferentialBaseGUID      uniqueidentifier    NOT NULL
    , IsReadOnly                bit                 NOT NULL
    , IsPresent                 bit                 NOT NULL 
);

IF @Version >= 10.5 ALTER TABLE #FileList ADD TDEThumbprint varbinary(32) NULL;
IF @Version >= 12   ALTER TABLE #FileList ADD SnapshotURL nvarchar(360) NULL;

SET @FileListCmd = N'RESTORE FILELISTONLY FROM DISK = N''' + @BackupFile + N''';';

INSERT INTO #FileList
EXEC (@FileListCmd);
SET @MaxLogicalNameLength = COALESCE((SELECT MAX(LEN(fl.LogicalName)) FROM #FileList fl), 0);
SELECT @MoveFiles = (SELECT N', MOVE N''' + fl.LogicalName + N''' ' 
    + REPLICATE(N' ', @MaxLogicalNameLength - LEN(fl.LogicalName)) 
    + N'TO N''' + CASE WHEN fl.Type = 'L' THEN @LogPath ELSE @DataPath END 
    + @DBName + N'\' + CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'System' 
                            WHEN fl.FileGroupName IS NULL THEN N'Log' 
                            ELSE fl.FileGroupName END 
    + N'\' + fl.LogicalName + CASE WHEN fl.Type = 'L' THEN N'.log' 
                                ELSE 
                                    CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'.mdf'
                                     ELSE N'.ndf' 
                                     END 
                                END + N'''
    '
FROM #FileList fl
FOR XML PATH(''));

SET @MoveFiles = REPLACE(@MoveFiles, N'&#x0D;', N'');
SET @MoveFiles = REPLACE(@MoveFiles, char(10), char(13) + char(10));
SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2);

SET @RestoreCmd = N'RESTORE DATABASE ' + @DBName + N'
FROM DISK = N''' + @BackupFile + N''' 
WITH REPLACE 
    , RECOVERY
    , STATS = 5
    ' + @MoveFiles + N';
GO';

IF LEN(@RestoreCmd) > 4000 
BEGIN
    DECLARE @CurrentLen int;
    SET @CurrentLen = 1;
    WHILE @CurrentLen <= LEN(@RestoreCmd)
    BEGIN
        PRINT SUBSTRING(@RestoreCmd, @CurrentLen, 4000);
        SET @CurrentLen = @CurrentLen + 4000;
    END
    RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);
END
ELSE
BEGIN
    PRINT @RestoreCmd;
END

    生成的RESTORE DATABASE命令如下所示：

    RESTORE DATABASE MyDB
FROM DISK = N'D:\SQLServer\backups\MyDB.bak' 
WITH REPLACE 
    , RECOVERY
    , STATS = 5
    , MOVE N'PRIMARY' TO N'C:\Database\Data\MyDB\system\PRIMARY'
    , MOVE N'LOG'     TO N'C:\Database\Log\MyDB\Log\LOG';
GO

    此代码还在 Linux 版本的 SQL Server 2017 上进行了测试。

    您问：

    没有更简单的方法将查询结果存储到变量中并使用它们吗？这是任何编程语言的小菜一碟。

    这里的要求不是向变量添加值。我们需要将一组不同数据的内容提取到一个表中。它在概念上可能类似于从对象加载数组。但是，在 SQL Server 中，您可以像命令一样存储命令输出结果的唯一方法RESTORE HEADERONLY是首先将其插入表中，然后从所需表中获取特定值。

    • 3

  3. 为什么不使用免费提供的dbatools或sp_restoregene？

    他们都可以做你所追求的。

    • 0

相关问题