Powershell script - Get the SQL Server Instance status with Get-Service

Question:  Do you have any powershell code listing the sql server instances running on a server host. The aim is to report on the service status. The script should cover multiple instances on a server.

Answer: This powershell script can execute on a server, returning all SQL Server instances . The script utilises the Get-Service cmdlet. Customise the script to various search parameters. In the example below, I'm using MSSQL$ , MSSQLSERVER and SQL Server.

These are common service naming conventions for SQL Server.

$server = $env:computername  
$object = Get-service -ComputerName $server  | where {($_.name -like "MSSQL$*" -or $_.name -like "MSSQLSERVER" -or $_.name -like "SQL Server (*") }
if ($object)
{
$instDetails= $object |select Name,Status
$instDetails
}else
{
 Write-Host "0 SQL Server instances discovered"
}

You'll be thinking about whether you'd prefer to run the script directly on the server or execute remotely. Powershel Get-Service can access remote servers -  Powershell script - Get the SQL Server Instance status with Get-Service . So with a few minor tweaks to the script about it is possible to run this script from a local script repository and call remote servers.

You could even go one step further and iterate through a whole list of servers.

Read More

Powershell script to report SQL Database Status

SQL Server - SQL Server Restart with Powershell 

List SQL Server Instances with Powershell remote registry search