Get the version and patch level on remote MS SQL Servers
Get the version and patch level on remote MS SQL Servers

With this script, you will be able to get a status on your Microsoft SQL Server version and patch level. This script uses a csv table I have created and that contains the version and patch level for each SQL build version. The csv file is available here and have to copied in the current script location : sql_version_build

The versions listed in the csv file are the following:

  • SQL Server 2016
  • SQL Server 2014
  • SQL Server 2012
  • SQL Server 2008R2
  • SQL Server 2008
  • SQL Server 2005
$host_array = "SQLSRV01",
"SQLSRV02",
"SQLSRV03"

function Get_SQL_info($computername){
	$array_tmp = @()
	$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computername )
	$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL",$true)
	$instancenames = $regkey.GetValueNames()
	$instancenames | % {
		$instance = $_
		$regvalue = $regKey.GetValue($instance)
		$regKeyVersion = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$regvalue\\MSSQLServer\\CurrentVersion",$true)
		$CurrentVersion = $regKeyVersion.GetValue("CurrentVersion")
		$instance_sql_info = $sql_build_info -match $CurrentVersion
		$sql_build = $instance_sql_info.build
		$sql_version = $instance_sql_info.version
		$sql_sp = $instance_sql_info.sp
		$sql_update = $instance_sql_info.update
		$Properties = @{server=$computername;
				instance=$instance;
				sql_build=$sql_build;
				sql_version=$sql_version;
				sql_sp=$sql_sp;
				sql_update=$sql_update}
		$Newobject = New-Object PSObject -Property $Properties
		$array_tmp += $newobject
	}
	return $array_tmp
}

$sql_build_info = import-csv -Delimiter ";" sql_version_build.csv

$sql_array = @()
$host_array | % {
	$sqlservername = $_
	$sql_array += Get_SQL_info($sqlservername)
}
$sql_array

The script output is an array with the following properties :

  • server
  • instance
  • sql_build
  • sql_version
  • sql_sp
  • sql_update

<>
Get the version and patch level on remote MS SQL Servers

Leave a Reply

Your email address will not be published. Required fields are marked *