SQL Server PowerShell : How to List SQL Server Services using PowerShell

Written by belle
Posted May 23, 2009 at 10:18 pm

Catch: this lists any service that has “SQL” in it, so can potentially list MySQL services too

ListAll SQL services


#list stopped SQL Server services
$computername = "APHRODITE"
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
   ($_.name -like "SQLAGENT*" -or $_.name -like "*SQL*") `
    }

Another way to do this:

#note ComputerManagement works on SQL Server 2005
#on SQL Server 2008 this has to be ComputerManagement10
Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State

List Stopped SQL services


#list stopped SQL Server services
$computername = "APHRODITE"
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
   ($_.name -like "SQLAGENT*" -or $_.Name -like "*SQL*") `
    -and $_.State -match "Stopped"
    }

List Running SQL services


#list stopped SQL Server services
$computername = "APHRODITE"
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
   ($_.name -like "SQLAGENT*" -or $_.Name -like "*SQL*") `
    -and $_.State -match "Stopped"
    }