Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Sometimes the name of the server changes and the SQL Server instance name must change accordingly.
For example, suppose your server name is a test and you have an instance named testinstance on your test server.
You can normally connect to Instance as “test\testinstance”.
Suppose that the name of the test server is changed to “prod”.
If you want to query the name of instance with the command Select @@SERVERNAME, you will see that your instance name is still “test\testinstance”.
We can fix this with the code below.
1 2 3 4 | sp_dropserver 'test\testinstance'; GO sp_addserver 'prod\testinstance', local; GO |
If you are using SQL Server 2012 or newer version, you will receive the below error when you execute the above script. Because sp_addserver is no longer supported in SQL Server 2012 and newer versions.
Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60 [Batch Start Line 0]
Feature “sp_addserver” is no longer supported. Replace remote servers by using linked servers.
To solve the error, use sp_addlinkedserver instead of sp_addserver.
1 | sp_addlinkedserver 'prod\testinstance', local |
