My learning
Anoop Agarwal
SQL DBA | 20+ Years in SQL Server & Cloud (Azure) | Database Optimization Expert | Migration Expert | Leadership in HA & DR"
Few days I was helping a friend setup logshipping. He had cloned the production VM as a new VM with anew host name to be used for DR. We could connect to the new DR sql server from SSMS and easily restore all the databases from production. When we tried to setup logshipping, the restore job kept failing. We could this error. I googled for it and found this article https://sqlservercitation.com/fix-agent_type-2-do-not-form-a-valid-pair/ . The moment I read the first line I understood our mistake. As we had cloned the server even though the host was new, but it remained same in sys.servers table. So we quickly changed it to new host name using this bit of code
EXEC sp_dropserver '<old_name>';
GO
EXEC sp_addserver '<new_name>', local;
GO
Reference this article : https://learn.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver16
?
And restated the sever. After this our restore job worked like magic.
My learning from this was to always remember to change the host name on sql server after we have cloned a VM.
?
--
5 个月Insightful