Friday, July 17, 2009

Correct the name of a SQL server in SQL 2005

Renamed a virtual server, but your SQL server instance still reflects the old name?

verify what SQL Server thinks it is by

select @@servername

rename the server with:

sp_dropserver [sqlserveroldname<\instancename>]
sp_addserver [sqlservernewname<\instancename>], local

restart the SQL Server Service

verify the new name by select @@servername

Note: this isn't a huge deal in SQL 2005 as it was in previous versions. You'll notice, for example, you can still access your SQL server even if it thinks it is in the wrong name, and the SSMS object explorer looks accurate. But for remote logins and I would assume linked server connections, having those names out of sync could be fun.


EDIT: Note that you cannot rename the instance name of a named instance, only the server name.