TRN file Backup using LSN

Question

I receive the TRN files on a daily basis from the client and I back it up on a regular basis, i have usually backed it up based on the timestamp, but they recommend using LSN. I download the files from azure and back it up in the SQL Db. Currently the first file for todays back up failed with error Msg 4305, Level 16, State 1, Line 3 The log in this backup set begins at LSN 41217000000286400001, which is too recent to apply to the database. An earlier log backup that includes LSN 41216000010994400001 can be restored. Msg 3013, Level 16, State 1, Line 3 RESTORE LOG is terminating abnormally.

My Query to auto restore all the log files from the file explorer: **

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 @backupFile NVARCHAR(500)
alter database [DB_NAME] 
set offline with rollback immediate
 alter database [DB_NAME] 
 set online
-- 2 - Initialize variables 
SET @dbName = 'DB_NAME' 
SET @backupPath = 'F:\ECW LOG FILES\05.22 SQL Backup full\Logbackup\db_20230522\'
-- backup path is the location in the file explorer where all the TRN files are located. 
-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd
select * from @fileList
DECLARE backupFiles CURSOR FOR 
SELECT backupFile 
FROM @fileList 
ORDER BY backupFile
OPEN backupFiles
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @backupFile LIKE '%.TRN' AND @backupFile LIKE '%ECW44Z1CLSDB07A$ASP44POD07A_AG01_db_LOG%' 
    BEGIN
        SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
        + @backupPath + @backupFile + ''' WITH STANDBY =N''F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBNAME.BAK'''
        EXEC (@cmd)
    END

**

How am I able to view the LSN no for all the files in the file explorer and find the right TRN file to restore first.

Answer 1

and I back it up on a regular basis

I guess it should be read - I restore it on a regular basis.

failed with error Msg 4305, Level 16, State 1,

You have a gap in log sequence. Some file is missed

How am I able to view the LSN no for all the files in the file explorer and find the right TRN file to restore first.

Use RESTORE HEADERONLY command

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver16

If you cannot find proper file, you should ask your sender for full or differential backup to restore with norecovery and continue your log shipping.

Answer 2

Your issue appears to be that you are restoring in the wrong order. For that you can just sort the log results.

But I would strongly recommend using DbaTools Powershell module, the Restore-DbaDatabase command works very well to do what you are doing. It will automatically read the header info of each file and restore the earliest one first.

$dbName = 'DB_NAME';
$backupPath = 'F:\ECW LOG FILES\05.22 SQL Backup full\Logbackup\db_20230522\';

Set-DbaDbState `
  -Database $dbName `
  -SingleUser;

$files =
  Get-ChildItem -Path $backupPath -Filter '*ECW44Z1CLSDB07A$ASP44POD07A_AG01_db_LOG*.trn' |
  Sort-Object LastWriteTime -Ascending;

$files;

Restore-DbaDatabase `
  -Database $dbName `
  -Path $files `
  -StandBy 'F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\';

Set-DbaDbState `
  -Database $dbName `
  -MultiUser;