We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the current tail log. (Without actually restoring these backups; the purpose here is to test the consistency of the backups.)
I already know how to do this for the existing backups: using RESTORE HEADERONLY I get the FirstLSN and LastLSN of every file, which can be compared for consecutive files, in order to determine whether they are compatible.
However, I don't know how to check whether the tail log follows the last log backup.
If I had the FirstLSN of the tail log, I could compare it to the LastLSN of the last log backup. But how can I obtain the FirstLSN of the tail log?
I need a solution that works from SQL Server 2005 upwards (ideally using t-sql). So far, I have searched Google to no avail. Btw. I first posted this on stackoverflow; but migrated it here since it was flagged off-topic there.
I tried the two provided solutions on a small example (SQL Server 2005, 9.0.5057):
BACKUP DATABASE TestDb TO DISK = 'C:\temp\backup test\Full.bak'
-- fire some update queries
BACKUP LOG TestDb TO DISK = 'C:\temp\backup test\Log1.bak'
-- fire both queries from the provided answers:
-- Martin Smith's answer yields: 838886656088920652852608
-- Shawn Melton's answer yields: 46000000267600001
RESTORE HEADERONLY FROM DISK = 'C:\temp\backup test\Log1.bak'
-- yields: 46000000267600001
So it appears the first one is off by several orders of magnitude.
I then did the same test on SQL 2008 SP1 (10.00.2531), where both queries yielded the correct answer.