Getting MSSQL Instance Version in Powershell

Question

From running the following I can see that there are 4 MSSQL instances running on this machine:

$Hostname = (Get-WmiObject Win32_OperatingSystem).CSName

$GetSQLInstance = Get-Service -ComputerName $Hostname | Where-Object { ($_.Name -eq 'mssqlserver' -or $_.Name -like 'mssql$*') -and $_.DisplayName -like 'SQL Server*'}

$GetSQLInstance

Status   Name               DisplayName                           
------   ----               -----------                           
Running  MSSQL$INSTANCE01   SQL Server (INSTANCE01)               
Running  MSSQL$INSTANCE02   SQL Server (INSTANCE02)               
Running  MSSQL$INSTANCE03   SQL Server (INSTANCE03)               
Running  MSSQL$INSTANCE04   SQL Server (INSTANCE04)

I am trying to ascertain the MSSQL version of these instances, what would be the best way to do so? I thought to use Get-SqlInstance, but it doesn't seem to be installed on this machine:

Get-SqlInstance

Get-SqlInstance : The term 'Get-SqlInstance' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the 
spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Get-SqlInstance
+ ~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Get-SqlInstance:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Why not try `Invoke-Sqlcmd -Query "SELECT @@VERSION"`. Check [this](https://stackoverflow.com/questions/7587077/how-do-i-check-for-the-sql-server-version-using-powershell) link. — Vivek Kumar Singh, Sep 13 '22 at 11:27
The [ServiceController class](https://learn.microsoft.com/en-us/dotnet/api/system.serviceprocess.servicecontroller?view=dotnet-plat-ext-6.0) (which is what [`Get-Service`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-service?view=powershell-7.2) uses as it's output) doesn't have awareness of the version of the software, so you'll likely need to *connect* to the instance to get this information. — Thom A, Sep 13 '22 at 11:29
@VivekKumarSingh Unfortunately, `Invoke-Sqlcmd` cannot be used due to the `SQLPS` module not being loaded, and I do not have the permissions to run the script to enable it. @Larnu How do I connect to the instance to extract this information? — saturnSam, Sep 13 '22 at 12:16
What about the other solutions in the link which I have posted. Did any of them work for you. — Vivek Kumar Singh, Sep 13 '22 at 13:52

score 1 · Accepted Answer · answered Dec 06 '22 at 20:28

I use a modified version of this solution here. This is nice since you can run this remotely from your machine and it writes the info to a CSV.

Invoke-Command -ComputerName (Get-Content 'C:\Powershell\machines.txt') -ScriptBlock {
  $SQLInstance = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
  foreach ($Install in $SQLInstance)
  {
    $Path = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$Install
    return (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Path\Setup")
  }
} | Select-Object -Property PSComputerName,Edition,PatchLevel | Export-CSV -Path 'C:\Powershell\output.csv' -NoTypeInformation

Getting MSSQL Instance Version in Powershell

1 Answers1

Linked