Sql-server – Log Sequence Numbers in Full BackUp and Log Backup

restoresql serversql-server-2008sql-server-2008-r2transaction-log

My full back-up starts daily at 02:00 AM. It takes about 3 hours.
I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours).
I can verify the LSN sequences of T-log backups when I "restore headeronly".
However, there seem to be a gap between the last LSN of the full backup and the T-log backup taken immediately after backup had started (i.e 02:15)

Below is restore headeronly output from the full backup started at 02:00

FirstLSN: 76583000073086500011
LastLSN: 76595000084143900001

…and this is the restore headeronly output from the log backup started at 02.15

FirstLSN: 76570000086250400001
LastLSN: 76571000009693900001

Shouldn't the last LSN of the full back up and first LSN of log back up be the same?
I compared the LSNs of the log back up taken at 05:15 and 05:00 (when the full back up has finished) but there is no sequence again.
I also checked RESTORE FILELISTONLY of the full backup and CreateLSN, DropLSN of log file is 0.
What is it that I might be missing here?
Thank you.

Best Answer

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. (http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)

Related Solutions

SQL Server Restore Sequence from Multiple Backups

Typically, backups to file are appended with a timestamp (e.g. MyDatabase_FULL_201202060900.bak) so you have the option of using a script to generate the restore sequence given a directory of files. Google will give you dozens of examples, as will the SSC script library. I have this script in my library currently.

If the server you've taken the backups from is available, you can generate the restore script from the msdb tables dbo.backupset and dbo.backupmediafamily, example here. Note SSMS will generate the correct script for you and you can use Profiler to capture the queries it uses to do so.

Sql-server – SQL Server transaction log backups: test whether tail log follows last known log backup

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.

Related Question