August 10, 2005 at 9:50 am
I have server called SQLPRO12 and has 2 named instances. we planning changeing name from SQLPRO12 to SQLLFG. how to change sql server name ?
any body explain to me
August 10, 2005 at 10:31 am
i found the answer on microsoft site
sp_dropserver <old_servername\instancename>
go
sp_addserver <new_servername\instancename> , local
go
August 10, 2005 at 11:55 am
I've found the following script helpful:
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
August 11, 2005 at 1:29 am
It's "renaming a server" in BOL...
August 11, 2005 at 11:57 am
One thing to remember os that you will have to stop/restart SQL server for this to be totally in effect. Things like @@servername and the servername in the errorlog are set only on startup. Just one little omission from MS ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 14, 2005 at 12:09 am
August 15, 2005 at 8:56 am
Tahir, for v7.0 your are correct.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 15, 2005 at 2:33 pm
To further complicate things, I would like to present the following:
I recently renamed a SQL2000 server. I then went in to change my maintenance plans and many of the extract jobs I have running on the server. I got the following error:
Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.
I then looked up the error and found out that when you rename a server on 2000 you can no longer edit any of your old jobs. Go here to see the full details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
It says you have to rename the server back to the old name and then script out all the old jobs and then delete them. Then you change the name once again and script in the jobs. What a pain. Just remember to script and delete all of your jobs before renaming.
Does anyone else have a way to get around the whole renaming fix? I work at a hospital and I have to schedule the down time and coordinate with too many people to allow me to implement this fix anytime soon.
August 15, 2005 at 3:37 pm
Amazing.
I went into sysjobs and changed the name in the originating_server column and then was able to delete all the old jobs. I have run checkdb and have found no errors. At least I no longer have to jump through hoops to find the time to fix this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply