Sqlserver-dba.com

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.

################################CODE START##########################

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
}

################################CODE FINISH##########################

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

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact