Hello Everyone,
Today, I have a Requirement to change server the name of SQL Server, After completed this task going to share the sample query for change the
server name in SQL Server.
Some Noted Point Keep in Mind which
are given below
1. If SQL Server is clustered then you
should not rename the server name otherwise you rename it.
2. If you are using replication in SQL
Server then can't change server name.
3. If you are installed the Reporting
Service that time you not rename or change the server name.
First you run below query and find the detail
about you SQL like Host Name, Server Name, Machine Name, Net BOIS name and last
one SQL Server is clustered or not.
SELECT HOST_NAME() AS 'HOSTNAME',
@@SERVERNAME
AS 'SERVERNAME',
SERVERPROPERTY('SERVERNAME') AS 'SERVERNAME',
SERVERPROPERTY('MACHINENAME') AS 'MACHINENAME',
SERVERPROPERTY('COMPUTERNAMEPHYSICALNETBIOS') AS 'NETBIOSNAME',
SERVERPROPERTY('ISCLUSTERED') AS 'ISCLUSTERED'
The
Steps of fallowing process as given below
STEP 1: EXECUTE BELOW TO DROP THE CURRENT SERVER NAME
EXEC SP_DROPSERVER 'OLD SERVER NAME'
STEP 2: EXECUTE BELOW TO ADD NEW SERVER NAME. MAKE SURE
LOCAL IS SPECIFIED
EXEC SP_ADDSERVER 'NEW SERVER NAME', 'LOCAL SERVER'
STEP 3: RESTART SQL SERVICES FOR REFRESH ALL THE STUFF
STEP 4: VERIFY THE NEW SERVER NAME WHICH YOU CHANGED
USING THE BELOW QUERY
SELECT @@SERVERNAME
AS SERVERNAME
SELECT * FROM SYS.SERVERS WHERE SERVER_ID = 0
The output look like below image.
Please put a comment or let me know your
thoughts if you have. Thank you! :)