How to Find SQL Server Instances using PowerShell?

by

Recently, in our project, we wanted to retrieve all SQL Server instances across your network. I suggested using PowerShell for this. In this tutorial, I will explain to you how to find SQL Server instances using PowerShell, step by step.

Before you start, make sure you have:

  • PowerShell 7+ recommended
  • SQL Server PowerShell module (SqlServer) installed.
  • Administrative privileges on target systems (for remote discovery).
  • Network access to target servers (firewall ports open).

You can install the SQL Server module by running:

Install-Module -Name SqlServer -Scope CurrentUser

Now, let me show you several methods for getting all SQL Server Instances in PowerShell.

Method 1: Using System.Data.Sql Namespace (Network Discovery)

The most straightforward approach uses the built-in .NET framework capabilities to discover SQL Server instances across your network.

# Discover all SQL Server instances on the network
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

This single line command leverages the System.Data.Sql namespace to retrieve a DataTable containing information about all visible SQL Server instances in your network from SQL Server 2000 onwards. The output includes server names, instance names, versions, and whether clustering is enabled.

For better formatting, you can pipe the results:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Format-Table -AutoSize

Pros: Simple, no additional modules required, discovers network-wide instances. Cons: May not discover all instances due to firewall restrictions or the SQL Browser service being disabled.

Check out PowerShell Get-WindowsAutoPilotInfo

Method 2: Using the Get-SqlInstance Cmdlet

The easiest and most reliable way to find all the SQL Server instances on a local or remote computer is using the Get-SqlInstance cmdlet from the SqlServer module.

Here is the PowerShell script to get the SQL Server instances from the local machine.

Import-Module SqlServer
Get-SqlInstance -ServerInstance $env:COMPUTERNAME

This command retrieves all SQL Server instances installed on your local machine.

Each result includes details like:

  • Instance name
  • Version
  • Edition
  • Service name
  • Connection string

If you have the SqlServer PowerShell module installed, the Get-SqlInstance cmdlet provides more detailed information about SQL Server instances.

Here is the modified version of the PowerShell script.

# Install SqlServer module if not already installed
Install-Module -Name SqlServer -Force -AllowClobber

# Get SQL instances on local computer
Get-SqlInstance

# Get specific SQL instance
Get-SqlInstance -ServerInstance "ServerName\InstanceName"

# Get instances from multiple computers
$computers = @("Server1", "Server2", "Server3")
foreach ($computer in $computers) {
    try {
        Get-SqlInstance -ServerInstance $computer
        Write-Host "Successfully connected to $computer" -ForegroundColor Green
    }
    catch {
        Write-Warning "Failed to connect to $computer : $($_.Exception.Message)"
    }
}

The Get-SqlInstance cmdlet gets a SQL Instance object for each instance of SQL Server present on the target computer. This method will also provide the instance information, including version, edition, and configuration details.

If you want to query from multiple servers simultaneously, then you can run the following PowerShell script.

$servers = @("SQL01", "SQL02", "SQL03")
Get-SqlInstance -ServerInstance $servers | Select-Object ComputerName, InstanceName, Version

You can also export the information into a .CSV file using the following command.

Get-SqlInstance -ServerInstance $servers | Export-Csv -Path "C:\SQL_Instance_Report.csv" -NoTypeInformation

Read Find OU of a Computer Using PowerShell

Method 3: Using WMI (Windows Management Instrumentation)

If you don’t have the SQL PowerShell module installed, you can still discover SQL instances using WMI queries.

SQL Server services typically start with MSSQL$ or SQLAgent$. You can use Get-WmiObject (Windows PowerShell) or Get-CimInstance (PowerShell Core) to find them.

Example 1: Local Computer

You can find all the SQL Server instances using the below PowerShell script.

Get-WmiObject -Class Win32_Service | 
Where-Object { $_.Name -like "MSSQL*" -or $_.Name -like "SQLAgent*" } | 
Select-Object Name, DisplayName, State, StartMode

This command lists all SQL-related services on your local computer.

Example 2: Remote Computers

$computers = @("Server01", "Server02")
foreach ($computer in $computers) {
    Get-WmiObject -Class Win32_Service -ComputerName $computer |
    Where-Object { $_.Name -like "MSSQL*" } |
    Select-Object @{Name='ComputerName';Expression={$computer}}, Name, State
}

This approach is lightweight and doesn’t require SQL modules, making it ideal for large-scale network scans.

Check out RPC Server is Unavailable Error in PowerShell

Method 4: Registry-Based Discovery

For local machine discovery, you can query the Windows Registry where SQL Server instances are registered:

# Function to find SQL instances via registry
function Get-SqlInstancesFromRegistry {
    param(
        [string]$ComputerName = $env:COMPUTERNAME
    )
    
    try {
        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName)
        $regKey = $reg.OpenSubKey('SOFTWARE\Microsoft\Microsoft SQL Server')
        
        if ($regKey) {
            $instanceNames = $regKey.GetValue('InstalledInstances')
            
            foreach ($instance in $instanceNames) {
                $instanceInfo = [PSCustomObject]@{
                    ComputerName = $ComputerName
                    InstanceName = $instance
                    ServerInstance = if ($instance -eq 'MSSQLSERVER') { $ComputerName } else { "$ComputerName\$instance" }
                }
                Write-Output $instanceInfo
            }
        }
    }
    catch {
        Write-Error "Failed to query registry on $ComputerName : $($_.Exception.Message)"
    }
    finally {
        if ($reg) { $reg.Close() }
    }
}

# Usage
Get-SqlInstancesFromRegistry
Get-SqlInstancesFromRegistry -ComputerName "RemoteServer"

Read PowerShell Kill Process by Name

Method 5: Service-Based Discovery

You can also discover SQL Server instances by examining SQL Server services:

# Get SQL Server services on local machine
Get-Service -Name "*SQL*" | Where-Object {$_.Name -like "MSSQL*"} | 
Select-Object Name, Status, DisplayName

# Remote service discovery
$computers = @("Server1", "Server2", "Server3")
foreach ($computer in $computers) {
    try {
        $services = Get-Service -ComputerName $computer -Name "*SQL*" | 
                   Where-Object {$_.Name -like "MSSQL*"}
        
        foreach ($service in $services) {
            $instanceName = if ($service.Name -eq "MSSQLSERVER") { 
                "Default" 
            } else { 
                $service.Name -replace "MSSQL\$", "" 
            }
            
            [PSCustomObject]@{
                ComputerName = $computer
                ServiceName = $service.Name
                InstanceName = $instanceName
                Status = $service.Status
                ServerInstance = if ($instanceName -eq "Default") { $computer } else { "$computer\$instanceName" }
            }
        }
    }
    catch {
        Write-Warning "Failed to query services on $computer : $($_.Exception.Message)"
    }
}

This approach works for named instances on standalone servers but requires modification for clustered environments.

In this tutorial, I explained how to find SQL Server instances using PowerShell using several methods, such as: like Get-SqlInstance, WMI queries, and the .NET DataSourceEnumerator.

You may also like:

100 PowerShell cmdlets download free

100 POWERSHELL CMDLETS E-BOOK

FREE Download an eBook that contains 100 PowerShell cmdlets with complete script and examples.