How to Retrieve MSSQLSERVER or SQL Server engine running on a set of Servers.

How to Retrieve MSSQLSERVER  or SQL Server engine running on a set of Servers.





Create a Notepad file and add all server name

Save the file in C:\Temp Folder


.\Get-SQLServer.ps1 -ComputerList "Computer.txt"

Save the powershell script with name "Get-SQLServer.ps1" in C:\Temp folder.

#Requires -Version 2.0

param
(
[Parameter(Position=0,ValuefromPipeline=$true)][string][alias("cn")]$computer,
[Parameter(Position=1,ValuefromPipeline=$false)][string]$computerlist
)

If (-not ($computer -or $computerlist))
{
$computers = $Env:COMPUTERNAME
}

If ($computer)
{
$computers = $computer
}

If ($computerlist)
{
$computers = Get-Content $computerlist
}

foreach ($computer in $computers)
{
$object = Get-WmiObject win32_service -ComputerName $computer  | where {($_.name -like "MSSQL$*" -or $_.name -like "MSSQLSERVER" -or $_.name -like "SQL Server (*") -and $_.name -notlike "*helper*" -and $_.name -notlike "*Launcher*"}
if ($object)
{
write-host "####################Retrieving Details for Computer:  $computer" -ForegroundColor Green
$instInfo= $object | select Name,StartMode,State, Status
$instInfo
}else
{
 Write-Host "No instance found!"
 }
}