pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

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>]
go
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.

MSDN: http://msdn.microsoft.com/en-us/library/ms143799.aspx

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

No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.