How to troubleshoot LSN mismatch issue in log shipping
I have got an email, how to fix for log shipping, I already have a post for alwayson
https://sqlserverblogforum.com/alwayson/how-to-solve-the-lsn-mismatch-in-sql-server/
For log shipping: you will have entry like this in the job error
Message
2019-12-21 21:09:45.39 ***
Error: The file ‘\LAPTOP-ISGUKEUC\Backup_Copy\T_20191221153913.trn’ is too recent to apply to the secondary database ‘T’.(Microsoft.SqlServer.Management.LogShipping) ***
2019-12-21 21:09:45.39 *** Error:
The log in this backup set begins at LSN 31000000048600001, which is too recent to apply to the database.
An earlier log backup that includes LSN 31000000048200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Following is the code to find the break and fix:
Very easy fix: If we know the date when it got broken, just add that as condition and generate restore script and run it all in once, it will pick up the right file and follow it, get it in asc order.
Run this script on source server and copy the First_LSN of first row.
SELECT 'restore database T from disk= ''' +f.physical_device_name+''' with norecovery', b.backup_finish_date,b.first_lsn,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model, b.server_name ,b.database_name,b.user_name, f.physical_device_name,b.* FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) WHERE database_name like'T' and b.first_lsn<=31000000048200001 order by b.first_lsn desc
Copy paste the copied First_LSN in the condition and b.first_lsn>=31000000048200001
, get the result and restore backup on destination server.
SELECT 'restore database T from disk= ''' +f.physical_device_name+''' with norecovery', b.backup_finish_date,b.first_lsn,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model, b.server_name ,b.database_name,b.user_name, f.physical_device_name,b.* FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) WHERE database_name like'T' and b.first_lsn>=31000000048200001 ORDER BY b.backup_finish_date
If you still could not find or the backup been deleted, then reinitialize the log shipping.
