Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm restoring transaction logs to a database, but need a way to know which transaction log I should start with. Typically I'll see something like this:

The log in this backup set terminates at LSN 103000001633300001, which is too early to apply to the database. A more recent log backup that includes LSN 103000002524300001 can be restored.

So how do I get the LSN 103000002524300001 programattically? The database is in NORECOVERY mode so DBCC LOG doesn't work. I've got records of the transaction logs themselves so I know what their first and last LSN's are. I just don't know where the database itself currently sits.

share|improve this question
up vote 3 down vote accepted

Some lsn columns are in sys.master_files which at the server level

The column "redo_start_lsn" may be just the ticket.

I haven't tried this BTW...

share|improve this answer
redo_start_lsn does look good! I have no idea how this would look if my database had more than one file, but all of my databases have a single file so I won't worry about it. – Clyde Jun 2 '11 at 16:44
Just tested this with log shipping, SQL 10.50.6220.0, norecovery. last_lsn in msdb.dbo.backupset on the primary matches redo_start_lsn in msdb.sys.master_files on the secondary/secondaries. If you query both servers a few times over the course of [insert length of time between log backups] you can watch as the backups are taken on the primary and the redo_start_lsn moves to match on the secondary(ies) after the restore. – Jeremy Dec 16 '15 at 7:56

You can get this information with a query such as:

SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = 'AV_PROD'
ORDER BY restore_date DESC
share|improve this answer

Not sure about getting the first/last LSN from a transaction log file programmatically, but you can just script the recovery to try to apply each transaction log you have in order -- the ones that are "too early" (already exist in the DB) will fail with this error. The rest should apply cleanly.

Hopefully someone has a better idea though -- I can imagine that being quite painful to wait through if you have a lot of transaction logs as the whole log probably needs to be scanned to determine what the initial and final LSNs are.

share|improve this answer
right, and for me it's not so much the wait as it is the inability to tell if there was a real error. – Clyde Jun 2 '11 at 16:13

The fact that you have a db in norecovery means that you've already restored a backup of some sort. That backup has a header. Use that.

share|improve this answer

RESTORE HEADERONLY returns the FirstLSN and LastLSN of the backup as well as the DatabaseBackupLSN containing the LSN of the last full backup. From these you can recreate the necessary order of restore. You never need to look at the database LSN, the necessary order can (and should) always be recreated from the backup media itself.

share|improve this answer
I already know the first and last lsn of the backup file. It's the database itself I don't know the current state. – Clyde Jun 2 '11 at 16:16
Typo - should be HEADERONLY (not HEADERSONLY). I can't edit post. – Greg Sansom May 12 at 4:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.