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 }
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.
Subscribe to:
Post Comments (Atom)
I'm sure you know this already, but you can get loads of info using 'dir':
ReplyDeletePS 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
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