恢復完整備份 SQLSERVER 2019 後如何恢復多個事務日誌備份

我正在創建我的 DR 策略，我的完整備份恢復正常。

完整備份是使用 Ola Hallengren 腳本的修改版本進行的。

完整備份的文件時間戳顯示 07/26/2022 2:17 AM。

我每 5 分鐘記錄一次事務日誌，所以我想恢復它們的鏈。這些事務日誌備份是通過 SQL Server 中的代理作業創建的。我有前一天和本週的交易日誌。

理想情況下，我希望能夠編寫腳本來進行恢復。

使用維護作業獲取事務日誌。我的問題是如何控制事務日誌文件的名稱，以及如何創建腳本來進行恢復？

另外，我如何知道哪個事務日誌與 LSN 方面的最後一次完整備份相關並且不破壞日誌鏈？

有幾種方法可以恢復數據庫備份；該msdb數據庫包含您需要的所有詳細資訊。

話雖如此，由於您使用的是 Ola Hallengren 的維護解決方案，您可能會使用其他人圍繞該系統使用的文件夾結構建構的幾個腳本之一。

例如，sqldotnet有一個自動恢復過程。

從本質上講，該頁面顯示瞭如何使用xp_cmdshell獲取備份文件夾中所有備份的列表、對它們進行排序、找到最新的完整備份、還原它，然後還原所有日誌備份。實際的還原腳本本身取自MSSQLTips。MSSQLTips 腳本是：

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'Customer' 
SET @backupPath = 'D:\SQLBackups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
  AND backupFile LIKE @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + QUOTENAME(@dbName) + ' FROM DISK = '''  
      + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

-- 4 - Find latest diff backup 
SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.DIF'  
  AND backupFile LIKE @dbName + '%' 
  AND backupFile > @lastFullBackup 

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
  SET @cmd = 'RESTORE DATABASE ' + QUOTENAME(@dbName) + ' FROM DISK = '''  
      + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 
  PRINT @cmd 
  SET @lastFullBackup = @lastDiffBackup 
END 

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR  
  SELECT backupFile  
  FROM @fileList 
  WHERE backupFile LIKE '%.TRN'  
  AND backupFile LIKE @dbName + '%' 
  AND backupFile > @lastFullBackup 

OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @cmd = 'RESTORE LOG ' + QUOTENAME(@dbName) + ' FROM DISK = '''  
      + @backupPath + @backupFile + ''' WITH NORECOVERY' 
  PRINT @cmd 
  FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  

-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
PRINT @cmd 

msdb如果您想走這條路，您需要參考的表格包括：

• dbo.backup 文件
• dbo.backup 文件組
• dbo.backupmediafamily
• dbo.backupmediaset
• dbo.backupset

最後一個dbo.backupset包含以下列，可用於確保您以正確的 LSN 順序恢復文件。

first_lsn包含備份集覆蓋的最早 LSN。 last_lsn自然地，包含備份集覆蓋的最新 LSN。並且，嘿-presto，database_backup_lsn包含最新完整備份的 LSN。

作為對@HannahVernon 提供的腳本的改進，可以在沒有動態 SQL 的情況下執行此操作。

SET NOCOUNT ON;

-- 1 - Initialize user variables 
DECLARE @dbName sysname = 'Customer';
DECLARE @backupPath NVARCHAR(500) = 'D:\SQLBackups\';
DECLARE @onlyListFiles bit = 0;

-- 2 - Other variable declaration 
DECLARE @lastBackupDate datetime;
DECLARE @lastFullBackup NVARCHAR(500);
DECLARE @lastDiffBackup NVARCHAR(500);
DECLARE @logBackups TABLE (backupFile NVARCHAR(255), creation_time datetime);
DECLARE @crsr CURSOR;
DECLARE @backupFile NVARCHAR(500);

-- 3 - get latest full backup
SELECT TOP (1)
   @lastFullBackup = f.full_filesystem_path,
   @lastBackupDate = f.creation_time
FROM sys.dm_os_enumerate_filesystem(@backupPath, @dbName + '*.bak') f
ORDER BY
   f.creation_time DESC;

IF @lastFullBackup IS NULL
   THROW 50001, 'Cannot find last full backup', 1;

-- 4 - get latest diff after the full backup
SELECT TOP (1)
   @lastDiffBackup = f.full_filesystem_path,
   @lastBackupDate = f.creation_time
FROM sys.dm_os_enumerate_filesystem(@backupPath, @dbName + '*.dif') f
WHERE f.creation_time > @lastBackupDate
ORDER BY
   f.creation_time DESC;

-- 5 - get list of log backups
INSERT INTO @logBackups(backupFile, creation_time) 
SELECT
   f.full_filesystem_path,
   f.creation_time
FROM sys.dm_os_enumerate_filesystem(@backupPath, @dbName + '*.trn') f
WHERE f.creation_time > @lastBackupDate;

IF @onlyListFiles = 1
BEGIN
   SELECT backupFile
   FROM @logBackups
   UNION ALL
   SELECT @lastDiffBackup
   WHERE @lastDiffBackup IS NOT NULL
   UNION ALL
   SELECT @lastFullBackup;

   RETURN;
END;

-- 6 - restore full
RESTORE DATABASE @dbName
FROM DISK = @lastFullBackup
WITH NORECOVERY, REPLACE;

-- 7 - check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
  RESTORE DATABASE @dbName
  FROM DISK = @lastDiffBackup
  WITH NORECOVERY;
END;

-- 8 - check for log backups 
SET @crsr = CURSOR FAST_FORWARD FOR  
   SELECT f.backupFile  
   FROM @fileList f
   ORDER BY
     f.creation_time;

OPEN @crsr;

-- Loop through all the files for the database  
FETCH NEXT FROM @crsr INTO @backupFile;  

WHILE @@FETCH_STATUS = 0  
BEGIN
   RESTORE LOG @dbName
   FROM DISK = @backupFile
   WITH NORECOVERY;

   FETCH NEXT FROM @crsr INTO @backupFile; 
END;

CLOSE @crsr;

-- 9 - put database in a useable state 
RESTORE DATABASE @dbName WITH RECOVERY;

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