The Original question can be found here:
I need to know if there is a way to modify the script found in the original question to allow for all instances on a computer. I have a situation where I have used an image to duplicate computers and the instances are keeping the original name of the computer.
I can fix it by manually renaming each instance but that takes time and there are 5 instances at least on at least 15 computers. It would be great if there was a way to script it to look at each instance and check the current name of the computer and correct each instance.
Here is an example of the current state of one of the computers:
The first section is the dialog when you start up the management console. Here I have to change the Server Name and the login so that it matches the current computer.
The second section is the place where I can rename the user for the instance in the security section of the instance.
The third section is the Computers Local Security Groups list. As you can see there is a whack of groups for each program that installed an instance and the names are all of the original computer name. I am concerned that this is a problem that will come back to bite me later and I was wondering if I need to make corrections to these group names. Yes the computers' name was originally IMW761 then changed to IMWTechTemp, but not before I had made my Acronis image to duplicate the computer IMW761.
I found a script in the other thread I mentioned, I was hoping that there was a way to modify it and make it do it for all SQL instances on the computer. This is the script:
declare @currentName as nvarchar(128) declare @newName as varchar(max) declare @serverName as varchar(max) declare @serverInstance as varchar(max) select @currentName = @@SERVERNAME select @serverInstance = cast(serverproperty('InstanceName') as varchar(max)) select @serverName = cast(serverproperty('MachineName') as varchar(max)) set @newName = @serverName if (@serverInstance <> '') begin set @newName = @serverName + '\' + @serverInstance end if (@currentName <> @newName) Begin print 'sp_dropserver ''' + @currentName + ''''; print 'go' print 'sp_addserver ''' + @newName + ''',local' print 'go' end else Print 'ALL OK'
Any ideas, suggestions or help is greatly appreciated.