Specifying Microsoft SQL Server Transaction Log Settings

By default, Veeam Backup & Replication creates application-consistent image-level backups of VMs running the Microsoft SQL Server application and truncates transaction logs after each successfully completed backup session — this will allows you to restore Microsoft SQL Server databases using specific backups. To protect mission-critical Microsoft SQL Server databases, you can instruct Veeam Backup & Replication to create secondary restore points with transaction logs in addition to primary image-level backups — this will allow you to restore your databases to specific points in time.

To back up Microsoft SQL Server transaction logs periodically, do the following:

1. Switch to the SQL tab and select the Backup logs periodically option.
2. In the Backup logs every field, specify how frequently you want transaction logs to be backed up. The maximum field value is 480 minutes.
3. In the Retain log backups section, choose either of the following options:

• Select the Until the corresponding image-level backup is deleted option if you want to remove transaction log backups and the related image-level backups at the same time, according to the retention policy settings specified at step 4.
• Select the Keep only last <N> days of log backups option if you want to retain transaction logs for a specific time period, regardless of the retention policy settings specified for image-level backups. Note that image-level backups must always be kept for a longer period than the related transaction log backups.

For more information on how Veeam Backup & Replication retains transaction logs, see the Veeam Backup & Replication User Guide, section Microsoft SQL Server Log Backup.

4. In the Log shipping servers section, choose whether you want to use a specific Windows server to transfer transaction log backups or let Veeam Backup & Replication choose it automatically to reduce the load on the backup server.

By default, Veeam Backup & Replication automatically chooses a log shipping server for each of the processed VMs based on network settings and rules listed in the Veeam Backup & Replication User Guide, section Log Shipping Servers. You can also manually limit the list of machines that may be used as log shipping servers — to do that, click Choose, select the Use the specified servers only option and then select check boxes next to the necessary Windows servers.

For a Windows server to be displayed in the list of available log shipping servers, it must be added to the backup infrastructure as described in the Veeam Backup & Replication User Guide, section Adding Microsoft Windows Servers. Keep in mind that the list will also include Linux servers added to the backup infrastructure; however, Linux servers cannot be used as log shipping servers due to technical limitations in the current version.

You can also choose not to truncate logs at all. However, keep in mind that this option requires databases to use the simple recovery model. Otherwise, transaction logs may grow large and increase the storage space consumption significantly. For more information on recovery models used by Microsoft SQL databases, see Microsoft Docs.

Considerations and Limitations

When you configure transaction log settings, consider the following:

• If a processed VM runs Microsoft SQL Server along with Oracle Server and transaction log backup is enabled for both applications, the Microsoft SQL Server transaction logs will not be backed up — Veeam Backup & Replication will create transaction log backups for the Oracle Server application only.

• If a processed VM runs Microsoft SQL Server that hosts the Veeam Backup & Replication configuration database, its transaction logs will not be backed up:

• If the Microsoft SQL Server has the SQL Server Always On availability groups feature disabled, the configuration database will be excluded from application-aware processing automatically.
• If the Microsoft SQL Server has the SQL Server Always On availability groups feature enabled, you will have to exclude the configuration database from application-aware processing manually, as described in this Veeam KB article.

For more information on the SQL Server Always On availability group feature, see Microsoft Docs.