SUMMARY
Microsoft SQL Server databases - Full backup is sucessful, but differential and transaction log backups fail
ISSUE
Purpose
Why are my Microsoft SQL Server Differential and Transaction backups failing?
Description
Error message in failed differential or transaction log backup:
The integrity backup table for database '<dbName>', instance 'local' and validate differential or transactional log backup doesn't exist: Please contact the Database Administrator to ensure that CREATE TABLE permissions have been granted on database 'msdb' for the 'Unitrends' agent service account
Message in the successful full backup that was prior:
Failed to create integrity table 'msdb.dbo.UNITRENDS_<dbName>_BACKUPS_STATUS', and will not be able validate sql checkpoint for differential and transactional backups for current backup group.
RESOLUTION
Add the sysadm role for the user the "BP Agent" service runs as, which is NT Authority\SYSTEM by default. Once done, we need run a new FULL backup. Then differential and transactional backups would run as expected.
CAUSE
Unitrends BP Agent runs as the Local System account by default. The user this account runs as does not have permissions to perform the necessary tasks.