How to get SQL Server services' status using PowerShell

Introduction

The below PowerShell script will retrieve the status of all the SQL Server services for each instance.

Running the Sample

Step 1: Open command prompt in alleviated mode and navigate to the path where PSSQLServer ServicesStatus.ps1 is located.
Step 2: Create a text file "Servers.txt" and store all the server names one after the other in a separate line whose services' status need to be retrieved. Save the text file.
Step 3: Update the location of the source text file created in Step 2 in the PowerShell script and run it.

Using the Code

Below is the code snippet to get SQL Server services' status using PowerShell.

# Getting the list of all SQL Servers from the below location.
$servers = @(get-content "D:\Servers.txt")
 
# Defining columns of the output table.
$SrvcName = @{label="Service Name" ;alignment="left" ;Expression={$_.Name};};
$SrvcMode = @{label="Start Mode"   ;alignment="left" ;Expression={$_.StartMode};};
$SrvcState = @{label="State"        ;alignment="left" ;Expression={$_.State};};
# With the help of Get-WmiObject cmdlet getting the status information of
# SQL Server services and SQL Agent services. Looping through the list of servers to get
# service name, mode, state information.
 
foreach($server in $servers)
{
 
    $srvc = Get-WmiObject `
            -query "SELECT *
                    FROM win32_service
                    WHERE    name LIKE '%MSSQL%'
                          OR name LIKE '%SQLAgent%'"                                         `
             -computername $server `
            | Sort-Object -property name;
  
    Write-Output ("Server: {0}" -f $server);
    Write-Output $srvc | Format-Table $SrvcName, $SrvcMode, $SrvcState;
}

# Getting the list of all SQL Servers from the below location. 
$servers = @(get-content "D:\Servers.txt") 
  
# Defining columns of the output table. 
$SrvcName = @{label="Service Name" ;alignment="left" ;Expression={$_.Name};}; 
$SrvcMode = @{label="Start Mode"   ;alignment="left" ;Expression={$_.StartMode};}; 
$SrvcState = @{label="State"        ;alignment="left" ;Expression={$_.State};}; 
# With the help of Get-WmiObject cmdlet getting the status information of 
# SQL Server services and SQL Agent services. Looping through the list of servers to get 
# service name, mode, state information. 
  
foreach($server in $servers) 
{ 
  
    $srvc = Get-WmiObject ` 
            -query "SELECT * 
                    FROM win32_service 
                    WHERE    name LIKE '%MSSQL%' 
                          OR name LIKE '%SQLAgent%'"                                         ` 
             -computername $server ` 
            | Sort-Object -property name; 
   
    Write-Output ("Server: {0}" -f $server); 
    Write-Output $srvc | Format-Table $SrvcName, $SrvcMode, $SrvcState; 
}

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.