Backup All transaction logs


Job details

Name: Backup All transaction logs
Platform: Sqlserver
Category: Maintenance
Premium package: Maintenance package
Description: Takes backup of all transaction logs for databases running in FULL recovery mode.
Long description: Takes backup of all transaction logs for databases running in FULL recovery mode.
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name Default value Description
backup directory Directory path for backup files. Exp. D:\Backup_db\
delete old backup files (in hours) 48 The task deletes backup files older than the parameter value (in hours).
ignore databases List over the database (separated by comma) which will be ignored from being backuped.
enable NO If set to “YES“ the task will run “backup database” command for every online database. This task is meant for Desktop editions of SQL Server where SQL Agent service is not running. Default value is “NO”.
backup device null Name of the backup device. If this parameter is configured (set to value “null”), the “backup directory” and “delete old backup files (in hours)” parameters will be ignored. Only DISK type devices are supported.
compress backup YES If set to “YES“ the “WITH COMPRESSION“ option will be added to “BACKUP LOGDDL command. COMPRESSION is not supported on Express Edition and MS SQL Server 2005.
create subdir YES Creates the sub directory (if it does not exist) for each database that is backed up. The xp_create_subdir procedure is used.

Job Summary

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

Implementation

Dependencies and Cleanup

Parameters and Configuration

Reporting and Monitoring

Reporting Examples

Database Name Backup Start Backup Finish Size (MB)
Database1 10 AM 10:05 AM 50 MB
Database2 11 AM 11:06 AM 45 MB

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