Missing database log backup

Job details

Name:	Missing database log 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 transaction log backup (type L) exists.
Version:	2
Default schedule:	45 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 on instances of missing database log backups for SQL Server and Azure SQL Managed Instance databases, ensuring critical transaction log data is consistently backed up.
Why: This job is essential to ensure data recoverability and integrity. Missing log backups can lead to significant data loss and inability to restore databases to a specific point in time, which can be critical in recovery scenarios.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT name AS database_name, create_date AS cr_date
FROM master.sys.databases 
WHERE recovery_model_desc='FULL' 
  AND name NOT IN (SELECT name FROM sys.databases WHERE source_database_id IS NOT NULL) 
  AND name NOT IN (SELECT DISTINCT database_name FROM msdb.dbo.backupset WHERE type='L') 
  AND name IN (SELECT name FROM master.sys.databases WHERE recovery_model_desc='FULL' AND CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'Status'))='ONLINE')
ORDER BY name

Functionality Details

The job operates by checking each database to determine if a transaction log backup exists.
It is specifically designed for databases where the recovery model is set to FULL, as this model maintains complete log backups for accurate point-in-time recovery.
The job includes a default list of databases to ignore during the check, customizable via parameters.
The output includes the database names missing log backups, creation dates of these databases, and highlights whether any specified databases were excluded from the check.

Alert Details

Statuses returned include OK (0), WARNING (1), and ALARM (2), which reflect the severity of missing log backups.
When executed, the job evaluates the number of databases missing log backups and adjusts the status accordingly.

Output Details

Output format provides a detailed list of missing log backup databases.
The job produces a summary message indicating the overall backup status across checked databases.

Scheduling and Execution

This job has a default schedule recommended by dbWatch to run daily at 07:45 AM.
It is set with a maximum run-time of 120 seconds to ensure prompt alerting and minimize performance impact on the database.

Compatibility and Installation

The job is compatible with instances running SQL Server and Azure SQL Managed Instance that do not have an engine-specific configuration.
The job can be directly installed on compatible database instances, featuring express installation functionality for streamlined setup.

Reporting

A report template is included to format the output into a detailed view accessible via dbWatch reports.
The report provides a table view of the databases affected, aiding DBA‘s in identifying and resolving the issue promptly.