When working with transaction log backup restore or log shipping in SQL Server, you may encounter the following error message:

Msg 4305, Level 16, State 1, Line 47

The log in this backup set begins at LSN 33000000048000001, which is too recent to apply to the database. An earlier log backup that includes LSN 33000000044800001 can be restored.

Msg 3013, Level 16, State 1, Line 47

RESTORE LOG is terminating abnormally.

This error occurs when you try to restore a transaction log backup that is not in the correct sequence. In order to understand this error better, let’s take a look at a sample scenario.

First, we create an empty database called SQLAuthority and perform a full backup. Then, we create three test tables and perform two transaction log backups. Finally, we perform another full backup.

Now, let’s try to restore the database using the backups we have taken. However, before restoring, we drop the existing database and then attempt to restore the full backup and the second transaction log backup.

At this point, we encounter the error message mentioned earlier. The error message indicates that we have skipped the first transaction log backup, which is required for the restore process.

In order to resolve this issue, we need to restore the missing transaction log backups in the correct sequence. To determine the sequence of backups, we can query the MSDB database on the source server using the following script:

DECLARE @db_name VARCHAR ( 100 )
SELECT @db_name = 'SQLAuthority'

SELECT TOP ( 30 ) s.database_name , m.physical_device_name , 
CAST ( CAST ( s.backup_size / 1000000 AS INT ) AS VARCHAR ( 14 )) + ' ' + 'MB' AS bkSize , 
CAST ( DATEDIFF ( second , s.backup_start_date , s.backup_finish_date ) AS VARCHAR ( 4 )) + ' ' + 'Seconds' TimeTaken , 
s.backup_start_date , CAST ( s.first_lsn AS VARCHAR ( 50 )) AS first_lsn , 
CAST ( s.last_lsn AS VARCHAR ( 50 )) AS last_lsn , 
CASE s.[type] 
WHEN 'D' THEN 'Full' 
WHEN 'I' THEN 'Differential' 
WHEN 'L' THEN 'Transaction Log' 
END AS BackupType , 
s.server_name , s.recovery_model 
FROM msdb.dbo.backupset s 
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
WHERE s.database_name = @db_name 
ORDER BY backup_start_date DESC , backup_finish_date

This script will provide us with the backup history for the specified database, including the sequence of backups and their respective LSNs (Log Sequence Numbers).

Alternatively, we can also find the same information in the SQL Server ERRORLOG. The ERRORLOG contains messages related to backup and restore operations, including the LSNs of the backups. By searching for the relevant messages, we can determine the correct sequence of backups.

Once we have identified the missing transaction log backups, we can restore them in the correct sequence to resolve the error.

By understanding the cause of this error and following the appropriate steps to restore the backups in the correct sequence, we can successfully restore a database using transaction log backups.

Do you have any other tricks or tips for resolving this issue? If so, please share them in the comments below to help others facing similar challenges.

Reference: Pinal Dave (https://blog.sqlauthority.com)