What is an LSN?
How can I determine which backup contains the correct LSN?
LSN, Log Sequence Number, is what uniquely identifies a log record in the transaction log, you can find more on this here: Introduction to Log Sequence Numbers
Every backup contains log records (yes, not only log backups, full and differential also, because the database restored from full or diff. backup should be consistent, you can find the details here: Understanding SQL Server Backups by Paul S. Randal)
So every backup has the inormation about itself (you can see it using RESTORE HEADERONLY) as well as msdb tables related to backups such as msdb..backupset
and you can find there the information reguarding first LSN, last LSN, i.e. the min LSN and max LSN of log records that this backup contains.
It's very usefull information because knowing it makes it possible to relate every backup to other backups. The log backup chain as well as the corresponding full or diff backup is not determined based on backup_finish_date because there can be Copy-Only Backups, but all is determined in base of LSNs.
Different LSNs are recorded for every backup. The simplest are log backups LSNs: every successive log backup has the first LSN equals to the last LSN of previous one.
For example, when you receive your error, this tells you that this backup has Fist LSN equals to 76120000013549400001, and in order to restore your backup chain you need to restore an earlier one (with Last LSN = 76120000013549400001)
The error tells you that you should find a log backup containing LSN = 75428000036693300001, this is because the last backup that you restored had the Last LSN = 75428000036693300001. All this information server get out of the last restored backup header, from its First LSN and Last LSN.
How can you find it?
The simplest way is using msdb..backupset
, but if you are given the backup files only and has no access to the original server, you can gather all the information from your backup files in one table (create the table, insert into it exec('restore headeronly from disk = ...'))
I give you a code to get this information from msdb:
select top 1 b1.[type],
b1.first_lsn,
b1.last_lsn,
b3.physical_device_name
from msdb..backupset as b1
join msdb..backupmediaset as b2
on b1.media_set_id = b2.media_set_id
join msdb..backupmediafamily as b3
on b2.media_set_id = b3.media_set_id
where database_name = 'your_db' and (last_lsn) < 76120000013549400001
order by last_lsn desc;
This code will give you previous backup, but if you want to get the previous of previous, use top 2, ecc.
There are other usefull LSNs that every backup records, for example, every differential backup "knows" what full backup contains its differential base, the field DatabaseBackupLSN of differential points to CheckpointLSN of corresponding full backup.
But this is another story. I've tried to write it short but this argument is wide, hope I answered your questions.