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