Find Sql Servers Service pack information across your Enterprise using power shell in one click

Imagine if your shop has more than 100+ SQL servers and you want to create a report to show the patching level of each SQL Server, How did you do that in fastest way.

Login into each server and run query will consume time and leads some mistakes.

 

Power-shell make easy for you.

  • Create a text file .
  • Add SQL Server instance names to the text file and save.
  • Change the path in the following script  C:\hari1\mysqlservers.txt to your text file name and execute the power Shell script.
    Output will be saved in CSV and you can share this report to your management team.
    output
"SqlServerName","Version","SP"
"HARIERE-PC\SQLSERVER2014","12.0.2254.0","RTM"
"HARIERE-PC\SQLSRV2012","11.0.2218.0","RTM"
# Author Hari Ere
# SqlServerversionspcheck.ps1
#create a text file and add sqlserver instanace names in the file
# usage: ./SqlServerversionspcheck.ps1
# Check Sqlserver versions and Service packs across the network
#define data table as public
 $dt = new-object System.Data.DataTable 
foreach ($svr in get-content "C:\hari1\mysqlservers.txt")
{
 #Establish trusted Sql Connection
 $con = "server=$svr;database=master;Integrated Security=sspi"
 #query to get sqlservername,version and sp info
 $cmd = "SELECT @@servername as SqlServerName,SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
 #build data adapter
 $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
 #fill the data set
 $da.fill($dt) | out-null
 
 #for debug purpose uncomment the follwoing two statements.
 ##$svr
 ##$dt | Format-Table -autosize
}
$dt | export-csv "C:\hari1\mysqlserveroutput.csv" -noType

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s