SQL Server Monitoring – Backup Status

Updated February 19, 2022.


To list status of backups.


This feature queries the MSDB tables dbo.backupmediafamily and dbo.backupset which returns status for the generated backups.


The report page Backup Status has two tables, the main table table showing all backups performed, and the second in the upper right area showing the databases where there has not been performed full, differential or in case of FULL recovery mode log backup within the age specified by the 2 slicers.


The model for the report uses the facts [Backup age] and [Backup status], and the dimensions [Database], [Date], [Junk dimensions] and [Time].

The fact view [Backup status] in the database contains many values from the MSDB tables, but as good practice dictates it, it is only the values used that are loaded into Power BI report.


There is no configuration for this feature.


The default schedule for the feature is every hour (3.600 seconds).


The default retention for the table dbo.fhsmBackupStatus is 90 days and then there is a retention with the filter “Type = ‘L'” at 7 days.