Hi All,

I'm trying to create a restore job which gets the latest transactional log file and it will run daily. I wanted to know if there is any way of writing a script so it captures the lsn number of the transactional log file please?

Thank you in advance!

database_id = db_id('SecondaryDatabaseName') and type = 0

This will give you the LSN of last restored log based on which you could identify the next one. You could also use powershell for this. Please check the link.

http://www.sqlservercentral.com/Forums/Topic1167312-391-1.aspx

You can query the lsn Information from msdb.dbo.backupset:

select top 10 first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn, *
from msdb.dbo.backupset
where [type] = 'L'
order by backup_set_id desc

Thank you for your reply!

I used the sql script but I'm having an issue with the following line:

LTRIM(SUBSTRING(Col1,1,20))AS'DateTimeStamp',

LTRIM(SUBSTRING(Col1,71,6))AS'LSN',

LTRIM(SUBSTRING(Col1,21,18))AS'FileSize',

LTRIM(SUBSTRING(Col1,40,1000))AS'FileName1'

#OriginalFileList

BYLSN