This one is simple but interesting...
Log shipping which was running for an year, suddenly stopped working. Log shipping monitor says that there are no backups restored for last 14849 minutes... Why?
We started figuring this out, the copy and restore jobs were running fine but only the backup job was failing.
What was the error generated by the Log shipping backup job?
*** Error: Could not retrieve backup settings for primary ID 'abfb9a42-207e-451b-968c-3ac29edc16f9'.(Microsoft.SqlServer.Management.LogShipping) ****** Error: Failed to connect to server SQLSERVER2001.(Microsoft.SqlServer.ConnectionInfo) ****** Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(.Net SqlClient Data Provider) ***----- END OF TRANSACTION LOG BACKUP -----
But the name of the SQL Server name is SQLSERVER2012, so why is it looking for SQLSERVER2001? This is because the server was renamed from SQLSERVER2001 to SQLSERVER2012 recently but the sys.servers was not updated with this change.
As a result, SELECT @@SERVERNAME still points to old name so we ran commands given below to update sys.server entry and then RESTARTED SQL Server:
EXEC sp_dropserver 'Your_OLD_Computer_Name'
EXEC sp_addserver 'Your_NEW_Computer_Name', 'local'
Now we reconfigured log shipping to make it work. You can either do this or update the Log shipping backup with correct SQL Server instance name or create a SQL Server alias pointing back to new server name to make it work. Still not working? Leave the error message in the comments here....
Rename a Computer that Hosts a Stand-Alone Instance of SQL Server - http://msdn.microsoft.com/en-us/library/ms143799.aspx
In this case we mostally re asign the logshipping.
Hello Chidambaram ,
My Monitor server Log shipping table are not updated . Could you please let me know why these tables are not updated with backup/restore/alerts information .
Primary server -running under local account
Primary server agent - running under the domain account
Secondary server -running under local account
secondary server agent - running under same the primary domain account
Mirror server -running under local account
Mirror server agent - running under the different domain account
Could you please explain about the what kind of security we need to provide the agent accounts .
This works. Thanks for a Wonderful article.
This worked... Thanks a lot!
I just changed the name in the OS cmnd on the Job in the Agent... seems to be working fine.
Thanks for sharing this. It really helped me.
Brilliant, this helped me allot, thank you Sakthivel Chidambaram
Greetings for the day,
i am getting the error while configuring log shipping its ran successfully after 3 days after I am getting LS_Backup Job is running continuously from last 1 day. In view history of the backup job i am getting the below error:
* Error: Could not retrieve backup settings for primary ID '5f6eb97b-3c0e-4ad4-ae35-62aec3248132'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-03-19 14:24:52.96 *** Error: Failed to connect to server AUDDBAS1.(Microsoft.SqlServer.ConnectionInfo) ***
2015-03-19 14:24:52.96 *** Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)(.Net SqlClient Data Provider) ***
2015-03-19 14:24:52.96 ----- END OF TRANSACTION LOG BACKUP ----
Also i had tried to reconfigure the log shipping but i am getting same error.
i am not able to find the perfect solution for this .please suggest me how to resolve the issue
my server name and host name are same AUDDBAS1
Hate to say this, but if you rename a Windows server AFTER installing SQL Server, you're going to be in for a few unpleasant surprises. Always de-install and re-install would be my advice, there are too many issues caused by renaming to make the time saved not doing it worthwhile.
That is a great. It helped me a lot. I could no get log shipping running in the fist place.
Thank a lot!
your blog post here was super-useful, thank you.
in this sentence, did you mean to say "update the log shipping backup JOB" ?
You can either do this or update the Log shipping backup with correct SQL Server instance name
Reply from Sakthi:
Yes, I mean log shipping backup job