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
begin
-- Already got these transactions
end
else
begin
-- Restore log
end