This blog is part of the blog series named SQL Server Monitoring.
Purpose
To list status of backups.
Report
The report has 2 tables, where the upper table are showing the databases for which there has not been performed full, differential or in case of Full recovery mode log backup within the age specified by the 2 slicers, and the lower table table are showing all backups performed with toggle bottoms to see only Copy only and-or Damaged backups.
Sources
This feature queries the MSDB tables dbo.backupmediafamily and dbo.backupset which returns status for the generated backups.
Model
The model for the report uses the facts [Backup age] and [Backup status], and the dimensions [Date], [Database] and [Junk dimensions].
Service setup
The feature uses the service ‘fhsmSPBackupStatus’ which can be configured using the stored procedure fhsmSPControl.
Parameter
There is no parameter to be configured for this feature.
Schedule
The default schedule for the feature is every hour (3.600 seconds).
EXEC fhsmSPControl
@Type = 'Schedule',
@Command = 'list',
@Task = 'fhsmSPBackupStatus',
@Name = 'Backup status',
@Wildcard = 0;
EXEC fhsmSPControl
@Type = 'Schedule',
@Command = 'set',
@Task = 'fhsmSPBackupStatus',
@Name = 'Backup status',
@ScheduleType = 0,
@Enabled = 1,
@ExecutionDelaySec = 3600,
@FromTime = '00:00:00',
@ToTime = '23:59:59',
@Monday = 1,
@Tuesday = 1,
@Wednesday = 1,
@Thursday = 1,
@Friday = 1,
@Saturday = 1,
@Sunday = 1;Retention
The default retention for the table dbo.fhsmBackupStatus is 90 days.
Further more there is a retention with the filter “Type = ‘L'” at 7 days to delete records for log backups.
EXEC fhsmSPControl
@Type = 'Retention',
@Command = 'list',
@TableName = 'fhsmBackupStatus',
@Wildcard = 0;
EXEC fhsmSPControl
@Type = 'Retention',
@Command = 'set',
@TableName = 'fhsmBackupStatus',
@Enabled = 1,
@Sequence = 1,
@Days = 90;
EXEC fhsmSPControl
@Type = 'Retention',
@Command = 'set',
@TableName = 'fhsmBackupStatus',
@Enabled = 1,
@Sequence = 2,
@Days = 7,
@Filter = 'Type = ''L''';