Microsoft SQL Server Transaction Log Settings
If you back up Microsoft SQL Server, you can specify how Veeam Agent for Microsoft Windows must process database transaction logs:
- At the Guest Processing step of the wizard, make sure that the Enable application-aware processing check box is selected.
- Click Applications.
- In the Processing Settings window, on the General tab, select Process transaction logs with this job.
- In the Processing Settings window, click the SQL tab.
- To specify a user account that Veeam Agent for Microsoft Windows will use to connect to the Microsoft SQL Server, select the Specify Windows account with sysadmin role on SQL Server check box and enter a user name and password for the Microsoft Windows user account.
The Microsoft Windows account used to connect to the Microsoft SQL Server must be assigned the following roles:
- Administrator role on the target computer with Veeam Agent.
- Sysadmin role on the target Microsoft SQL Server.
To provide minimal permissions, the account must be assigned the following roles and permissions:
- SQL Server instance-level roles: dbcreator, public.
- Database-level roles: db_backupoperator, db_denydatareader, public; for system databases (master, model, msdb) — db_backupoperator, db_datareader, public; for system database (msdb) — db_datawriter.
- Securables: view any definition, view server state.
- Specify how transaction logs must be processed. You can select one of the following options:
- Select Truncate logs to truncate transaction logs after successful backup. Veeam Agent for Microsoft Windows will wait for the backup to complete successfully and then truncate transaction logs. If the backup job fails, the logs will remain untouched until the next backup job session.
- Select Do not truncate logs to preserve transaction logs. When the backup job completes, Veeam Agent for Microsoft Windows will not truncate transaction logs.
We recommend that you enable this option for databases that use the Simple recovery model. If you enable this option for databases that use the Full or Bulk-logged recovery model, transaction logs may grow large and consume all disk space. In this case, the database administrator must take care of transaction logs him-/herself.
- Select Backup logs periodically to back up transaction logs with Veeam Agent for Microsoft Windows. Veeam Agent for Microsoft Windows will periodically copy transaction logs to the backup location and store them together with the image-level backup. During the backup job session, transaction logs will be truncated.
For more information, see Microsoft SQL Server and Oracle Logs Backup and Restore.
You can enable transaction log backup in one backup job only. If you have already configured a job that is set up to back up transaction logs, you will not be able to select the Backup logs periodically option in the properties of another backup job.
If you have selected to back up transaction logs with Veeam Agent for Microsoft Windows, you must specify settings for transaction log backup:
- In the Backup logs every <N> minutes field, specify the frequency for transaction log backup. By default, transaction logs are backed up every 15 minutes. The maximum log backup interval is 480 minutes.
- In the Retain log backups section, specify retention policy for transaction logs stored in the backup location.
- Select Until the corresponding image-level backup is deleted to apply the same retention policy for image-level backups and transaction log backups.
- Select Keep only last <N> days of log backups to keep transaction logs for a specific number of days. By default, transaction logs are kept for 15 days. If you select this option, you must make sure that retention for transaction logs is not greater than retention for the image-level backup. For more information, see Retention for Transaction Log Backups.
Veeam Agent for Microsoft Windows automatically excludes its configuration database from application-aware processing during backup. Transaction logs for the configuration database are not backed up.