
Applies to SQL Server 2019, 2022 and 2025. It also reports older versions it can reach, as out of support where they are. The script runs in Windows PowerShell 5.1 or PowerShell 7.
Windows Update doesn’t patch SQL Server unless you’ve allowed it to update other Microsoft products, and on database servers most people haven’t. So SQL Server needs its own report. This one compares every instance with the build you’ve tested and approved for its version, and says which are behind, which are out of support and which availability groups are running mixed builds.
What the report shows
- Each instance with its version, edition, CU and KB, from
SERVERPROPERTY:ProductVersion,ProductUpdateLevelandProductUpdateReference. - When the current build was installed, from
ResourceLastUpdateDateTime, and the days since. - The approved build for that version, from a table you update after each patch cycle, and the status: OK, BELOW TARGET, NO TARGET SET, OUT OF SUPPORT or UNKNOWN VERSION.
- Availability groups whose replicas are on different builds. Fine for a few hours during a rolling upgrade, never for weeks.
- Exceptions, shared with the Windows report: an instance deliberately left behind shows with its reason, owner and expiry date.
- The monthly figure: instances at or above target, by version.
Why it matters
Cumulative updates arrive every couple of months while a version is in mainstream support, and security fixes come as GDRs. Without a report, the honest answer to “are we patched?” is usually “most of them, probably”.
The target build is the point of the report. It turns “latest” (which changes every few weeks and isn’t always wise on day one) into “what we’ve tested and agreed”. One CU behind it is a scheduling issue. Four behind is a decision someone made, and it should be written down as an exception.
Collect daily so the report always shows the current build. Report monthly, after Patch Tuesday and after each new CU has been through testing. Risk reads the out-of-support list: an unsupported SQL Server is a risk they’ve accepted whether or not anyone told them.
The script
For each instance, one query reads the version properties and the names of any availability groups the instance is part of. It stores one row per instance per run.
# Invoke-SqlPatchAudit.ps1
# SQL Server patch level for every instance in the list: version, build, CU
# and KB, when the current build was installed, and which availability groups
# the instance is in. Schedule it daily; report on it monthly.
#
# Set the approved build for each version in dbo.SqlBuildTarget after every
# patch cycle; the report compares each instance with it.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\SqlPatchReport.sql
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$PatchQuery = @"
SET NOCOUNT ON;
SELECT
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) AS InstanceName,
CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS SQLEdition,
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) AS SQLVersion,
CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) AS MajorVersion,
CAST(SERVERPROPERTY('ProductBuild') AS INT) AS Build,
CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(32)) AS ProductLevel,
CAST(SERVERPROPERTY('ProductUpdateLevel') AS NVARCHAR(32)) AS ProductUpdateLevel,
CAST(SERVERPROPERTY('ProductUpdateReference') AS NVARCHAR(32)) AS ProductUpdateReference,
CAST(SERVERPROPERTY('ResourceLastUpdateDateTime') AS DATETIME2(0)) AS LastPatched,
(SELECT STRING_AGG(CAST(ag.name AS NVARCHAR(MAX)), N', ')
FROM sys.availability_groups AS ag) AS AvailabilityGroups;
"@
$Servers = Get-ServerList -Path $ServerListPath
$CaptureDate = Get-Date
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.SqlPatchAudit'
foreach ($Server in $Servers) {
try {
$Source = Open-SqlConnection -Server $Server
try {
$Data = Invoke-SqlQuery $Source $PatchQuery
}
finally {
$Source.Close()
}
$null = Add-DataTableRows $Repo 'dbo.SqlPatchAudit' $Data $Server $CaptureDate
Write-CollectionLog $Repo -Collection 'SqlPatch' -ServerName $Server -CaptureDate $CaptureDate `
-Status 'SUCCESS' -Rows 1
$R = $Data.Rows[0]
Write-Host "$Server : $($R.SQLVersion) $($R.ProductUpdateLevel) $($R.ProductUpdateReference)"
}
catch {
Write-CollectionLog $Repo -Collection 'SqlPatch' -ServerName $Server -CaptureDate $CaptureDate `
-Status 'FAILED' -ErrorMessage $_.Exception.Message
Write-Warning "$Server : $($_.Exception.Message)"
}
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
# . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.
function Get-ServerList {
# One server per line. Blank lines and anything after # are ignored,
# and duplicates (in any case) are removed.
param([Parameter(Mandatory)][string]$Path)
Get-Content -Path $Path |
ForEach-Object { ($_ -split '#')[0].Trim() } |
Where-Object { $_ } |
Sort-Object -Unique
}
function Get-ComputerName {
# SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
param([Parameter(Mandatory)][string]$Server)
($Server -split '\\')[0]
}
function Open-SqlConnection {
param(
[Parameter(Mandatory)][string]$Server,
[string]$Database = 'master'
)
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
"TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
$Conn.Open()
$Conn
}
function Invoke-SqlQuery {
# Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Table = New-Object System.Data.DataTable
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
, $Table
}
function Invoke-SqlNonQuery {
param($Connection, [string]$Sql)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
$null = $Cmd.ExecuteNonQuery()
}
function Add-InventoryRow {
# Inserts one row from an ordered hashtable of column = value.
# Every value goes in as a parameter, never pasted into the SQL text.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)][System.Collections.IDictionary]$Row
)
$Columns = @($Row.Keys)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
(($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'
foreach ($Column in $Columns) {
$Value = $Row[$Column]
if ($null -eq $Value -or $Value -is [System.DBNull] -or
($Value -is [string] -and $Value.Trim() -eq '')) {
$Value = [System.DBNull]::Value
}
$null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
}
$null = $Cmd.ExecuteNonQuery()
}
function Invoke-SqlQuerySet {
# Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 600
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Set = New-Object System.Data.DataSet
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Set)
$Set
}
function Assert-InventoryTable {
# Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
param($Connection, [Parameter(Mandatory)][string[]]$Table)
foreach ($Name in $Table) {
$Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
if ($Id -is [System.DBNull]) {
throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
}
}
}
function Add-DataTableRows {
# Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)]$Data,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate
)
foreach ($Source in $Data.Rows) {
$Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
foreach ($Column in $Data.Columns.ColumnName) {
$Row[$Column] = $Source[$Column]
}
Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
}
$Data.Rows.Count
}
function Write-CollectionLog {
# One row per server per collection per run, so a server that failed is never just missing.
param(
$Connection,
[Parameter(Mandatory)][string]$Collection,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate,
[Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
[int]$Rows = 0,
[string]$ErrorMessage
)
Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
CaptureDate = $CaptureDate
Collection = $Collection
ServerName = $ServerName
CollectionStatus = $Status
RowsCollected = $Rows
ErrorMessage = $ErrorMessage
})
}
The tables
The script stops with a clear message if these are missing. Run this once before the first collection.
-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
CREATE DATABASE DBA;
GO
USE DBA;
GO
-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
CREATE TABLE dbo.CollectionLog
(
CollectionLogID BIGINT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
CaptureDate DATETIME2(0) NOT NULL,
Collection VARCHAR(30) NOT NULL, -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
ServerName NVARCHAR(128) NOT NULL,
CollectionStatus VARCHAR(20) NOT NULL, -- SUCCESS, PARTIAL, FAILED
RowsCollected INT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
CREATE CLUSTERED INDEX CX_CollectionLog
ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO
-- Maintained by hand: servers or instances deliberately left behind, and until when
IF OBJECT_ID(N'dbo.PatchException', N'U') IS NULL
CREATE TABLE dbo.PatchException
(
PatchExceptionID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_PatchException PRIMARY KEY,
ServerName NVARCHAR(128) NOT NULL, -- computer name for Windows, instance name for SQL
Scope VARCHAR(10) NOT NULL
CONSTRAINT CK_PatchException_Scope CHECK (Scope IN ('Windows', 'SQL')),
Reason NVARCHAR(400) NOT NULL,
RiskOwner NVARCHAR(128) NOT NULL,
ExpiresOn DATE NOT NULL,
CreatedOn DATE NOT NULL CONSTRAINT DF_PatchException_CreatedOn DEFAULT (CAST(SYSDATETIME() AS DATE))
);
GO
IF OBJECT_ID(N'dbo.SqlPatchAudit', N'U') IS NULL
BEGIN
CREATE TABLE dbo.SqlPatchAudit
(
CaptureDate DATETIME2(0) NOT NULL,
ServerName NVARCHAR(128) NOT NULL, -- as listed in Servers.txt
InstanceName NVARCHAR(128) NULL,
SQLEdition NVARCHAR(128) NULL,
SQLVersion NVARCHAR(32) NULL, -- e.g. 16.0.4135.4
MajorVersion INT NULL, -- 15 = 2019, 16 = 2022, 17 = 2025
Build INT NULL, -- the third part of SQLVersion
ProductLevel NVARCHAR(32) NULL,
ProductUpdateLevel NVARCHAR(32) NULL, -- CU number, NULL on RTM or GDR-only builds
ProductUpdateReference NVARCHAR(32) NULL, -- KB number
LastPatched DATETIME2(0) NULL, -- when the current build was installed
AvailabilityGroups NVARCHAR(1000) NULL
);
CREATE CLUSTERED INDEX CX_SqlPatchAudit
ON dbo.SqlPatchAudit (ServerName, CaptureDate);
END;
GO
-- Maintained by hand after each patch cycle: the approved build for each version
IF OBJECT_ID(N'dbo.SqlBuildTarget', N'U') IS NULL
CREATE TABLE dbo.SqlBuildTarget
(
MajorVersion INT NOT NULL CONSTRAINT PK_SqlBuildTarget PRIMARY KEY,
ProductName NVARCHAR(40) NOT NULL,
TargetBuild INT NULL, -- third part of ProductVersion of the approved update
TargetName NVARCHAR(60) NULL, -- e.g. 'CU20 + GDR', for people reading the report
ExtendedSupportEnds DATE NULL
);
GO
IF NOT EXISTS (SELECT 1 FROM dbo.SqlBuildTarget)
INSERT INTO dbo.SqlBuildTarget (MajorVersion, ProductName, ExtendedSupportEnds)
VALUES (12, N'SQL Server 2014', '2024-07-09'),
(13, N'SQL Server 2016', '2026-07-14'),
(14, N'SQL Server 2017', '2027-10-12'),
(15, N'SQL Server 2019', '2030-01-08'),
(16, N'SQL Server 2022', '2033-01-11'),
(17, N'SQL Server 2025', NULL); -- add the date from Microsoft's lifecycle page
GO
The report queries
Run these in the DBA database. Before the first run, set TargetBuild for each version you have: the third part of the version number of the update you’ve approved, for example 4135 for 16.0.4135.4. Microsoft’s latest updates for SQL Server page lists them.
/*
SqlPatchReport.sql - run in the DBA database.
Monthly, after Patch Tuesday and after each new CU has been tested.
Before the first run, set the approved build for each version, for example:
UPDATE dbo.SqlBuildTarget
SET TargetBuild = 4135, TargetName = N'CU14 + GDR' -- third part of ProductVersion
WHERE MajorVersion = 16;
Take the build numbers from Microsoft's "latest updates for SQL Server" page
once the update has passed your testing.
*/
USE DBA;
DECLARE @Today DATE = CAST(SYSDATETIME() AS DATE);
/* 1. Every instance against its target, worst first ----------------------- */
WITH Latest AS
(
SELECT P.*,
ROW_NUMBER() OVER (PARTITION BY P.ServerName ORDER BY P.CaptureDate DESC) AS RN
FROM dbo.SqlPatchAudit AS P
),
Report AS
(
SELECT
L.ServerName,
L.InstanceName,
T.ProductName,
L.SQLEdition,
L.SQLVersion,
L.ProductUpdateLevel,
L.ProductUpdateReference,
L.LastPatched,
DATEDIFF(DAY, L.LastPatched, L.CaptureDate) AS DaysSincePatched,
T.TargetBuild,
T.TargetName,
T.ExtendedSupportEnds,
L.AvailabilityGroups,
E.Reason AS ExceptionReason,
E.RiskOwner AS ExceptionOwner,
E.ExpiresOn AS ExceptionExpires,
CASE
WHEN L.CaptureDate < DATEADD(DAY, -2, SYSDATETIME()) THEN 'NO RECENT DATA'
WHEN T.MajorVersion IS NULL THEN 'UNKNOWN VERSION'
WHEN T.ExtendedSupportEnds < @Today THEN 'OUT OF SUPPORT'
WHEN T.TargetBuild IS NULL THEN 'NO TARGET SET'
WHEN L.Build < T.TargetBuild THEN 'BELOW TARGET'
ELSE 'OK'
END AS RawStatus
FROM Latest AS L
LEFT JOIN dbo.SqlBuildTarget AS T
ON T.MajorVersion = L.MajorVersion
OUTER APPLY
(
SELECT TOP (1) X.Reason, X.RiskOwner, X.ExpiresOn
FROM dbo.PatchException AS X
WHERE X.ServerName IN (L.ServerName, L.InstanceName) AND X.Scope = 'SQL' AND X.ExpiresOn >= @Today
ORDER BY X.ExpiresOn DESC
) AS E
WHERE L.RN = 1
)
SELECT
ServerName, InstanceName, ProductName, SQLEdition, SQLVersion,
ProductUpdateLevel, ProductUpdateReference, LastPatched, DaysSincePatched,
TargetName, ExtendedSupportEnds, AvailabilityGroups,
CASE WHEN RawStatus <> 'OK' AND ExceptionReason IS NOT NULL
THEN 'EXCEPTION until ' + CONVERT(CHAR(10), ExceptionExpires, 23)
ELSE RawStatus END AS PatchStatus,
ExceptionReason, ExceptionOwner
FROM Report
ORDER BY CASE RawStatus WHEN 'OUT OF SUPPORT' THEN 0 WHEN 'BELOW TARGET' THEN 1 WHEN 'UNKNOWN VERSION' THEN 2
WHEN 'NO TARGET SET' THEN 3 WHEN 'NO RECENT DATA' THEN 4 ELSE 5 END,
DaysSincePatched DESC;
/* 2. Availability groups whose replicas are on different builds ----------- */
WITH Latest AS
(
SELECT P.*,
ROW_NUMBER() OVER (PARTITION BY P.ServerName ORDER BY P.CaptureDate DESC) AS RN
FROM dbo.SqlPatchAudit AS P
),
AgReplica AS
(
SELECT LTRIM(RTRIM(S.value)) AS AvailabilityGroup, L.InstanceName, L.SQLVersion, L.LastPatched
FROM Latest AS L
CROSS APPLY STRING_SPLIT(L.AvailabilityGroups, N',') AS S
WHERE L.RN = 1
)
SELECT A.AvailabilityGroup, A.InstanceName, A.SQLVersion, A.LastPatched
FROM AgReplica AS A
WHERE A.AvailabilityGroup IN
(
SELECT AvailabilityGroup
FROM AgReplica
GROUP BY AvailabilityGroup
HAVING COUNT(DISTINCT SQLVersion) > 1
)
ORDER BY A.AvailabilityGroup, A.SQLVersion;
/* 3. Estate summary by version: the monthly figure ------------------------ */
WITH Latest AS
(
SELECT P.*,
ROW_NUMBER() OVER (PARTITION BY P.ServerName ORDER BY P.CaptureDate DESC) AS RN
FROM dbo.SqlPatchAudit AS P
)
SELECT
COALESCE(T.ProductName, 'Unknown') AS ProductName,
COUNT(*) AS Instances,
SUM(CASE WHEN L.Build >= T.TargetBuild THEN 1 ELSE 0 END) AS AtOrAboveTarget,
CAST(100.0 * SUM(CASE WHEN L.Build >= T.TargetBuild THEN 1 ELSE 0 END)
/ COUNT(*) AS DECIMAL(5,1)) AS CompliantPct,
MAX(T.ExtendedSupportEnds) AS ExtendedSupportEnds
FROM Latest AS L
LEFT JOIN dbo.SqlBuildTarget AS T ON T.MajorVersion = L.MajorVersion
WHERE L.RN = 1
GROUP BY COALESCE(T.ProductName, 'Unknown')
ORDER BY ProductName;
/* 4. Instances that couldn't be checked in the latest run ----------------- */
SELECT L.ServerName, L.CaptureDate, L.ErrorMessage
FROM dbo.CollectionLog AS L
WHERE L.Collection = 'SqlPatch'
AND L.CaptureDate = (SELECT MAX(CaptureDate) FROM dbo.CollectionLog WHERE Collection = 'SqlPatch')
AND L.CollectionStatus <> 'SUCCESS';
Watch out for
- GDR branch instances. An instance on the GDR branch (security fixes only, no CUs) has a much lower build number than the CU branch, so against a CU target it shows BELOW TARGET even when it has every security fix. If you run both branches, record the GDR ones as exceptions or keep them as a separate target.
- The target is a manual step. If nobody updates
dbo.SqlBuildTarget, every instance is compliant forever. Make it part of the patch cycle checklist. - Availability group names must be unique. The mixed-build query groups replicas by AG name. Two clusters that both have an AG called AG1 will look like one.
- Fill in the SQL Server 2025 date. The version table leaves its end of support empty for you to add from Microsoft’s lifecycle page.
- Patched isn’t restarted. SQL Server reports the new build as soon as the update installs, but Windows may still be waiting for a reboot. The Windows report shows that half.
Where it fits
This is part of my Auditing section, alongside the Windows patch report. My SQL Server Patches posts list each new CU and security update as it’s released, which is where your target builds come from. The Audit page has the thresholds I’d start from.
The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Integrity checks | Windows patching | Security | Capacity
Clive Roberts DBA
One thought on “SQL Server Patch Report: Every Instance Against Its Target Build”
Comments are closed.