How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019

Question

I am creating my DR strategy, and my full back-up recovers fine.

The full backups are taken with a modified version of the Ola Hallengren scripts.

The file timestamp of the full backup shows 07/26/2022 2:17 AM.

I have transaction logs taken every 5 minutes, so I would like to restore a chain of them. These transaction log backup's are created through an agent job in SQL Server. I have transaction logs from the previous day and well into the week.

Ideally I would like to be able to script this out to do the recovery.

The transaction logs are taken using a maintenance job. My question's are how can I control the names of the transaction log files, and how do I go about creating my script to do the restore?

Also how do I know which transaction log correlates to the last full back up in terms of LSN and not breaking the log chain?

Answer 1

There are several methods for restoring database backups; the msdb database contains all the details you need.

Having said that, since you're using Ola Hallengren's maintenance solution, you could likely use one of several scripts others have built around the folder structure used by that system.

For instance, sqldotnet has an automated restore process.

Essentially, that page shows how to use xp_cmdshell to obtain a list of all backups in the backup folder, sorts them, finds the most recent full backup, restores it, then restores all the log backups. The actual restore script itself is taken from MSSQLTips. The MSSQLTips script is:

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 

The msdb tables you'd need to reference if you want to go that route include:

• dbo.backupfile
• dbo.backupfilegroup
• dbo.backupmediafamily
• dbo.backupmediaset
• dbo.backupset

That last one, dbo.backupset contains the following columns which can be used to ensure you're restoring files in the correct LSN order.

first_lsn contains the earliest LSN covered by the backup set. last_lsn contains, naturally, the most recent LSN covered by the backup set. And, hey-presto, database_backup_lsn contains the LSN of the most recent full backup.

Answer 2

If PowerShell is an option for your script, dbatools' Restore-DbaDatabase cmdlet could be your friend.

Restoring a full backup chain up to the latest transaction log can be as simple as:

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

There is tons of parameters available to control the restore. It plays nicely with backups made with Ola Hallengren's BackupDatabase procedure.

Answer 3

As an improvement on the script provided by @HannahVernon, it is possible to do this without dynamic 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;

Answer 4

I just wanted to mention that there's an excellent script from Brent Ozar that allows restoring backups made with Ola's scripts (MIT licensed).

You could create the transaction logs with Ola's scripts so everything works out of the box (even though Brent Ozar's scripts can be parametrized).

The documentation page: sp_DatabaseRestore: Open Source Database Restore Stored Procedure

For your case (once you've create the stored procedure on the server) you could restore with something like this:

--Restore the newest FULL backup and all LOG backups and then recover the database so that it can be used:
EXEC dbo.sp_DatabaseRestore 
  @Database = 'LogShipMe', 
  @BackupPathFull = 'D:\Backup\SQL2016PROD1A\LogShipMe\FULL\', 
  @BackupPathLog = 'D:\Backup\SQL2016PROD1A\LogShipMe\LOG\', 
  @ContinueLogs = 0, 
  @RunRecovery = 1;