Check Log Sequence Numbers match for your Log Backups
-- Specify the database name
DECLARE @DatabaseName NVARCHAR(128) = 'INSERTDBNAMEHERE'; -- Replace with your database name
DECLARE @StartDate DATETIME = '2023-10-05 15:00:00.000';
-- Common Table Expression (CTE) to retrieve log backup information
WITH LogBackupCTE AS (
SELECT
@@SERVERNAME AS ServerName,
T1.name AS DatabaseName,
T3.backup_start_date AS Bkp_StartDate,
T3.backup_finish_date AS Bkp_FinishDate,
first_lsn,
last_lsn,
DATEDIFF(SECOND, T3.backup_start_date, T3.backup_finish_date) AS Bkp_Time_Sec,
T3.type AS Bkp_Type,
(T3.backup_size / 1048576.0) AS BackupSizeMB,
(T3.compressed_backup_size / 1048576.0) AS CompressedBackupSizeMB,
(CAST((T3.backup_size / 1048576.0) / (DATEDIFF(SECOND, T3.backup_start_date, T3.backup_finish_date) + 1) AS DECIMAL(10, 2))) AS MBPS,
user_name AS UserName,
physical_device_name AS BackupLocation,
ROW_NUMBER() OVER (ORDER BY backup_start_date) AS RowNumber
FROM master..sysdatabases AS T1
LEFT JOIN msdb..backupset AS T3 ON (T3.database_name = T1.name)
LEFT JOIN msdb..backupmediaset AS T5 ON (T3.media_set_id = T5.media_set_id)
LEFT JOIN msdb..backupmediafamily AS T6 ON (T6.media_set_id = T5.media_set_id)
WHERE T1.name = @DatabaseName
AND T3.backup_finish_date > @StartDate
AND DATABASEPROPERTYEX(T1.name, 'STATUS') = 'ONLINE'
AND t3.type = 'L'
AND T1.name <> 'tempdb'
)
-- Query to check if the last LSN of one backup matches the first LSN of the next backup...
SELECT DISTINCT
l1.ServerName,
l1.DatabaseName AS DBName,
l1.Bkp_StartDate,
l1.Bkp_FinishDate,
l1.First_LSN,
l1.Last_LSN,
CASE
WHEN L1.last_lsn = L2.first_lsn THEN 'Match'
WHEN (L1.last_lsn IS NULL or L2.first_lsn IS NULL) Then '<LATEST BACKUP>'
ELSE 'Mismatch'
END AS 'LSNStatus ',
l1.Bkp_Time_Sec,
l1.Bkp_Type,
l1.BackupSizeMB,
l1.CompressedBackupSizeMB,
l1.MBPS,
l1.UserName,
l1.BackupLocation
FROM LogBackupCTE L1
LEFT JOIN LogBackupCTE L2 ON L1.RowNumber + 1 = L2.RowNumber
Order by 3 asc
|
|
|
|
|
|