Technical Article

Rename SQL Server

,

Utilizing sp_dropserver and sp_addserver system procedures we will change local SQL Server name to a specified one, or, by default, to the WINS machine name.
Just another utility in my DBAservice toolbox database.
Notes:
1.Tested in SQL Server7.
2. In some cases it is necessary to rerun sql server setup in order to start the server after renaming. Neither data nor version is affected.

Create proc uspRenameServer 
@pNewName varchar(256)=null--If NULL we will attempt to rename server to the WINS machine name
/*
Purpose: renames SQL server. 
Server: all
Database: DBAservice
Developed: Yul Wasserman 03/08/02
*/AS
Declare @OldName varchar(256)
Declare @NewName varchar(256)
set @OldName=''
select @OldName=isnull(srvname,'') from  master.dbo.sysservers where srvid=0 
If @pNewName is NULL
Begin
create table #NName (NName varchar (256))
insert #NName exec master.dbo.xp_getnetname
select @NewName=Nname from #Nname
drop table #Nname
End
ELSE If @pNewName is not NULL
Begin
select @NewName=ltrim(rtrim(@pNewName))
End

If @OldName<>@NewName
BEGIN
IF @OldName <>''
BEGIN
print 'Attempting to drop server '+@OldName
Exec master.dbo.sp_dropserver  @OldName
END
print 'Attempting to add server '+@NewName
Exec master.dbo.sp_addserver @NewName,'local'
END
If isnull(@@Servername,'')<>@NewName 
Begin
Print 'Please shut down and restart SQL Server in order to complete renaming.' 
End
Else If isnull(@@Servername,'')=@NewName 
Begin
Print 'SQL Server is already named ' +@NewName
End

Rate

Share

Share

Rate