Check current patch levels for all SQL Servers in enviornment


Usually when a new database server gets built, it has the latest operating system service pack, and SQL Server is also installed with the latest service pack. Unfortunately, when there are many servers and instances, it’s easy to forget to patch them all, both at the OS level and at the SQL level. Also, it is hard to determine if all of them have been patched to the latest version. And when there are issues, the OS and SQL build numbers are needed for the provider to troubleshoot.


Below you’ll find a PowerShell script that checks the OS version details and the SQL Server build, which then can be compared against the latest build to see if it needs to be patched or not. To speed up the process, it queries the servers in parallel, but constructs a single query to update the Inventory database table in a single connection.

In the script there are some variables you need to modify:

  • $server: the name of the server where you have stored your Inventory table
  • $database: the database where you have stored your Inventory table
  • $query: modify this depending on the names of your columns
  • $Maxthreads: query 20 servers at a time; if you’re tight on resources you can lower this number, but the results may take longer to appear

The way it works is as follows:

  1. Query table “Inventory” in database “Inventory”, which contains one entry for each instance with the details: ServerName, InstanceName, Version, BuildNumber, ServicePack, OSVersion, OSBuildNumber, OSServicePack and DatetimeLastUpdate.
  2. In parallel and for each server, query the SQL information (BuildNumber, ServicePack) and OS information (OSVersion, OSBuildNumber, OSServicePack), and construct a SQL update statement.
  3. When all threads have finished, we have a list of SQL update statements to run in our Inventory database. Note that if you don’t want to run these commands immediately, you need to comment the last line of the script.
  4. Finally, we can run a query to identify if we have the latest SQL Server build numbers, and a similar approach can be done to identify if we have the latest OS build number.

Script to create the inventory table and insert test records

The following script creates our Inventory table where we will list each server we need to connect to.  The script also adds a few sample records for testing.  You would need to insert the names of your servers to test in your environment.

CREATE TABLE [Inventory] (
   [ServerName] VARCHAR(128),
   [InstanceName] VARCHAR(128),
   [Version] VARCHAR(20),
   [BuildNumber] VARCHAR(20), 
   [ServicePack] VARCHAR(20), 
   [OSVersion] VARCHAR(20), 
   [OSBuildNumber] VARCHAR(20), 
   [OSServicePack] VARCHAR(20), 
   [DatetimeLastUpdate] DATETIME) 
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('nonexist', 'nonexist', 'Microsoft SQL Server 2000')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WIND01', 'WIND01', 'Microsoft SQL Server 2014')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINT01', 'WINT01\TEST01', 'Microsoft SQL Server 2008')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD04', 'Microsoft SQL Server 2016')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD05', 'Microsoft SQL Server 2017')

PowerShell Script

Here is the script.  As mentioned, you will need to adjust the first couple of variables to match your environment.  Also, this script uses Windows authentication to connect to the servers.

$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "."
$database = "Inventory"
$query = @"
SELECT [ServerName], [InstanceName]
  FROM [Inventory]
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of instances
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
# environment setup
$output = ""
$errors = ""
$Jobs = @()
$ISS = []::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$script = {
   Param ([string]$serverName, [string]$instanceName)
   $ErrorActionPreference = "Stop"
   $query = "
      SET NOCOUNT ON --No 'rows returned' message
      SELECT SERVERPROPERTY('productversion') [ProductVersion], SERVERPROPERTY('productlevel') [ProductLevel]"
   try {
      $command = "UPDATE [Inventory] SET "
      # Get SQL info
      $data = sqlcmd -S $instanceName -Q $query -h-1 -W -b -r1 -s"\t" 2>&1 #no header, no whitespaces, break on error, errors to output, tab separator, stderr to stdout
      if (!$data -or $data.length -eq 0) { return $instanceName + ": Unable to query server" | Out-String } #server may not exist anymore
      $data = $data.split('\t')
      $command += "[BuildNumber] = '"+$data[0]+"'"
      # Get OS info
      $data = Get-WmiObject -class Win32_OperatingSystem -computername $serverName | select Version, BuildNumber, ServicePackMajorVersion
      $command += ", [OSVersion] = '"+$data."Version"+"', [OSBuildNumber] = '"+$data."BuildNumber"+"', [OSServicePack] = '"+$data."ServicePackMajorVersion"+"'"
      "" #indicate there was no error
      $command+", [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = '"+$instanceName+"';"
   catch {
      return $instanceName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String
function CreateThread() {
   param ([string]$serverName, [string]$instanceName, [ref]$Jobs)
   $PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
   $PowershellThread.AddArgument($serverName) | out-null
   $PowershellThread.AddArgument($instanceName) | out-null
   $PowershellThread.RunspacePool = $RunspacePool
   $Handle = $PowershellThread.BeginInvoke()
   $Job = "" | select Handle, Thread, object
   $Job.Handle = $Handle; $Job.Thread = $PowershellThread
   $Jobs.value += $Job
$ResultTimer = Get-Date #start time
#start processing first task for each instance
for ($i=0; $i -lt $objects.length; $i++) {
   CreateThread $objects[$i].ServerName $objects[$i].InstanceName ([ref]$Jobs)
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
   #update completed jobs, get errors and result, and dispose them
   foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
      $results = $Job.Thread.EndInvoke($Job.Handle)
      if ($results[0] -and $results[0] -ne "") {
         $errors += $results[0]
      for ($i = 1; $i -lt $results.count; $i++) {
         $output += $results[$i]+"`r`n"
      #end thread
      $Job.Thread = $Null
      $Job.Handle = $Null
   #show progress
   $inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
   Write-Progress `
      -Activity "Gathering data" `
      -PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
      -Status "$inProgress pending"
   #exit on timeout
   $currentTime = Get-Date
   if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
      Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
   Start-Sleep -Milliseconds $SleepTimer
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $output

The output generated by the script is similar to the following. This is stored in the $output variable and the last line executes these commands to update the Inventory table.  If you don’t want to update the data you can comment out the last line and add $output as the last line to see the what is stored in the $output variable.

PS C:\Users\PabloEcheverria\Desktop> .\UpdateInventory.ps1
nonexist: 1 HResult 0x102, Level 16, State 1
UPDATE [Inventory] SET [BuildNumber] = '12.0.4100.1', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'DEV01';
UPDATE [Inventory] SET [BuildNumber] = '10.0.5890.0', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'TEST01';
UPDATE [Inventory] SET [BuildNumber] = '13.0.4466.4', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD04';
UPDATE [Inventory] SET [BuildNumber] = '14.0.3022.28', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD05';

After the information has been updated in the Inventory database, you can run the following query to determine if you have the latest SQL Server versions. Note that you need to update the “Build” column values to match the latest version at the moment you’re running the query, I personally use this link to get the latest build numbers for each version of SQL Server.

USE [Inventory]
CREATE TABLE #Info ([Version] VARCHAR(128), [Build] VARCHAR(20))
INSERT INTO #Info VALUES ('Microsoft SQL Server 2000',    '8.00.2305')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2005',    '9.00.5324.00')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008',    '10.0.6556.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008 R2', '10.50.6560.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2012',    '11.0.7462.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2014',    '12.0.5579.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2016',    '13.0.4474.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2017',    '14.0.3023.8')
SELECT [msl].[ServerName], [msl].[InstanceName], [DatetimeLastUpdate], [msl].[BuildNumber], [i].[Build] [LatestBuild], CASE WHEN ISNULL([msl].[BuildNumber], '') <> [i].[Build] THEN 'NO' ELSE 'YES' END [Latest?] 
FROM [Inventory] [msl]
LEFT JOIN #Info [i] ON [i].[Version] = [msl].[Version]
ORDER BY [latest?], [msl].[InstanceName]

And here is the output from running the above query:

ServerName InstanceName DatetimeLastUpdate BuildNumber LatestBuild Latest?
nonexist nonexist 8.00.2305 NO
WIND01 WIND01 3/14/2018 18:28 12.0.4100.1 12.0.5571.0 NO
WINT01 WINT01\TEST01 3/14/2018 18:28 10.0.5890.0 10.0.6556.0 NO
WINP04 WINP04\PROD04 3/14/2018 18:28 13.0.4466.4 13.0.4466.4 YES
WINP04 WINP04\PROD05 3/14/2018 18:28 14.0.3022.28 14.0.3022.28 YES

Note that this script can be easily converted into a SQL Server job to run on a scheduled basis to check if all servers are patched to the latest version and notify you about the ones that aren’t or it can be run manually when you know there have been updates or you want to provide the latest information accurately. Also, a similar approach can be followed to identify if you have the latest OS patch level.

Next Steps
  • Start by creating your inventory database, and then run the process to update the information. Then check if you have the latest version for them.
  • You can learn more about SERVERPROPERTY in SQL 2016 by following this link.
  • You can learn more about PowerShell Get-WmiObject function by following this link.
  • You can automate the part to get the latest build numbers by following this link.

Last Update:

About the author

I’ve worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I’ve been suited to implement new processes and optimize existing ones.

View all my tips

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

This site uses Akismet to reduce spam. Learn how your comment data is processed.