Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

5 Answers

up vote 2 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
add comment

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
add comment

RESTORE HEADERSONLY 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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.