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

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

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

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).