Missing database backup

Job details

Name:	Missing 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 for each database if backup (type D and I) exists.
Version:	2
Default schedule:	30 7 * *
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).
return_status_value	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0)

Job Summary

Purpose: The purpose of this job is to monitor and alert for missing backups in SQL Server databases. It specifically checks if each database has a type D (full) or type I (differential) backup existing.
Why: This job is critical to ensure the data availability and integrity in SQL Server databases. Without recent backups, the database is at risk of data loss in case of a failure, potentially leading to significant operational disruptions and data recovery costs.
Manual checking: You can manually check missing database backups by issuing the following SQL command:

SELECT master.dbo.sysdatabases.NAME AS database_name, master.dbo.sysdatabases.crdate AS cr_date FROM master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.database_name IS NULL and master.dbo.sysdatabases.name not in ('tempdb') and master.dbo.sysdatabases.name in (select name from master..sysdatabases where convert (varchar(20), DATABASEPROPERTYEX(name,'Status'))='ONLINE') ORDER BY cr_date asc

Job Details

“Missing Database Backup” job is designed to run only on instances that are either Microsoft SQL Server or Azure SQL Managed Instance.
This job operates by using a SQL query to identify databases without backups and provide a list of those databases along with the creation date of each.
An additional parameter “ignore_databases” allows specifying databases that should be excluded from checking by providing a comma-separated list of database names.
The job uses a JavaScript code to process the query results, check the list of ignored databases, and set the return status along with the appropriate message. The status value can be set to ALARM (2), WARNING (1), or OK (0) based on the number of missing backups detected against predefined thresholds.
The status of the job along with a detailed message, including a list of databases missing backups, is provided.

Output and Reporting

Attribute	Details
Job Name	Missing database backup
Version	2
Company	dbwatch.no
Category	Availability
Description	Ensures all active databases have recent backups.
Default Schedule	Daily at 07:30 AM

Additional Reporting Details

The detailed report includes the list of databases for which backups are missing.
Output is provided in table format within the dbWatch report to aid in visualization and further analysis. The table categorizes databases without backups, helping in prompt identification and action.
The dbWatch Report Template also facilitates a continuous monitoring schedule, defaulting to an hourly checkup, to maintain up-to-date status reporting on the backup status of each database.

Note: The job setup and execution require that it’s only deployed on compatible systems as defined in its configuration (SQL Servers and Azure SQL Managed Instances without versions before 2000).