Database log backup

Job details

Name:	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 the transaction log backup statistics (type L) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb).
Version:	2
Default schedule:	30m
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 minutes since the last database transaction log backed.
alarm_threshold	2	The maximum number of minutes since the last database transaction log backed.

Job Summary

Purpose: This job regularly checks the currency of transaction log backups for non-system SQL Server or Azure SQL Managed Instance databases, excluding master, model, and msdb.
Why: Keeping transaction log backups up to date is essential for operational continuity and disaster recovery protocols. Failure to regularly backup transaction logs can lead to significant data loss and affect the ability to restore to a specific point in time, crucial during data recovery situations.
Manual checking: To manually check the transaction log backup status, you can run the following SQL command:

SELECT 
    msdb.dbo.backupset.database_name,
    datediff(minute, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) as diff_minutes,
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_backup_date
FROM
    msdb.dbo.backupset
WHERE
    msdb.dbo.backupset.type = 'L'
    AND database_name NOT IN ('master', 'model', 'msdb')
    AND database_name IN (SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND is_read_only = 0)
GROUP BY
    msdb.dbo.backupset.database_name;

Alert Logic

If the time since the last log backup exceeds the alarm threshold, an alarm status is triggered, indicating immediate attention is required.
If the time since the last backup exceeds the warning threshold but is less than the alarm threshold, a warning status is prompted to indicate the monitoring status without immediate risk.
Databases specifically included in the ‘ignore_databases’ list are excluded from checks to allow flexibility in maintenance schedules for certain databases.

Output Format

The job’s output includes the detailed report on backup status for each database checked, listing:
- Database name
- Time elapsed since the last transaction log backup
- Exact time of the last backup
This output is vital in ensuring that all operational databases are backed up within the acceptable risk thresholds set in the system.

Automated Task Details

Defense against data loss: Checking transaction log backup frequency is a critical line of defense against potential data loss.
Scheduled Review: The default execution schedule for this job is every 30 minutes, which supports proactive monitoring and rapid problem identification.
Alert handling: The system dynamically updates the status based on log backup currency – from normal to warning and ultimately to alarm status if necessary.

This automation and detailed, timely report ensure that database administrators have clear actionable insights into the backup status of each database, thereby aligning with best practices for disaster recovery and business continuity management.