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.