Follow up to: SQL Server 2008 R2: Problems after computer name change

Question

The Original question can be found here:

SQL Server 2008 R2: Problems after computer name change

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.

Answer 1

You could resort to Powershell for that.

I have tested this on a server with multiple instances and this should work:

foreach ($instance in (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances)
{
    if ($instance -eq  "MSSQLSERVER")
    {
    $serverinstance = "."
    }
    else
    {
    $serverinstance = ".\" + $instance  
    }

    Invoke-Sqlcmd -Query "select @@version;" -ServerInstance $serverinstance
}

I replaced your query with something safer on my environment though.

You could use -InputFile to source an sql file