有沒有辦法從備份文件中檢索邏輯文件名？

我想編寫一個自動化腳本，從其備份文件中恢復 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'

我在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'
', 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是首先將其插入表中，然後從所需表中獲取特定值。

引用自：https://dba.stackexchange.com/questions/234925