Get the last Transaction Log Backup Per Database
;WITH LastLogBackup AS (
SELECT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bmf.physical_device_name AS backup_file_path,
CAST(bs.backup_size / 1024.0 / 1024.0 AS DECIMAL(10, 2)) AS backup_size_mb,
ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_finish_date DESC) AS RowNum
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.type = 'L' -- Transaction Log
)
SELECT
d.name AS [DatabaseName],
d.recovery_model_desc AS [RecoveryModel],
Case llb.type
WHEN 'L' Then 'LOG'
END AS [Backup Type],
llb.backup_start_date AS [LastBackupStartDate],
llb.backup_finish_date AS [LastBackupFinishDate],
DATEDIFF(MINUTE, llb.backup_finish_date, GETDATE()) AS [MinutesSinceLastBackup],
llb.backup_size_mb AS [BackupSizeMB],
ISNULL(llb.backup_file_path, 'NO LOG BACKUP FOUND') AS [BackupFilePath]
FROM sys.databases d
LEFT JOIN LastLogBackup llb
ON d.name = llb.database_name AND llb.RowNum = 1
WHERE d.database_id > 4 -- Exclude system databases (master, tempdb, model, msdb)
AND d.source_database_id IS NULL -- Exclude database snapshots
ORDER BY d.name;
|
|
|
|
|
|