Database backup (system databases)

Job details

Name:	Database backup (system databases)
Platform:	Sqlserver
Category:	Availability
Description:	This procedure analyzes the FULL backup statistics for the system databases (master, model and msdb) using data from the msdb.dbo.backupset table.
Long description:	This procedure analyzes the FULL backup statistics for the system databases (master, model and msdb) using data from the msdb.dbo.backupset table.
Version:	1.4
Default schedule:	25 6 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & alwayson_active=‘NO’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
time threshold	168	An alert is triggered (parameter value “return status”) if the number of hours (default 168 hours – 1 week) since the last FULL backed up is greater than this parameter value.
include databases	master, msdb	List of system databases to be checked for backup (separated by comma).
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing or old backup for system databases.

Job Summary

Purpose: The purpose of this job is to monitor the backup status of system databases (master, model, and msdb) in SQL Server instances and Azure SQL Managed Instances.
Why: This job is essential because system databases are critical for the recovery and proper functioning of SQL Server environments. Monitoring the backup status ensures that the system databases can be restored in the event of a failure, minimizing downtime and data loss.
Manual checking: You can check the backup status manually by executing the following SQL commands in the database:

SELECT database_name, MAX(backup_finish_date) AS last_backup_date FROM msdb.dbo.backupset WHERE database_name IN ('master', 'model', 'msdb') AND type = 'D' GROUP BY database_name;

Detailed Operation

This job operates by using a procedure named “dbw_sys_backup_proc” that analyzes backup data from the “msdb.dbo.backupset” table to check the last full backup time for selected system databases.
The procedure generates alert statuses based on the threshold defined for the time since the last backup (default: 168 hours, i.e., one week). Alert levels include ALARM for critical issues, WARNING for warnings, and OK for normal status.
Dependencies include a self-reference to ensure that the “dbw_sys_backup_proc” exists and is operational. The job confirms this before proceeding with the data analysis.

Monitoring Logic

The job’s logic involves iterating over specified system databases, checking the most recent backup times, and verifying these against the defined thresholds.
If a database’s last backup time exceeds the threshold, it records this in a dynamic summary alongside other databases meeting this criteria.
It similarly records databases lacking any backup history in the monitored MS SQL instance.

Output and Reporting

Database name	Last backup	Backup Age (hours)
master	2020-01-01 12:00:00	30
model	2020-01-01 12:00:00	30
msdb	2020-01-02 12:00:00	24

The reports generated include tabular presentations of databases with backups older than a defined window (e.g., 48 hours) and databases without any recorded backups.
This data helps database administrators quickly ascertain the health of system database backups and take corrective actions as necessary.

Upgrade Considerations

The job includes procedures to handle upgrades from previous versions by ensuring that necessary adjustments are made to accommodate changes in monitoring and reporting criteria.
Upgrades address the evolution of database properties and monitoring thresholds, ensuring that the monitoring job remains relevant under new conditions of operation and system requirements.

Implementation and Maintenance

The job is designed for automatic yet configurable operation, considering typical system environments and operational best practices.
Maintenance involves periodic review of backup policies, monitoring thresholds, and tuning of procedural parameters to align with organizational requirements and changes in the IT landscape.

This detailed analysis ensures that the dbWatch job effectively contributes to operational resilience by facilitating proactive management of system database backups.