Database backup (SIMPLE)


Job details

Name: Database backup (SIMPLE)
Platform: Sqlserver
Category: Availability
Description: This procedure analyzes the backup statistics (type D) from the msdb.dbo.backupset table for databases in SIMPLE recovery model (excluding the system databases: master, model and msdb).
Long description:
Version: 2.1
Default schedule: 50 5 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & alwayson_active=‘NO’ & eng_inst_priv = 0 & (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 if new 24 If the database has been created within the last 24 hours (default value), no warning/alarm will be triggered.

Job Summary

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

Job Configuration Details

Task Execution Logic

Alert Triggers

Result Presentation

Example of Output Table:

Database Name Last Backup Backup Age (hours)
Database A 2023-01-01 12:00:00 50
Database B 2023-01-02 12:00:00 30

Dependencies and Additional Scripts

Importance of Regular Monitoring