Redirecting Linked Server

Have you been in the situation when all your SQL code is referring to Linked server XYZ and now you have to migrate it to Linked Server ABC?

What do you have to do? Re-Write all stored procedures, functions, views, triggers, which use old server name?

Not necessary. You can redirect your old Linked server name to another location.

Would say you have following Linked Server:

SELECT * FROM sys.servers
WHERE server_id > 0;

In my example my linked server has an IP addressed name "192.168.1.103" and pointed to the same SQL Server host.

If we want to redirect that linked server to another SQL Server host we can do following:

EXEC sp_setnetname
    @server = N'SQL2016_01', -- Original Linked Server Name
    @netname = N'SQL2016_02'; -- Newly redirected SQL Server

After running that statement we will see the following picture:

That means ALL our code, which uses OLD Linked Server name is pointed now to the new SQL Server.

Be very careful with that tool. Potentially you can create cross reference like this:

Linked Server "SQL2016_01" is now referring to actual SQL Server "SQL2016_02" and Linked Server "SQL2016_02" is now referring to actual SQL Server "SQL2016_01".
 That is complete mess which can lead not only to loss/discrepancy of the data, but easy loss of a job.