DBA Scripts: Patch SQL Server and Check Status

Peter Whyte

–

Part of the DBA-Tools Project.

Config-Driven Multi-Server Patching, and a One-Command Status Check

Invoke-SqlPatch.ps1 reads a server list and CU definitions from a config file, compares each instance’s installed version against the target, downloads the installer if needed, and applies it, locally or remotely over WinRM. patch-summary.ps1 is the fast companion check: every SQL Server instance and SSMS install on a machine, its build mapped to a human-readable CU label, and recent patch activity from the log history, in one command.

A real gap turned up testing this pair: patch-summary.ps1‘s build-to-CU lookup table had no entry for SQL Server 2025 at all, so a fully up-to-date SQL 2025 instance reported as “Unknown build.” Fixed below.

Why a Config-Driven Patcher and a Status Check Matter

  • Patch status should be answerable in one command, not a per-server manual check. patch-summary.ps1 gives version, CU label, SSMS version, and recent activity for a machine in one pass.
  • Invoke-SqlPatch.ps1 never downloads or installs anything by accident. -WhatIf reports status only, -DownloadOnly stages installers without applying them, and even a real run prompts per-server before installing unless -Force is explicitly passed.
  • The version comparison is genuinely useful in both directions. Behind means patch needed; ahead of the configured target is flagged too, worth knowing when someone patched a server manually and the config file wasn’t updated to match.

The Bug: SQL Server 2025 Missing From the CU Lookup Table

patch-summary.ps1 maps a raw build number like 17.0.4045.5 to a readable label like “SQL 2025 CU5” using a hardcoded lookup table. Running it against this lab instance, genuinely running SQL Server 2025, returned:

MSSQLSERVER               17.0.4045.5      Unknown build          Enterprise Developer Edition (64-bit)

The table simply had no SQL Server 2025 (major version 17) entries at all, only 2016 through 2022. powershell\patching\sql\patch-config.psd1, the config file Invoke-SqlPatch.ps1 reads, already had the correct mapping (17.0.4045.5 → SQL2025 CU5 / KB5084896), the two files had simply drifted apart. Fixed by adding the missing entry:

'17.0.4045.5'  = 'SQL 2025 CU5'

Re-running against the same instance:

MSSQLSERVER               17.0.4045.5      SQL 2025 CU5           Enterprise Developer Edition (64-bit)

Invoke-SqlPatch.ps1

<#
.SYNOPSIS
Download and apply SQL Server Cumulative Updates to local or remote servers.

.DESCRIPTION
Reads server list and patch definitions from patch-config.psd1. For each server and
each installed SQL instance: detects installed version, compares against target,
downloads the CU installer if needed, applies it locally or remotely via WinRM.

.PARAMETER WhatIf
Report version status and planned actions without downloading or installing anything.

.PARAMETER DownloadOnly
Download CU installers to PatchRoot but do not install.

.PARAMETER Force
Skip the per-server install confirmation prompt.
#>

(The full script is in the repo, link below.)

Real Output — WhatIf Against This Lab Instance

No elevation needed to preview:

[11:08:35] SQL Server Autopatch - 20260730-110835
[11:08:35] Config    : powershell\patching\sql\patch-config.psd1
[11:08:35] Servers   : localhost
[11:08:35] PatchRoot : D:\SQLPatches
[11:08:35] [WhatIf - no changes will be made]

[11:08:36] --- localhost ---
[11:08:36]   Instance  : MSSQLSERVER  (MSSQL17.MSSQLSERVER)
[11:08:36]   Installed : 17.0.4045.5
[11:08:36]   Target    : 17.0.4045.5 (SQL2025 CU5 / KB5084896)
[11:08:36]   Status    : UP TO DATE - no action needed.

Server    Instance    Installed   Target      Status
------    --------    ---------   ------      ------
localhost MSSQLSERVER 17.0.4045.5 17.0.4045.5 UpToDate

Correctly detects the local instance’s real version, compares it against the configured target for SQL 2025, and reports the accurate result: this instance is already current, no patch needed. Genuine output against a real, currently-patched instance, not a manufactured example.

patch-summary.ps1

<#
.SYNOPSIS
Show patch status for all SQL Server instances and SSMS on this machine.

.DESCRIPTION
Detects all installed SQL Server instances, queries their version, maps the build
number to a known CU level, and displays SSMS version alongside patch log history.
#>

(The full script is in the repo, link below.)

Real Output — Run Against This Lab Machine (After the Fix)

  SQL Server Patch Summary - 2026-07-30 11:10
  ----------------------------------------------------------------------

  SQL Server Instances
  MSSQLSERVER               17.0.4045.5      SQL 2025 CU5           Enterprise Developer Edition (64-bit)

  SSMS
  SQL Server Management Studio 22     v22.7.0

  Recent patch activity (last 5 logs):
  2026-07-30 11:08  ssms-uninstall-20260730-110856.log       Success
  2026-07-30 11:08  ssms-install-20260730-110847.log         Unknown
  2026-07-30 11:08  sql-autopatch-20260730-110835.log        Unknown
  2026-06-15 17:34  ssms-uninstall-20260615-173412.log       Success
  2026-06-15 17:34  ssms-uninstall-20260615-173406.log       Success

Every field correct after the fix: instance version, the now-resolving CU label, SSMS version, and real log history pulled straight from output-files\patches\, including the same-session -WhatIf runs from testing this exact pillar, logged and showing up honestly as Unknown (a status check, not an applied patch) rather than misreported as a success.

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

# Check status without changing anything
.\powershell\patching\sql\Invoke-SqlPatch.ps1 -WhatIf

# One-command overview of everything on this machine
.\powershell\patching\patch-summary.ps1

# Download only, install later
.\powershell\patching\sql\Invoke-SqlPatch.ps1 -DownloadOnly

# Patch specific servers, skip confirmation
.\powershell\patching\sql\Invoke-SqlPatch.ps1 -Server SQL01,SQL02 -Force

These scripts live in the repo at:

Understanding the Results

  • “Unknown build” in patch-summary.ps1 means the CU lookup table hasn’t been updated for that SQL Server version yet, not that something is wrong with the instance. Add the mapping, same as the SQL 2025 fix here, rather than assuming it’s a script defect.
  • “AHEAD” status in Invoke-SqlPatch.ps1 means the installed build is newer than the configured target. Worth updating patch-config.psd1 rather than treating it as an error, someone likely patched manually and the config drifted behind reality.
  • Unknown in the recent-activity log is expected for status-only and -WhatIf runs. Only a completed, successful install writes success language to the log.

Best Practices

  • Keep patch-config.psd1‘s CU download URLs and target versions current manually, Microsoft doesn’t expose a queryable API for this, the note at the bottom of both scripts’ output says so plainly.
  • Run Invoke-SqlPatch.ps1 -WhatIf across your whole server list before any real patch window, know what’s behind, current, and ahead before deciding what to actually apply.
  • Use -DownloadOnly ahead of a maintenance window to stage installers, then run without it during the window itself to apply with less time pressure.
  • Run patch-summary.ps1 as a routine check independent of any patch cycle, it’s cheap, read-only, and catches CU-map gaps (like the SQL 2025 one here) the moment a new major version shows up as “Unknown.”

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

My instance shows “Unknown build” even though I know the patch level, what’s wrong?

The build-to-CU lookup table in patch-summary.ps1 needs a manual entry added for that specific build. This isn’t unique to any one version, the table simply needs updating every time a new CU releases, exactly the gap the SQL 2025 fix in this post closes for that version.

What happens if Invoke-SqlPatch.ps1 finds an instance running a newer build than the configured target?

It reports Ahead and takes no action, it never downgrades. Update patch-config.psd1‘s target version to match reality if the newer build is intentional.

Summary

One script patches, config-driven and safe by default (-WhatIf, -DownloadOnly, per-server confirmation); the other reports status in one command across every SQL Server and SSMS install on a machine. A real gap in the status script’s CU lookup table, no SQL Server 2025 entry at all, was found and fixed by testing both against this lab’s genuinely-current SQL 2025 instance, confirmed correct with real before-and-after output.

Blog Categories

Recent Posts

Comments

Leave a Reply

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