How to get SQL Server Version with Powershell

SQL Server Version with Powershell and documenting SQL Server versions, is a necessary timesaving methods . For instance , I'm upgrading loads of sql servers at the moment and the following script , gives me a quick way of getting a summary of versions. Place all the relevant servers into a file , such as "C:\servers.txt" , just place them on separate lines.

An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Keep in mind, you will need to have  Allow Remote Connections enabled

To run , copy and paste straight into a Powershell cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension.

Method 1 : Format-table output

foreach ($svr in get-content "C:\Servers.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

Method 2 : Export-CSV

$dt = new-object "System.Data.DataTable"
foreach ($svr in get-content "C:\Servers.txt"){
    
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray |sort Column1| export-csv -notype C:\sql_server_versions.csv

As well as SQL Server Version with Powershell  , check Powershell for SMO

Read More

How to Check Powershell version

SQL Server – Find SQL Server tcp port with Powershell 

Powershell - run script on all sql servers

Number of physical processors with Powershell – number of cores New