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’)]

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.

Purpose: The purpose of this job is to monitor and ensure that all databases in the SIMPLE recovery model, excluding the system databases, are being backed up regularly. It checks for last backup dates from the msdb.dbo.backupset table and raises alerts based on configured thresholds.
Why: This job is important to ensure data availability and protection against data loss. Monitoring backups, especially for databases in the SIMPLE recovery model, is crucial since these databases do not support point-in-time recovery. Failure to regularly backup could lead to significant data loss in case of a failure.
Manual checking: You can check this manually in the data management studio by querying the backup history from the msdb.dbo.backupset table:

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

“Database backup (SIMPLE)” task examines databases in a SIMPLE recovery model.
It excludes system databases: master, model, and msdb.
Alerts are generated based on the age of the last backup relative to a configurable threshold.

The procedure first determines databases to be excluded from checks based on user-defined parameters.
Scheduled tasks check the last backup date for each database.
Check includes a provision to ignore databases recently created (newer than a specified threshold).
Databases without a recent backup (as per the threshold) are flagged, and details are compiled for reporting.

An alarm or warning is triggered if the last backup date is older than the specified threshold.
Separate statuses and detailed messages are prepared for databases with old backups and those missing backup records entirely.

The results include detailed listings of databases with their last backup date and duration since the last backup.
Specific presentations include:
- Databases with over 48 hours old backup
- Databases missing backup records
- Most recent backup details for each database in a SIMPLE recovery model

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

The job leverages auxiliary tables and procedures for managing exclusions and handling data.
Upgrades include validation and insertion routines for missing default values in parameters, ensuring backward compatibility and consistency.

Continuous monitoring of database backups is essential for operational security and compliance with data protection policies. Regular checks help identify potential issues before they escalate into significant problems, ensuring data integrity and accessibility.