How-to: Instances

A PowerShell script to enumerate the SQL instances across multiple servers.

##
# Read in a list of Server Names from a file. 
# For each server, query the services to find the SQL Server instance names.
# List all the SQL instances found to a log file.

##

$servers = get-content "C:\batch\servers.txt"
$logfile = "C:\batch\sql-instances.txt"
$logerrs = "C:\batch\sql-failures.txt"

Echo "Server, Instance" >> $logfile

ForEach ($server in $servers) { 
   $instances = Get-CIMinstance -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}

   if (!$?) {
      Echo "$server - No SQL instance found" >> $logerrs
      Echo "$server - No SQL instance found"
   }
   Else {
      ForEach ($instance in $instances) {
         if (($instance.name -eq "MSSQLSERVER") -or ($instance.name -like "MSSQL$*")) {
            Echo "$server, $($instance.name)" >> $logfile
            Echo "$server, $($instance.name)"
         }
      }  
   }
}
# SS64.com/sql/syntax-instances.html

“The majority is never right. Never, I tell you! That’s one of these lies in society that no free and intelligent man can help rebelling against. Who are the people that make up the biggest proportion of the population—the intelligent ones or the fools? I think we can agree it’s the fools, no matter where you go in this world, it’s the fools that form the overwhelming majority” ~ Henrik Ibsen, Dr. Stockmann, in An Enemy of the People, act 4 (1882)

Related commands

SQLCMD -L ~ List all SQL instances