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
}

2 comments:

  1. I'm sure you know this already, but you can get loads of info using 'dir':

    PS SQLSERVER:\> dir SQLSERVER:\SQL\myserver |select name, edition, version

    Name Edition Version
    ---- ------- -------
    myserver\INST01 Standard Edition (64-bit) 10.50.2500
    myserver\INST02 Standard Edition (64-bit) 10.50.2500

    ReplyDelete
  2. Hmm, I didn't get an email about your post....just happened to be looking at the blog and noticed it. Thanks for the comment; i wasn't aware of that...I tried adding "status" to the select and I'm getting "Offline" for all the servers i check, which isn't right. I'm also not seeing the ability to look at StartMode or current state. I do like some of the info in DIR, but I'm using the above script to see 1) what instances are installed, and 2) is the service running. I'll try looking into it more, see if I can get that info somehow with dir SQLSERVER.

    ReplyDelete