Help Center
Choose product document...
Veeam Backup & Replication 9.0
Veeam Backup Explorers User Guide

Required Microsoft SQL Server Backup Job Settings

To be able to explore and recover the necessary database, you should have a transactionally-consistent backup of your Microsoft SQL Server created successfully. For details, see the Veeam Backup & Replication User Guide for VMware or for Hyper-V platform.

Also, you need to decide whether you need to recover your SQL database items to the latest available restore point only, to any point in time, or to the state before a particular transaction. Then you should configure the corresponding log processing options with Veeam Backup & Replication, using the VM processing settings in backup job properties.

Required Microsoft SQL Server Backup Job Settings Important!

Remember that application-specific log handling options required for your restore scenario are available on the SQL tab of the VM's guest OS processing settings. The SQL tab becomes active only if Veeam Backup & Replication is instructed to process transaction logs for SQL server (not 3rd party application). So you should open the General tab and make sure that  the Process transaction logs with this job (recommended) option is selected in the Transaction logs section.

Required Microsoft SQL Server Backup Job Settings 

Then go to the SQL tab and select the option you need:

  • Truncate logs – if selected, this option will allow only for the database restore to the state as of currently selected VM restore point (no logs to replay). Any recovery model can be set for SQL server database; logs written by SQL server will be truncated by Veeam Backup & Replication.

Required Microsoft SQL Server Backup Job Settings Note:

If you want to use this option, note that in case log truncation with the specified guest processing account is not a success, Veeam will try to perform it using NT AUTHORITY\SYSTEM account, so for SQL Server 2012 or SQL Server 2014 make sure it has sufficient rights (see this Veeam Knowledge Base article for more information).

As for SQL Server 2005, 2008 and 2008 R2, default settings in these versions allow for database log truncation by local SYSTEM account (however, if they were modified, make sure this account is permitted to truncate logs).

Required Microsoft SQL Server Backup Job Settings Tip:

An alternative method can be also recommended if you want to prevent storage space from being occupied with growing log files: set the database logging model to simple and use the Don’t truncate logs option (as described below). This method does not involve log truncation, so it does not require specific permission assignment.

  • Do not truncate logs – this option instructs Veeam to preserve logs (if any) on the original SQL server (nor they will be truncated, neither will be backed up by Veeam Backup & Replication). With this option selected, your database administrator will have to take care of database logs. Applicable restore scenario –  database restore to the state as of currently selected VM restore point.

Required Microsoft SQL Server Backup Job Settings Important!

To prevent log files from growing rapidly, it is strongly recommended NOT to use this option if full or bulk-logged recovery model is set up for your SQL server database; simple recovery model is the recommended setting.

  • Backup logs – if selected, this option will support any database restore scenario (to the state as of in currently selected VM restore point, to any point in time or to the state before particular transaction). Veeam Backup & Replication will periodically ship transaction logs to the backup repository and store them with the SQL server VM backup, truncating transaction logs on the original VM. Make sure that recovery model for the required SQL server database(s) is set to full or bulk-logged. Contact your database administrator, if necessary.

Required Microsoft SQL Server Backup Job Settings 

Consider that if it is possible to establish a direct connection between the VM guest OS and backup repository, log files will be shipped directly from the VM guest OS to the backup repository. This is the optimal method, as it does not involve additional resources and puts less load on the VM guest OS. Otherwise, files will be shipped via log shipping server(s). You can instruct Veeam Backup & Replication to choose a log shipping server automatically from the list of available ones, or to use a specific server.

Required Microsoft SQL Server Backup Job Settings Note:

If direct connection is possible, files will be always transferred from VM guest to repository directly (regardless of the configured log shipping server, as this server will not be involved). This approach helps to optimize performance at file transfer.

To be able to restore to any point in time or to the state before particular transaction, make sure that recovery model for the database is set to full or bulk-logged. Contact your database administrator, if necessary.

The following table lists possible database logging models and Veeam backup options, describing all combinations.

Veeam option:  

Truncate logs

Do not truncate logs

Backup logs periodically

SQL DB
logging model:

Simple

Databases in this mode are skipped from this type of processing.

Though it is a default option, there is no sense in applying it in this case.

Applicable option for this mode.

Databases in this mode are skipped from this type of processing.

Log files do not grow (and do not need to be backed up), so this option does not make sense in this case.

Full

Applicable option. Veeam performs “backup to NUL” for log files on guest.

Applicable but not recommended to use without native or 3rd party means of log truncation or backup – otherwise, logs will increase in size.

Applicable option. Log backup files (.BAK) are copied from the temporary folder on SQL Server to Veeam repository.  As soon as data is copied to target, .BAK files are deleted from source.

Bulk-logged

Applicable option. Veeam performs “backup to NUL” for log files on guest.

Applicable but not recommended to use without native or 3rd party means of log truncation or backup – otherwise, logs will increase in size.

Applicable option. Log backup files (.BAK) are copied from the temporary folder on SQL Server to Veeam repository.  As soon as data is copied to target, .BAK files are deleted from source.

Veeam Large Logo

User Guide for VMware vSphere

User Guide for Microsoft Hyper-V

Enterprise Manager User Guide

Veeam Cloud Connect Administrator Guide

Veeam Backup Explorers User Guide

Backup and Restore of SQL Server Databases

PowerShell Reference

RESTful API Reference

Veeam Direct Restore to Microsoft Azure

Veeam Backup FREE Edition User Guide

Veeam ONE Documentation

Veeam Endpoint Backup Documentation

Veeam Management Pack Documentation