Backup All transaction logs

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to manage the backup of transaction logs for databases in a SQL Server environment, specifically focusing on those running in FULL recovery mode.
• Why: This job is crucial for ensuring data integrity and enabling the possibility to restore data to any point in time. Without transaction log backups, only full or differential backups would be available, limiting the restore points and potentially increasing data loss during a disaster recovery.
• Manual checking: You can check transaction log backups manually in the database using the below SQL commands:

SELECT database_name, backup_finish_date, backup_size FROM msdb..backupset WHERE type = 'L' ORDER BY backup_finish_date DESC

Implementation

• This job involves various database operations, including:
  • Creation of necessary tables and procedures for managing backup operations and error handling.
  • Execution of backup operations for each online database adhering to certain properties, as defined in the long description in the XML content. Conditions like database status being online, access level being multi-user, and not being a publication or subscription participant are checked.
  • Handling of ignore lists for databases and compression of backups based on conditions and SQL Server edition.

Dependencies and Cleanup

• There are several critical dependencies for execution:
  • dbw_backup_transaction_log_proc (Main procedure for performing backups)
  • dbw_backup_transaction_log_err_tab (Error table to record any issues during the backup process)
  • dbw_backup_transaction_log_histr (Table to maintain historical logs about backup executions)
• Cleanup on failure ensures that the system remains clean even if the job fails, which helps prevent clutter and potential data confusion in management tables.

Parameters and Configuration

• Some of the parameters the job uses include:
  • Backup directory
  • Whether to delete old backup files (and the age after which files are deleted)
  • Databases to ignore
  • Whether backups should be enabled
  • Specification of the backup device
  • Compression setting
  • Whether to create subdirectories per database

Reporting and Monitoring

• Transaction log backups are tracked and reported, providing essential metrics such as:
  • Count of databases backed up
  • Errors encountered during backups
  • Backup sizes
  • Start and end times of backups
  • The elapsed time for backups

Reporting Examples

• The report also includes execution history for backups and lists any databases that encountered errors during backup.

I hope this summary provides a comprehensive understanding of the job’s functionality and its critical role within the database management environment.