This document is not supported any longer.How It Works: SQL Server and Transaction Log Backup
If you plan to set up the SQL Server backup job to back up transaction logs, consider that it will comprise 2 jobs:
- SQL Server image-level VM backup job (‘parent’) named <job_name>, for example, Test Job. This is the job you configure explicitly in the management console; its session starts on schedule or is started manually by user.
- Transaction log backup job (‘child’) named with suffix: <job_name> SQL Backup, for example, Test Job SQL Backup. This job is created by Job Manager if it detects that ‘parent’ (VM backup) job is scheduled to back up at least one SQL Server with application-aware image processing switched on and transaction log backup enabled.
Transaction log backup job (‘child’) is triggered by VM backup (‘parent’) — this sequence ensures that VM (and database) restore point will be present when it comes to log replay. ‘Child’ job runs permanently in background, with the specified frequency of log shipping to repository. Its session data is kept in the Veeam Backup & Replication database and is displayed in the Veeam backup management console.
The whole process goes as follows:
Stages 1 and 2
When scheduled, Job Manager (Veeam component working on Veeam backup server) launches ‘parent’ job that creates an image-level backup of SQL Server VM and stores it to backup repository.
Stage 3
A new ‘child’ job session starts, and Veeam Backup & Replication installs a runtime component Veeam Log Shipper Service to the VM guest OS in order to support guest processing and log handing. This service runs during the ‘child’ job session and collects information about databases whose logs should be processed. The service also detects whether it is possible to store logs into the repository through direct access, or using a log shipping server. When ‘child’ job session ends, the service is stopped and removed from guest. Then a new session starts, and the service is installed again.
Transaction log backup is performed by SQL Server; this operation also includes log truncation so that the space can be reused. These log backups are stored as .BAK files in a temporary folder in the SQL Server VM guest file system.
|
Default location of the temporary folder is %allusersprofile%\Veeam\Backup. If it is necessary to use another location, you can specify it in the SqlTempLogPath (STRING) registry value that you can create under HKLM\SOFTWARE\Veeam\Veeam Backup and Replication registry key on your SQL Server VM (you may need to restart the Log shipper service on that VM for the change to take effect). |
Stage 4
Every 15 minutes (default frequency) Job Manager component detects what databases currently exist on SQL Server, and maps this data with VM backup information kept in Veeam Backup & Replication database. This periodic mapping reveals the databases for which Veeam Backup & Replication must ship transaction logs to repository during this 15-min interval.
If there are any logs that for some reasons were not shipped to backup repository by Veeam during the previous interval(s), they will be also included in the processing list. To detect those remaining logs, Veeam enumerates .BAK files in the temporary folder.
Stage 5
Transaction log backup files (.BAK) are transferred from temporary location on SQL Server to the target location in backup repository, either directly or via a log shipping server. Source-side Veeam transport service compresses log data to be transferred according to its built-in settings. On the repository side, data is compressed according to ‘parent’ job settings (see the "Data Compression and Deduplication" section of the User Guide for details).
As soon as data is copied to the target, transaction log backup files are deleted from the temporary folder on SQL Server.
|
Transaction logs that for some reason were not processed during log backup interval remain in that temporary folder and are processed during the next log backup interval (see Stage 4). |
The figure below depicts in detail what goes on within a log backup interval for each SQL Server VM in the job (that has log backup option enabled).
Related Topics