SQL Server – Understanding Identical Log Sequence Numbers

sql serversql-server-2012transaction-log

Below is the log Sequence number of the log back ups and full back ups

Why are LSN numbers same from 11.30 to 11.45 pm…Was it due to the fact that there was no changes made ? When can i find out that the log sequence has been broken ?

11.30 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
11.45 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
12.00 am Full first LSN: 325:31409:239, last LSN: 325:31507:1
01.00 AM lOG first LSN:  325:31307:1,   last LSN: 325:42530:1

Best Answer

Log Sequence Numbers reflect any and all modifications made to a database. If no transactions have occurred between two backups, those backups will have the same LSNs.

Having said that, even the action of taking a backup will normally cause an increment to the LSN.

What version of SQL Server is this question about?

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

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.

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

SQL Server Restore Sequence from Multiple Backups

Related Question