Microsoft SQL Server 2005/2008:Change Local Server Name of running SQL Server.

Sometime we change the local server host name where sql server is running.SQL Server will automatically take this name as new server/instance name.But the problem with @@servername where value still old server name when sql server installed.

Its become very necessary when we plan to do replication.To avoid the conflicts of the server name, we should change the local server name to changed hosted server name.

For this, Follow below steps to get new name of sql server server/instance name.

1.Check local server name

Select @@servrname

Go

 

2.Change the computer/hosted server name to 'Bhavu' .

Restart the machine.Name will reflect into server/instancename.

3.Drop Old server name by executing this query.

Replication should be dropped before dropping it.

Remove all linked server.

See Error ,There are still remote logins or linked logins for the server 'Bhavu\SQL2005'.

sp_dropserver 'Bhavu\SQL2005';
GO

3.Add New Server Name.

sp_addserver 'Bhavu', local;
GO

Restart the sql server.

4.New Change reflected if the value of @@servername change.