Database backup


Job details

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

Parameters

Name Default value Description
ignore databases dbwatch% The list of databases to be ignored (separated by comma).
backup date threshold 48 A warning/alarm is triggered if the number of hours since the database was last backed up is greater than the parameter value.
return status when missing backup 2 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing backup of a database.
return status when old backup 2 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the last backup date is older than the “backup date threshold” parameter value.
exclude databases in SIMPLE recovery mode NO If set to YES the alert will exclude all databases configured with SIMPLE recovery mode.
exclude databases if new 24 If the database has been created within the last 24 hours (default value), no warning/alarm will be triggered.

Job Summary

Job Description and Detail

The task primarily focuses on databases from instances running SQL Server or Azure SQL Managed Instances, within the specified version range. It checks the last backup time against predetermined thresholds and provides alerts based on the configuration. The `dbw_backup_proc` procedure is used for this automation.

Exclusions in Monitoring

Technical Implementation

The procedure (`dbw_backup_proc`) performs series of checks:

Dependency and Error Handling

Reporting and Visualization

A comprehensive report template is included:

The job is scheduled to run daily at a specified time (default: 06:10), but this can be adjusted as needed. Notifications, either via DATABASE_MAIL or other configured alert mechanisms, are triggered based on the result of these checks, particularly when databases fail to meet the backup policies set in place.