5

#PSTip Retrieve all SQL instance names on local and remote computers

I was looking at way to retrieve the SQL instance names on a remote computer without using SQL SMO. This is essential for me as I don’t always expect to have the SQL SMO DLLs on the computer where I run my scripts.

We can certainly use the Windows Registry to find this information. If the remote system has a SQL service running, the SQL instance information is stored under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL key. We can use the remote Registry service to query for this information from remote systems. So, having remote Registry service enabled on the target systems is essential for the following script to work.

Function Get-SQLInstance {

    param (
        [string]$ComputerName = $env:COMPUTERNAME,
        [string]$InstanceName
    )

    try {
        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName)
        $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )
        $instances = $regkey.GetValueNames()

        if ($InstanceName) {
            if ($instances -contains $InstanceName) {
                return $true
            } else {
                return $false
            }
        } else {
            $instances
        }
    }
    catch {
        Write-Error $_.Exception.Message
        return $false
    }
}

You can use the above function as follows:

PS C:\> Get-SQLInstance
MSSQLSERVER

PS C:\> Get-SQLInstance -ComputerName SQL1
MSSQLSERVER
MIRRORInstance

PS C:\> Get-SQLInstance -ComputerName SQL1 -InstanceName SQLServer
False
Filed in: Columns, Tips and Tricks Tags: , ,

2 Pingbacks/Trackbacks

5 Responses to "#PSTip Retrieve all SQL instance names on local and remote computers"

  1. disqus_3yu60JDjck says:

    (get-itemproperty ‘HKLM:SOFTWAREMicrosoftMicrosoft SQL Server’).InstalledInstances

    • Ravikanth says:

      Why not? :) PowerShell has more than one way to do things and you got a one-liner there. But, using the remote registry gives us the ability to talk to remote computers too. That was the idea!

  2. kiquenet kiquenet says:

    It’s great. Any Async powershell about it?

Leave a Reply

Submit Comment

© 2014 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: