Challenge
After installing Veeam 9.5 Update 3, SQL logs are not truncated during backup, but the truncation task is still marked as a success in the job statistics. SQL Log Backup sessions complete with 0 KB transferred.
You can confirm if any SQL VMs are affected by this change by checking the Guest Helper log located on the Guest VM.
GuestHelper log location: \\GuestVM\c$\ProgramData\Veeam\Backup\VeeamGuestHelper_%date%.log
The specific error you will find in the GuestHelper log if affected is the following:
Description = The UPDATE permission was denied on the object 'backupset', database 'msdb', schema 'dbo'.
Cause
This is due to a change in required SQL Permissions in 9.5 Update 3. These permissions requirements were changed to resolve a previous issue with how Veeam sends requests to SQL to finalize database backup and log truncation. The specific change in permissions is that the msdb system database now requires db_datawriter role, whereas before this was not needed.
Required SQL Permissions for SQL log backup and truncation can be found here:
https://helpcenter.veeam.com/docs/backup/explorers/vesql_permissions.html?ver=95
Solution
To resolve this issue, please give the following SQL permissions to the account credentials specified to use for Guest Processing:
- Sysadmin role, or the following can be used if you need to provide only minimal permissions:
- SQL Server instance-level roles: dbcreator and public
- Database-level roles: db_backupoperator, db_denydatareader, public
- For System Databases:
- master - db_backupoperator, db_datareader, public;
- msdb - db_backupoperator, db_datawriter, db_datareader, public
- Securables: view any definition, view server state
- For truncation of SQL Server 2012 or SQL Server 2014 database transaction logs, this account should have the db_backupoperator database role (minimal required) or the sysadmin server role.