Need to rename SQL 2K computer 1

[tbode2006]

My client wants me to rename the process SQL server computer name to match a new company standard. The server instance currently is the same as the current computer name. I have found the following script:

DECLARE @machinename sysname, 
 @servername sysname, 
 @instancename sysname 
 
 
SELECT 
 @instancename = 
  CASE WHEN charindex('\', @@servername) = 0 THEN '' 
  ELSE SUBSTRING(@@servername, CHARINDEX('\', @@servername), (len(@@servername)+ 1) - CHARINDEX('\', @@servername)) 
  END 
SET @machinename = convert(nvarchar(100), serverproperty('machinename')) + @instancename; 
 
 
EXEC sp_dropserver @@servername; 
EXEC sp_addserver @machinename, 'local' 
print 'renamed to ' + @machinename
[\code]

On my office system, my instance shows up as LOCAL. Renaming the computer was no issue. When I went to modify an ODBC connection, the new computer name was there.

My question is: am I required to rename my instance if the instance is the existing computer name and not LOCAL? Would not the existing instance be broadcast as being available?

 

[mrdenny]

You can't rename the instance name of a SQL Server instance. Only the machine name.

That script is way more complex than is needed.

exec sp_drop_server 'OldServerName'
go
exec sp_add_server 'NewServerName', 'local'
go

Then restart the server. If it's a named instance then include then \InstanceName in both the old server name and new server name.

You will also need to update the sysjobs table with the new server name.

update sysjobs
    set originating_server = 'NewServerName'
where originating_server = 'OldServerName'

That code is untested so I may have the column name spelled wrong.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[tbode2006]

Thanks Denny,
The prodedures are sp_dropserver and sp_addserver by the way. When I arrived on site it took just 30 minutes to make the change.