Help Center
Choose product document...
Veeam Backup & Replication 9.5 [Archived]
Veeam Backup Explorers User Guide

Required Microsoft SQL Server Backup Job Settings

To be able to explore and recover necessary databases, 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 the particular transaction. Then you should configure the corresponding log processing options with Veeam Backup & Replication, using VM processing settings in the backup job properties.

Required Microsoft SQL Server Backup Job Settings 

Required Microsoft SQL Server Backup Job Settings Important!

The 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.

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

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

Required Microsoft SQL Server Backup Job Settings Note:

In case when truncating logs with the specified guest processing account fails, Veeam will try to perform it using the NT AUTHORITY\SYSTEM account. Make sure that the SQL Server 2016, 2014 and 2012 has sufficient access rights. See the Veeam Knowledge Base article for more information.

Default settings in previous versions of the SQL Server allow database log truncation being processed by the local SYSTEM account. However, if these settings have been modified, make sure this account is still 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 by the 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. 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 in this case would be the 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. Supports any database restore scenario. Veeam Backup & Replication will periodically ship transaction logs to the backup repository and store them along with the SQL server VM backup, truncating transaction logs on the original VM. Make sure that the recovery model for the required SQL server database(s) is set to full or bulk-logged.

Required Microsoft SQL Server Backup Job Settings 

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

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

SQL DB logging model

Veeam option

 

Truncate logs

Do not truncate logs

Backup logs periodically

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.  

Required Microsoft SQL Server Backup Job Settings Note:

The default location of the temporary folder with .BAK files 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 the 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).  

Veeam Large Logo

User Guide for VMware vSphere

User Guide for Microsoft Hyper-V

Enterprise Manager User Guide

Veeam Cloud Connect Guide

Veeam Agent Management Guide

Veeam Backup Explorers User Guide

Backup and Restore of SQL Server Databases

PowerShell Reference

RESTful API Reference

Veeam Backup FREE Edition User Guide