SQL Server 2012 – How to Fail Back After Log Shipping Failover

So in SQL Server 2012 SP1 I did this:

EXEC msdb..sp_helptext 'sp_verify_jobstep';

I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(@server))))

So, run this code with a PRINT:

IF NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(N'ServerB')))
BEGIN
  PRINT 'Not found.';
END

Then verify exactly what is in master.dbo.sysservers and also check master.sys.servers. I suspect either those two views don't match or the name in both is not exactly ServerB. Show the actual syntax you used to call the procedure and verify that the server wasn't renamed with leading/trailing spaces, whitespace or other non-printing characters.

Another idea is to not use the system procedure to set up log shipping in the first place. Log shipping isn't all that cumbersome to set up and in the past I've always just set up the backup / restore jobs manually. Also the stored procedure you called first only takes monitor_server as a potential server name where it could get into trouble connecting somehow, and it is nullable. Are you sure you need to pass it? Why would you want the monitor server to be the same as the source server? If one goes down they're both down by definition. What happens when you don't explicitly name a server as the monitor_server and leave that and other related parameters null?

Also these various hooks and potential conflicts are why I don't ever bother to rename servers that are already running. I'd rather pay the extra cost of wiping, reinstalling and restoring than dealing with all of this after the fact - especially if the quick rename was sold as something "hassle-free."

Two options:

• On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
• Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH 
   PASSWORD = 'ThisIsAVerySecurePassword', 
   SID = 0x70DD0D59ADF3FD4ABB000037826A2888 

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)