Determine LSN of database in NORECOVERY mode

Question

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.

score 4 · Accepted Answer · answered Jun 02 '11 at 16:27

4

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...

answered Jun 02 '11 at 16:27

gbn

6,009
1
17
21

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 02 '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 07:56

score 3 · Answer 2 · answered Feb 10 '13 at 13: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

score 0 · Answer 3 · answered Jun 02 '11 at 16:03

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.

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 02 '11 at 16:13

Remus Rusanu · Answer 4 · 2016-05-12T06:56:02.933

0

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.

edited May 12 '16 at 06:56

answered Jun 02 '11 at 16:14

Remus Rusanu

8,253
1
19
22

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 02 '11 at 16:16
Typo - should be HEADERONLY (not HEADERSONLY). I can't edit post. – Greg Sansom May 12 '16 at 04:12

score 0 · Answer 5 · answered Feb 10 '13 at 15:48

0

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.

answered Feb 10 '13 at 15:48

Ben Thul

2,969
16
23

Determine LSN of database in NORECOVERY mode

5 Answers5