SQL Server Patch Report: Every Instance Against Its Target Build

SQL Server Auditing: SQL Server Patch Report: Every Instance Against Its Target Build

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, ProductUpdateLevel and ProductUpdateReference.
  • 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