问还原完整备份SQLSERVER 2019后如何还原多个事务日志备份

EN

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 

Restore-DbaDatabase -SqlInstance sqlinstanceWhereToRestore -Path \\path\to\database\backups -DatabaseName  restoredDB

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;