Database Log backup

Job details

Name:	Database Log backup
Platform:	Sqlserver
Category:	Availability
Description:	This procedure checks the database transaction log backups from the msdb.dbo.backupset table (excluding system databases: master, model and msdb).
Long description:
Version:	2.0
Default schedule:	20 0,2,4,6,8,10,12,14,16,18,20,22 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2014′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore databases	model	The list of databases to be ignored (separated by comma).
warning threshold	4	A warning is triggered if the number of hours since the last transaction log has been backed up is greater than this parameter.
return status when missing backup	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing transaction log backup of a database.
return status when old backup	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the last backup date of the transaction log is older than the “backup date threshold” parameter value.
exclude databases if new	24	If the database has been created within the last 24 hours (default value), no warning/alarm will be triggered.

Job Summary

Purpose: The purpose of this job is to monitor transaction log backups for Microsoft SQL Server instances, specifically focusing on SQL Server versions between 2000 and 2014, excluding system databases (master, model, msdb). It checks the availability and timeliness of the backups to ensure they are performed within defined thresholds.

Why: This job is crucial because transaction logs play a fundamental role in the recovery process of SQL Server databases. Ensuring timely backups means safeguarding against data loss in the event of a failure. If backups are not performed on time or are missing, it can lead to severe business impacts, including data loss and non-compliance with data protection policies.

Manual Checking: You can check the status of transaction log backups manually by executing the following SQL commands against the msdb database:

SELECT database_name, MAX(backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupset
WHERE type = 'L' -- 'L' for Log backups
GROUP BY database_name
HAVING (MAX(backup_finish_date) < DATEADD(hh, -[Your Defined Threshold], GETDATE()))

Job Configuration and Detail

Object: dbw_backup_log_proc
Description: This stored procedure performs the actual check for transaction log backups. It references the msdb.dbo.backupset table to find the last log backup times, excludes the ones for system databases, and triggers alerts based on defined thresholds.

Default Schedule: Every 2 hours throughout the day. This frequency ensures that transaction log backups are regularly checked and issues are identified promptly.

Dependencies:
- dbw_backup_log_proc self-reference to ensure task integrity.
- dbw_ignore_log_db to manage which databases are ignored during the check.

Implementation Details: The procedure executes multiple checks:
- Identifies databases where the last log backup is older than the warning threshold.
- Identifies databases with no transaction log backup history.
- Records and reports the status (Alarm, Warning, OK) based on the findings and defined criteria.

Reports Generated:
- Regularly updating reports on databases with old log backups (older than 48 hours) and those missing log backups are available within the dbWatch reporting framework.
- The report also details the most recent log backup details such as database name, backup finish time, elapsed time, backup sizes, and device information.

Upgrade Path: The job includes specific upgrade scripts designed to transition from earlier version configurations (from versions 1.4 up to 1.9) seamlessly. These upgrades ensure parameters and procedures are up to date with the latest job definitions, correcting any discrepancies like missing or outdated parameters.

Critical Notices:
- This job should run with the required database privileges to access the backupset tables and perform necessary queries.
- Potential adjustments to the thresholds should be considered based on specific business requirements and backup strategies.