Restore Permissions for Log Shipping

NixNix Posts: 8
edited July 7, 2011 9:07AM in SQL Backup Previous Versions
When the backup job for log shipping runs on the source DB, I seem to have no issues. But when the restore runs on the backup DB, I get error error 890. I setup a new user in AD and gave them sysadmin rights but I cant tell if that is enough or if the new user is even used to perform the restore.
· Share on Twitter

Comments

  • NixNix Posts: 8
    I notice that the initial full restore that is done when the log shipping job is created works just fine because it is performed by the SA or whatever user I put in there initially but all the incremental restores after that were performed by the NT AUTHORITY\NETWORK SERVICE user. Why dose it change users and how do I give that user right to do restores to the destination DB?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Does the owner of the SQL Server job that performs the restore have rights to restore transaction logs on that database? Could you please check, using SSMS, who is the owner of the job?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • NixNix Posts: 8
    It seems to be working now!!! I checked the job and the owner was the AD account that I created for this test but the NT AUTHORITY\NETWORK SERVICE user was still performing the incremental transaction restores. I thought I gave that users sysadmin rights but when I double checked it today I noticed I gave it setupadmin rights instead. After fixing that change things seem to start working. Is it normal to have to give NT AUTHORITY\NETWORK SERVICE user sysadmin rights to do log shipping or am I doing something wrong? I am new to SQL administration so I apologize if this is a dumb question.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    What is the account that is used to run the SQL Backup Agent service? You can check via using the Windows Service Manager application.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • NixNix Posts: 8
    It was the one I created called SQLBackup.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    but the NT AUTHORITY\NETWORK SERVICE user was still performing the incremental transaction restores.
    How did you determine this?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • NixNix Posts: 8
    When I looked at the Activity History for that DB in SQL Backup it would show that user account as the user name that performed the task.
    · Share on Twitter
Sign In or Register to comment.