SQL Differentials and Transactional Logs backup fails on SQL Server 2008 R2, SQL Server 2012 or higher due to insufficient Unitrends agent service account privileges.

SUMMARY

Resolve issues with SQL 2008, SQL 2008 R2, SQL 2012+ differential and transactional log backups.

ISSUE

VSS SQL Differentials and Transactional log backups fail

RESOLUTION

By default, the Unitrends agent service (‘Bp Agent’) logs on and runs as ‘Local System account’. To be able to backup SQL databases, the service account has to be granted minimum rights to backup and restore databases or be configured to log in as ‘sysadmin’. Use the steps below to set minimum backup and restore permissions by granting rights to NT AUTHORITY\SYSTEM.

Step 1

Define minimum rights for the Backup Role Local System User using Model DB, and define minimum rights to the Backup Role Local System User using Master DB.

USE [model]
GO
exec sp_addrolemember N'db_backupoperator', N'NT AUTHORITY\SYSTEM'
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'dbcreator'
GO

Step 2

Define minimum rights for the Backup Role Local System for each SQL database.

USE [db_name];
GO
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]  WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember "db_backupoperator", "NT AUTHORITY\SYSTEM";
GO

SQL Management Studio Guide Reference

MS SQL Server Transaction / Differential error: The integrity backup table for database 'XXX', instance 'local' and validate differential or transactional log backup doesn't exist:

CAUSE

Unitrends agent service account ‘Bp Agent’ must be granted minimum rights to backup and restore SQL databases.

NOTES

For more detailed information on backup failures and performance issues see Common Backup Failures and Performance Issues