Clone box/SQL Manager problem

[bubarooni1]

Hi,

I've cloned a production computer that houses 2 instances of MSDE and one instance of SQL 2005 Express on to a test box so that a software vendor can perform some tests on it before upgrading our Great Plains to v10.0 and it's underlying database (one of the MSDE instances) to SQL 2005 Express.

I need both boxes to be on the network at once, but when I change the name of the test box so that i doesn't conflict with the already running production box, sql manager stops working and I can no longer access any of the databases. If I remove the test box from the network by pulling by removing the ethernet cable and rename it to the production server's name, it's fine.

Is there a simple hack to fix this? I really want the box on the network so that the software vendor can get in here and do their thing and get out of my hair.

Thanks In Advance

 

[ptheriault]

You need to change the name of the sql instances. You can't have a sql server of the same name on the network, its like having two pc's with the same IP. Look up sp_dropserver and sp_addserver in BOL.

Here is a proc to rename a server.

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
*/
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'
	UPDATE msdb.sysjobs SET name = @NewName	
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

- Paul
- If at first you don't succeed, find out if the loser gets anything.