Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

EDIT

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.

share|improve this question

migrated from serverfault.com Aug 25 '13 at 22:47

This question came from our site for professional system and network administrators.

    
I've been doing some research because it's an interesting question, but I'm not getting very far. I'm not sure SQL supports this out of the box. –  Katherine Villyard Aug 23 '13 at 18:42
1  
I am certain that there is a way to verify this, maybe using the LSN is not the way to do it. I'll put a bounty on the question in a few hours, this question needs more views.. –  Andreas Aug 24 '13 at 7:52
add comment

2 Answers

up vote 11 down vote accepted
+50

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:


SELECT 
   last_log_backup_lsn
FROM 
   sys.database_recovery_status
WHERE 
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.

share|improve this answer
    
The result of this query appears to be correct. –  Andreas Aug 27 '13 at 6:43
    
Certainly looks correct to me. The last_log_backup_lsn is equal to the first_lsn of the tail of the log. So, if you have a log file to be restored with a last_lsn equal to the last_log_backup_lsn from Shawn's code, then you know that you have a backup right up to the tail log. (Of course that is only guaranteed true at the moment of the query, the next moment a new log backup could be started.) –  RLF Aug 27 '13 at 15:15
    
@RLF added an additional note because this would also be true if the database is not accessible. It is not really a viable solution to use when needing to implement your disaster recovery plan. Purely for tabletop exercises or testing your plan. –  Shawn Melton Aug 27 '13 at 17:27
add comment

Something like the following should do it.

WITH LSN_CTE
AS
(
SELECT TOP 1
       LEFT( LogRecords.[Current LSN], 8 )          AS Part1,
       SUBSTRING( LogRecords.[Current LSN], 10, 8 ) AS Part2,
       RIGHT( LogRecords.[Current LSN], 4 )         AS Part3
FROM   sys.fn_dblog(NULL,NULL) AS LogRecords
ORDER BY [Current LSN]
)
SELECT CAST( CAST( CONVERT( varbinary, Part1, 2 ) AS int ) AS varchar ) +
       RIGHT( '0000000000' + CAST( CAST( CONVERT( varbinary, Part2, 2 ) AS int ) AS varchar ), 10 ) +
       RIGHT( '00000'      + CAST( CAST( CONVERT( varbinary, Part3, 2 ) AS int ) AS varchar ), 5 ) AS [Converted LSN]
FROM   LSN_CTE

Using the conversion code to decimal from this article.

The ORDER BY [Current LSN] may well be completely unnecessary overhead. I'm not sure. The result of this function always seems to be in LSN order and I guess it just reads the log sequentially but just in case...

share|improve this answer
    
@MartinSmith: fn_dblog does not seem to be very well documented. I assume its results always hold for the current database (since there is no WHERE DbName = 'XXX' in the snippet)? –  Andreas Aug 26 '13 at 8:08
    
@Andreas - Yes it is undocumented. And yes it returns the information from the log of the current database. –  Martin Smith Aug 26 '13 at 11:48
    
see my edit to the original question. The number returned by your snippet is much larger than the LSNs of recent backups of the same DB. –  Andreas Aug 27 '13 at 6:42
    
@Andreas - Strange. I only tried it on a single test DB and it worked correctly. Not sure where the error is. What version of SQL Server are you running this on? The CONVERT with style parameter 2 might be the problem. –  Martin Smith Aug 27 '13 at 7:15
    
(Though Shawn's answer seems massively preferable to this one anyway) –  Martin Smith Aug 27 '13 at 7:39
show 2 more comments

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.