Database backup

Job details

Name:	Database backup
Platform:	Sqlserver
Category:	Availability
Description:	This alert checks if the instance has been restarted since the last check
Long description:	This job checks the backup statistics (type D and I) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb).
Version:	2
Default schedule:	10 6 * *
Requires engine install:	No
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘NO’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore_databases	2	The list of databases to be ignored (separated by comma).
warning_threshold	2	The maximum number of hours since the last full database backed.
alarm_threshold	2	The maximum number of hours since the last full database backed.

Job Summary

Purpose: The purpose of this job is to monitor the status of database backups, specifically checking the time since the last backup for each database, excluding system databases (master, model, msdb) and any databases explicitly ignored.
Why: This job is important to ensure data availability and disaster recovery readiness on SQL Server and Azure SQL Managed Instance platforms. If the time since the last backup exceeds specific thresholds (warning or alarm), action may be necessary to prevent data loss and maintain compliance with data protection policies.
Manual checking: You can check this manually in the database by issuing the following SQL command:

SELECT msdb.dbo.backupset.database_name, datediff(hour, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) as diff_hours, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupset WHERE msdb.dbo.backupset.type in ('D','I') and database_name NOT IN ('master', 'model', 'msdb') GROUP BY msdb.dbo.backupset.database_name ORDER BY diff_hours DESC;

Detailed Monitoring Logic

Checks each database for the last backup finish date and calculates the difference in hours from the current time, ordering the results by this difference in descending order.
The check omits any database listed in the “ignore_databases” parameter and excludes system databases by default.
Results are segregated based on the configured thresholds:
Warning threshold (default: 24 hours)

Output Interpretation

The results are presented in a report format within dbWatch Control Center, with specifics details given for each database. Here are the possible statuses:

“Normal”: Backup is within the acceptable time frame.
“Warning”: Backup is older than the warning threshold but still within the acceptable risk limits.
“Alarm”: Backup is critically old, surpassing the alarm threshold, indicating immediate attention is required.

Backup Statistics SQL Query
The main SQL query used for extracting backup details:

SELECT msdb.dbo.backupset.database_name, datediff(hour, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) as diff_hours, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupset WHERE msdb.dbo.backupset.type in ('D','I') and database_name not in ('master','model','msdb') and database_name IN (SELECT a.name FROM master..sysdatabases a, sys.databases b WHERE a.name=b.name AND convert (varchar(20), DATABASEPROPERTYEX(a.name,'Status'))='ONLINE' AND b.is_read_only !=1) GROUP BY msdb.dbo.backupset.database_name ORDER BY diff_hours DESC

The output from this monitoring job plays a vital role in maintaining the integrity and availability of the database resources managed through dbWatch Control Center.