Is this log backup file the next one to restore?

When you restore from a transaction log backup, SQL Server checks the file against the database and gives you an error if the file is not the next one in the sequence. I wanted to know how this works so I did some digging. Not much in Books Online that I could see so I did some practical investigation with some of my log shipping files.

It looks like it compares the last LSN in the backup file’s header with the last LSN in the database’s restore history. If the last LSN in the file is less than the last LSN in the restore history, it indicates that the database already contains those transactions. Here’s some code that does it:

declare    @LastDBLSN    numeric(25,0)
        , @LastFileLSN    numeric(25,0)
--Get last LSN from database
select    top 1 @LastDBLSN = bs.last_lsn
from    msdb.dbo.restorehistory rh with (nolock)
        left join msdb.dbo.backupset bs with (nolock) on bs.backup_set_id = rh.backup_set_id
where    rh.destination_database_name = @DBName
order by rh.restore_history_id desc
--Get last LSN from backup file
declare @logbak table
    BackupName nvarchar(128)
    , BackupDescription nvarchar(255)
    , BackupType smallint
    , ExpirationDate datetime
    , Compressed tinyint
    , Position smallint
    , DeviceType tinyint
    , UserName nvarchar(128)
    , ServerName nvarchar(128)
    , DatabaseName nvarchar(128)
    , DatabaseVersion int
    , DatabaseCreationDate datetime
    , BackupSize numeric(20, 0)
    , FirstLSN numeric(25, 0)
    , LastLSN numeric(25,0)
    , CheckpointLSN numeric(25,0)
    , DatabaseBackupLSN numeric(25, 0)
    , BackupStartDate datetime
    , BackupFinishDate datetime
    , SortOrder smallint
    , CodePage smallint
    , UnicodeLocaleId int
    , UnicodeComparisonStyle int
    , CompatibilityLevel tinyint
    , SoftwareVendorId int
    , SoftwareVersionMajor int
    , SoftwareVersionMinor int
    , SoftwareVersionBuild int
    , MachineName nvarchar(128)
    , Flags int
    , BindingID uniqueidentifier
    , RecoveryForkID uniqueidentifier
    , Collation nvarchar(128)
    , FamilyGUID uniqueidentifier
    , HasBulkLoggedData bit
    , IsSnapshot bit
    , IsReadOnly bit
    , IsSingleUser bit
    , HasBackupChecksums bit
    , IsDamaged bit
    , BeginsLogChain bit
    , HasIncompleteMetaData bit
    , IsForceOffline bit
    , IsCopyOnly bit
    , FirstRecoveryForkID uniqueidentifier
    , ForkPointLSN numeric(25, 0) null
    , RecoveryModel nvarchar(60)
    , DifferentialBaseLSN numeric(25, 0) null
    , DifferentialBaseGUID uniqueidentifier
    , BackupTypeDescription nvarchar(60)
    , BackupSetGUID uniqueidentifier null
insert @logbak exec ('restore headeronly from disk = ''' + @FileName + '''');
select     @LastFileLSN = LastLSN
from    @logbak;
-- Compare them
if @LastFileLSN < @LastDBLSN
        -- Already got these transactions
        -- Restore log

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s