Tuesday, November 27, 2012

PowerShell: Find SQL Instances installed on...

I've been spending a lot of time lately learning and writing PowerShell scripts. I didn't realize how powerful these were until a recent SQL Saturday, where I sat in on a class and was amazed by all the stuff these scripts could do. Most of what I've written is based off of databases we have locally here at my work, but I've come up with a generic script that tells you all the instances of SQL Server running on a specific Windows server. You enter the server name in the $server variable on line 2 and the script executes, makes a WMI connection and returns the services installed that are for SQL Server.
cls
$server = "localhost"    #Do not put an instance here...this is just the root server

$obje = Get-WmiObject -ComputerName $server win32_service | where {($_.name -like "MSSQL$*" -or $_.name -like "MSSQLSERVER" -or $_.name -like "SQL Server (*") -and $_.name -notlike "*helper*" -and $_.name -notlike "*Launcher*"}
cls
Write-Host " "
Write-Host "Server: " $server
Write-Host " "
if ($obje -is [System.Array])
{
 for ($i=0; $i -lt $obje.Length; $i++)
 {
  $j = $i+1
  Write-Host "Instance $j ..."
  Write-Host "Service Name:  "  $obje[$i].Name
  Write-Host "Service Desc:  "  $obje[$i].DisplayName
  Write-Host "Start Mode:    "  $obje[$i].StartMode
  Write-Host "Service State: "  $obje[$i].State
  Write-Host "Service Status:" $obje[$i].Status
  Write-Host " "
 }
}else
{
 Write-Host "Service Name:  "  $obje.Name
 Write-Host "Service Desc:  "  $obje.DisplayName
 Write-Host "Start Mode:    "  $obje.StartMode
 Write-Host "Service State: "  $obje.State
 Write-Host "Service Status:" $obje.Status
}