Sql-server – How to fix an odd LSBackup job error in Log Shipping

log-shippingsql serversql server 2014

Configuration:

  • LSPrimary01 – Log Shipping Primary
  • LSSecondary01 – Log Shipping Secondary
  • OddServer03 – This server is not part of the LS configuration, the odd man out
  • Sean\LSServiceAccount – The domain user service account for log shipping on all servers

A colleague of mine and I were configuring Log Shipping in a test environment. We took a full COPY_ONLY backup of a database on LSPrimary01, a log backup, copied to LSSecondary01 and restored the backup and log WITH NORECOVERY. Then used the GUI on LSPrimary01 to configure Log Shipping. We already configured file sharing and proper permissions for Sean\LSServiceAccount.

The following error was the result:

  • START OF TRANSACTION LOG BACKUP
  • Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping)
  • Error: Failed to connect to server OddServer03.(Microsoft.SqlServer.ConnectionInfo)
  • Error: Login failed for user 'Sean\LSServiceAccount'.(Net SqlClient Data Provider)
  • END OF TRANSACTION LOG BACKUP

I ran the following query to verify my Log Shipping configuration:

SELECT 
      [primary_database]
      ,[backup_directory]
      ,[backup_share]
      ,[backup_retention_period]
      ,[monitor_server]
      ,[user_specified_monitor]
      ,[monitor_server_security_mode]
      ,[last_backup_file]
      ,[last_backup_date]
  FROM [msdb].[dbo].[log_shipping_primary_databases]

Everything in the result set was correct, save monitor_server, it had the value of OddServer03! As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option.

We never intended having a monitor server in the mix. We use Quest Spotlight to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind.

One other perplexing matter, the job that is getting created is LSAlert_OddServer03 instead of LSAlert_LSPrimary01.

Why is the service account trying to authenticate to a server that isn't part of the configuration?

Best Answer

My suspicion would be that even after removing the Log Shipping configuration the Log Shipping system tables are not being cleaned up properly. I would suggest the following steps from blog.sqlauthority.com for your situation:

1. Update the arguments for the command below and execute on LSPrimary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'Primary_Database_Name'
,@secondary_server = N'Secondary_Server_Name'
,@secondary_database = N'Secondary_Database_Name'
GO

2. Update the arguments for the command below and execute on LSSecondary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO

3. Run the query from your question again and examine the results.

If there are still improper entries, consider manually removing them. An example of something you could do would be this:

SELECT * INTO Tempdb.dbo.TemporaryCopy_log_shipping_primary_databases
FROM msdb.dbo.log_shipping_primary_databases 
--please note this only lasts until the database engine is restarted!

UPDATE dbo.log_shipping_primary_databases
SET monitor_server = 'LSPrimary01'
WHERE monitor_server = 'OddServer03'

Before running your own version of the commands above, which will try to clean out the system tables, it's worth considering whether you want to try skipping the GUI when trying to reset the Log Shipping configuration. If you want to try that, I would recommend scripting out your current configuration (Right-click your database in SQL Server Management Studio --> Choose Properties --> Click Transaction Log Shipping in left sidebar -> Click Script Configuration button on the bottom right).

You can then examine the results for what commands it uses as well as whether you see it inserting the OddServer03 value anywhere. At that point, you could clean everything up as described in the 3 steps above and then add Log Shipping back via the script you extracted and updated as needed. That would give you 100% control over what is actually executing and it would be less likely for the OddServer03 to somehow end up in the configuration.

NOTE: If any of the above links break, use Google to search for them: - dba.stackexchange.com site for the title of 'Can I deactivate log shipping jobs without raising errors' - blog.sqlauthority.com site for the title of 'Removing Multiple Databases from Log Shipping'

Related Question