Ask the Community
Groups
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. - Connect IT Community | Kaseya
<main> <article class="userContent"> <h2 data-id="summary"><strong>SUMMARY</strong></h2> <p>Resolve issues with SQL 2008, SQL 2008 R2, SQL 2012+ differential and transactional log backups.</p> <h2 data-id="issue"><strong>ISSUE</strong></h2> <p></p> <p>VSS SQL Differentials and Transactional log backups fail</p> <h2 data-id="resolution"><strong>RESOLUTION</strong></h2> <p>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 <b><i>NT AUTHORITY\SYSTEM</i></b>. </p> <h3 data-id="step-1">Step 1</h3> 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. <pre class="code codeBlock" spellcheck="false" tabindex="0"> USE [model] GO exec sp_addrolemember N'db_backupoperator', N'NT AUTHORITY\SYSTEM' GO EXEC master..sp_addsrvrolemember <a href="https://kaseya.vanillacommunities.com/profile/loginame" rel="nofollow">@loginame</a> = N'NT AUTHORITY\SYSTEM', <a href="https://kaseya.vanillacommunities.com/profile/rolename" rel="nofollow">@rolename</a> = N'dbcreator' GO</pre> <h3 data-id="step-2">Step 2</h3> Define minimum rights for the Backup Role Local System for each SQL database. <pre class="code codeBlock" spellcheck="false" tabindex="0"> 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</pre> <h2 data-id="sql-management-studio-guide-reference">SQL Management Studio Guide Reference </h2> <a rel="nofollow" href="/home/leaving?allowTrusted=1&target=https%3A%2F%2Funitrends-support.zendesk.com%2Fhc%2Fen-us%2Farticles%2F360013150377">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:</a><br> <h2 data-id="cause"><strong>CAUSE</strong></h2> <p>Unitrends agent service account ‘Bp Agent’ must be granted minimum rights to backup and restore SQL databases.</p> <h2 data-id="notes"><strong>NOTES</strong></h2> <p>For more detailed information on backup failures and performance issues see <a rel="nofollow" href="/home/leaving?allowTrusted=1&target=https%3A%2F%2Funitrends-support.zendesk.com%2Fhc%2Fen-us%2Farticles%2F360013174757%3Fq%3DSELECT%2BArticleNumber%252CId%252CSummary%252CResolution__c%252CAttachment__Body__s%252CCause__c%252CNotes__c%252CAttachment__ContentType__s%252CAttachment__Length__s%252CAttachment__Name__s%252CTitle%252CKnowledgeArticleId%252CDescription__c%2Bfrom%2BArticle__kav%2Bwhere%2BPublishStatus%253D%2527Online%2527">Common Backup Failures and Performance Issues</a></p> </article> </main>