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

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?

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.

StackExchange Database Administrators Q#315280, answer score: 3