1

Trying to setup log shipping between two SQL Server 2016 servers using the built-in wizard (right click database > Tasks > Ship Transaction Logs). Wizard succeeds, backup job runs fine, copy/restore jobs fail.

Wizard setup:

  • Configured primary db to backup at a network location. gave SQL Server Agent service full control on that folder. SQL Server service is running under a domain account which also has full control on that folder.
  • Added secondary server, configured copy job to copy to a local folder on that server. Gave service accounts full control on that local folder.
  • Configured restore job with no recovery mode

Wizard completes with no issues and creates jobs on both servers. On the secondary server, when I expand the databases, the database appears in "Restoring" mode. This mode does not go away. When I check the error logs I see the message

Error: 18456, Severity: 14, State: 38.

Login failed for user 'EAGLE\my_username'. Reason: Failed to open the explicitly specified database 'My_Db'. [CLIENT: ]

my_username is the username I used to log into the primary database and run the wizard and this user is a sysadmin on both servers. Why and where is this account even coming in picture? Why not the SQL Job agent service account?

Anyway if I try to manually run the copy job on secondary server. It fails and gives no specific error message except that this step failed. Event viewer or DB error log have no entries for this fail.

How can I make this work?

Improve this question
4
  • Have you somehow denied access to master for that login?
    – Randolph West
    Nov 25, 2016 at 5:17
  • Try logging onto primary (Windows) with the SQL Server service account, then setup log shipping.
    – stacylaray
    Nov 25, 2016 at 7:09
  • Also, you should see the backups hitting the network folder. Ensure the network folder is not a mapped drive but the full unc path. Are you using the same service account for both servers?
    – stacylaray
    Nov 25, 2016 at 7:13
  • @stacylaray it's not a mapped drive, and yes a full UNC path. same service account for both servers' SQL Job Agent service, but different accounts for SQL Server service.
    – joym8
    Nov 25, 2016 at 13:07

1 Answer 1

Reset to default
1

It was a totally unexpected underlying issue. As Hilary Cotter pointed out in this post, running the copy job from command prompt on secondary server "revealed" that .NET 3.5 framework was missing. Installing it fixed the issues. Beleive it or not, I did not do anything else other than installing .NET 3.5 framework.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.