Applies to any business running SQL Server on Windows: SQL Server 2019, 2022 and 2025, Windows Server 2016 to 2025.
Most businesses get database reports by accident. Something goes wrong, someone writes a query, and the query becomes a report that gets e-mailed for the next ten years. This page is the list I’d start from on purpose: what each report is for, how often it should run, who should read it and what number should make someone act.
The short version: a handful of daily reports for the DBA, a weekly summary for the IT lead, and a monthly pack for management and risk. Patching gets the most space, because it’s where auditors look first and where businesses most often have nothing.
The core reports
| Report | How often | Act when |
|---|---|---|
| Backups | Daily | Any database never backed up or past its RPO |
| Restore tests | Monthly | Any failed or skipped test |
| Availability | 5 minutes | Any server down; 30-day uptime below the agreed level |
| Disk space | Hourly | Below 10% or 10 GB free; full within 30 days |
| Failed jobs | Daily | Any failure on a production job |
| Integrity checks | Weekly | Any error, or a database not checked for 8 days |
| Inventory | Daily | Unknown server; collection failed; bad settings |
| Licensing | Weekly | Free edition in production; cores changed |
| Windows patching | Weekly | See the thresholds below |
| SQL patching | Monthly | See the thresholds below |
| Logins | Monthly | Any change not on a change ticket |
| Capacity | Quarterly | Anything full within 6 months |
These reports in my Auditing section each have their own write-up and script:
- Licensing
- Availability: ping and response time
- Disk space alerts
- Server inventory
- Database inventory
- Backups
- SSIS errors
The patching scripts are below.
Who reads what
A report nobody reads is just disk usage. I split them by audience:
- The DBA reads the daily exceptions: backups, failed jobs, SSIS errors, disk. Only the problems, ideally in one e-mail, before 9am.
- The IT lead or infrastructure manager gets a weekly summary: anything still open from the daily reports, patching status and capacity warnings.
- Management, risk and audit get a monthly pack: uptime against the service level, backup and restore test compliance, patch compliance, licensing position and open exceptions with an owner and a date.
The monthly pack should fit on two pages. If it doesn’t, it isn’t read.
Windows patching
Microsoft releases security updates on the second Tuesday of each month, Patch Tuesday. A Windows patching report answers one question: did this month’s updates actually land on every server, and if not, why not and until when?
What to report
- Each server, with its patch group or ring: test, pre-production, production wave 1, wave 2. Which ring a server is in decides its deadline.
- The last update installed and when. The most honest single number: days since the last update.
- Missing updates by severity, critical and important especially. This comes from your patching tool (WSUS, Configuration Manager, Intune, Azure Update Manager), not from a script.
- Reboot pending. An installed update that’s waiting for a reboot isn’t protecting anything. A server that’s been pending for two weeks failed its patching.
- Last boot time, as a cross-check. A server that hasn’t booted since before Patch Tuesday hasn’t finished patching.
- Operating system and support status. Unsupported Windows gets no security updates, so it can never be compliant.
- Exceptions: servers that are deliberately behind, with the reason, the risk owner and the date the exception ends.
How often
Collect daily. Report weekly during the patch cycle so stragglers get chased, and monthly as a compliance figure: the percentage of servers fully patched within the deadline.
Who reads it
The infrastructure team owns it and reads it weekly. The IT manager reads the monthly figure. Risk or compliance reads it because cyber insurance questionnaires and certifications ask for it, and they’ll ask for the evidence.
Thresholds
- Critical and high-risk security updates within 14 days of release. That’s the Cyber Essentials requirement in the UK, and a sensible default anywhere.
- All other security updates within 30 days.
- Days since last update over 35: overdue. That’s one monthly cycle plus a week.
- Reboot pending for more than 7 days: treat it as not patched.
- Out-of-support Windows: always red, until it’s upgraded or isolated with a documented exception. Windows Server 2012 R2 left support in October 2023, and Windows Server 2016’s extended support ends in January 2027, so now is the time to plan those.
- Compliance target: 95% or more of servers fully patched within the deadline, and every exception named.
SQL Server patching
SQL Server patching is a different rhythm. Security fixes arrive as GDRs. Cumulative updates (CUs) bring fixes and improvements, every couple of months while a version is in mainstream support. Once a version leaves mainstream support, it gets security fixes only.
Windows updates don’t patch SQL Server unless you’ve allowed Microsoft Update to include other Microsoft products, and on database servers most people haven’t. So SQL Server needs its own report.
What to report
- Each instance with its version, build and CU level.
SERVERPROPERTY('ProductVersion'),ProductUpdateLevel(CU number) andProductUpdateReference(the KB). - The target build. The CU and security update you’ve tested and approved for each version. You maintain this list, and the report compares every instance against it.
- Below target, and by how much. One CU behind is a scheduling issue. Four CUs behind is a decision someone made, and it should be written down.
- Support status of each version, with the end of extended support date.
- Branch. GDR or CU. An instance on the GDR branch only gets security fixes; once you install a CU you can’t go back to GDR for that version.
- The same build across an availability group or cluster. Mismatched replicas are fine for a few hours during rolling patching, never for weeks.
How often
Monthly, after Patch Tuesday and after each new CU has been tested. Collect daily so the report always reflects the current build, not the build from the day someone remembered to look.
Who reads it
The DBA owns it. The IT manager reads it monthly. Risk reads the out-of-support list, because an unsupported SQL Server is a risk they’ve accepted whether or not anyone told them.
Thresholds
- Security updates (GDRs, or CUs that contain security fixes) within 14 days for anything reachable from users or the internet, and within 30 days for the rest, matching the Windows policy.
- Cumulative updates within 60 to 90 days of release, after testing. Not on day one: let a CU settle for a couple of weeks and read the known issues first.
- No production instance more than two CUs behind the approved target without a written exception.
- Out-of-support versions: always red. SQL Server 2014 left extended support in July 2024 and SQL Server 2016 in July 2026. SQL Server 2017 follows in October 2027.
- Every AG replica on the same build within a week of starting a rolling upgrade.
The patching script
This collects both halves for every server: the last Windows update and when it was installed, pending reboot, last boot and the SQL Server build and CU. It’s the independent check. Your patching tool tells you what it tried to install. This tells you what the server actually reports.
# Invoke-PatchAudit.ps1
# Windows and SQL Server patch level for every server: last Windows update
# installed, pending reboot, last boot, SQL build and CU. Schedule it daily.
# It doesn't replace WSUS, Intune or your patching tool. It is the
# independent check that the patching actually landed.
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$SqlQuery = @"
SELECT
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) AS SQLVersion,
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('Edition') AS NVARCHAR(128)) AS SQLEdition;
"@
$PendingRebootCheck = {
$Keys = @(
'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending',
'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired'
)
[bool]($Keys | Where-Object { Test-Path $_ })
}
$Servers = Get-ServerList -Path $ServerListPath
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.PatchAudit', 'U') IS NULL
CREATE TABLE dbo.PatchAudit
(
CaptureDate DATETIME2(0) NOT NULL
CONSTRAINT DF_PatchAudit_CaptureDate DEFAULT SYSDATETIME(),
ServerName NVARCHAR(128) NOT NULL,
OSName NVARCHAR(256) NULL,
OSBuild NVARCHAR(64) NULL,
LastBootTime DATETIME2(0) NULL,
LastUpdateKB NVARCHAR(32) NULL,
LastUpdateInstalled DATETIME2(0) NULL,
RebootPending BIT NULL,
SQLVersion NVARCHAR(32) NULL,
ProductLevel NVARCHAR(32) NULL,
ProductUpdateLevel NVARCHAR(32) NULL,
ProductUpdateReference NVARCHAR(32) NULL,
SQLEdition NVARCHAR(128) NULL,
CollectionStatus VARCHAR(20) NOT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
"@
foreach ($Server in $Servers) {
$Row = [ordered]@{ ServerName = $Server; CollectionStatus = 'SUCCESS' }
$Problems = @()
# Windows: each part is tried separately so one blocked port doesn't lose the rest
try {
$OS = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName (Get-ComputerName $Server) -OperationTimeoutSec 60 -ErrorAction Stop
$Row.OSName = $OS.Caption
$Row.OSBuild = $OS.Version
$Row.LastBootTime = $OS.LastBootUpTime
}
catch { $Problems += "OS: $($_.Exception.Message)" }
try {
$Last = Get-HotFix -ComputerName (Get-ComputerName $Server) -ErrorAction Stop |
Where-Object { $_.InstalledOn } |
Sort-Object InstalledOn -Descending |
Select-Object -First 1
$Row.LastUpdateKB = $Last.HotFixID
$Row.LastUpdateInstalled = $Last.InstalledOn
}
catch { $Problems += "Updates: $($_.Exception.Message)" }
try {
$Row.RebootPending = Invoke-Command -ComputerName (Get-ComputerName $Server) -ScriptBlock $PendingRebootCheck -ErrorAction Stop
}
catch { $Problems += "Reboot check: $($_.Exception.Message)" }
# SQL Server
try {
$Source = Open-SqlConnection -Server $Server
try {
$Sql = (Invoke-SqlQuery $Source $SqlQuery).Rows[0]
}
finally {
$Source.Close()
}
foreach ($Column in $Sql.Table.Columns.ColumnName) {
$Row[$Column] = $Sql[$Column]
}
}
catch { $Problems += "SQL: $($_.Exception.Message)" }
if ($Problems) {
$Row.CollectionStatus = if ($Problems.Count -ge 4) { 'FAILED' } else { 'PARTIAL' }
$Row.ErrorMessage = $Problems -join ' | '
Write-Warning "$Server : $($Row.ErrorMessage)"
}
Add-InventoryRow -Connection $Repo -Table 'dbo.PatchAudit' -Row $Row
Write-Host "$Server : last update $($Row.LastUpdateKB) $($Row.LastUpdateInstalled), SQL $($Row.SQLVersion) $($Row.ProductUpdateLevel)"
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# 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()
}
The patch compliance report
The target table holds one row per SQL Server version. After each patch cycle, put the approved build in TargetBuild. The report then marks every server’s Windows and SQL Server status.
-- Run once in the DBA database: the target build for each SQL Server version.
-- Fill TargetBuild in from Microsoft's "latest updates for SQL Server" page after
-- each patch cycle, once the new CU or GDR has passed testing.
IF OBJECT_ID('dbo.SqlBuildTarget', 'U') IS NULL
BEGIN
CREATE TABLE dbo.SqlBuildTarget
(
MajorVersion INT NOT NULL PRIMARY KEY, -- 15 = 2019, 16 = 2022, 17 = 2025
ProductName NVARCHAR(40) NOT NULL,
TargetBuild NVARCHAR(32) NULL, -- e.g. the full ProductVersion of the approved CU
TargetName NVARCHAR(40) NULL, -- e.g. 'CU n + GDR'
ExtendedSupportEnds DATE NULL
);
INSERT INTO dbo.SqlBuildTarget (MajorVersion, ProductName, ExtendedSupportEnds)
VALUES (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
END;
GO
-- The monthly patch compliance report
DECLARE @WindowsDays INT = 35, -- monthly cycle plus a week's grace
@RebootDays INT = 7; -- pending reboot older than this is a failed patch
WITH Latest AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureDate DESC) AS RN
FROM dbo.PatchAudit
),
Cur AS
(
SELECT L.*,
CAST(PARSENAME(L.SQLVersion, 4) AS INT) AS MajorVersion,
CAST(PARSENAME(L.SQLVersion, 2) AS INT) AS Build
FROM Latest AS L
WHERE L.RN = 1
)
SELECT
C.ServerName,
C.OSName,
C.LastUpdateKB,
C.LastUpdateInstalled,
DATEDIFF(DAY, C.LastUpdateInstalled, C.CaptureDate) AS DaysSinceWindowsUpdate,
C.RebootPending,
DATEDIFF(DAY, C.LastBootTime, C.CaptureDate) AS DaysSinceBoot,
T.ProductName,
C.SQLVersion,
C.ProductUpdateLevel,
T.TargetBuild,
CASE
WHEN C.CollectionStatus = 'FAILED' THEN 'NO DATA'
WHEN C.LastUpdateInstalled IS NULL
OR DATEDIFF(DAY, C.LastUpdateInstalled, C.CaptureDate) > @WindowsDays THEN 'OVERDUE'
WHEN C.RebootPending = 1
AND DATEDIFF(DAY, C.LastUpdateInstalled, C.CaptureDate) > @RebootDays THEN 'REBOOT NEEDED'
ELSE 'OK'
END AS WindowsStatus,
CASE
WHEN C.SQLVersion IS NULL THEN 'NO DATA'
WHEN T.ExtendedSupportEnds < CAST(C.CaptureDate AS DATE) THEN 'OUT OF SUPPORT'
WHEN T.TargetBuild IS NULL THEN 'NO TARGET SET'
WHEN C.Build < CAST(PARSENAME(T.TargetBuild, 2) AS INT) THEN 'BELOW TARGET'
ELSE 'OK'
END AS SqlStatus,
C.CaptureDate
FROM Cur AS C
LEFT JOIN dbo.SqlBuildTarget AS T
ON T.MajorVersion = C.MajorVersion
ORDER BY
CASE WHEN T.ExtendedSupportEnds < CAST(C.CaptureDate AS DATE) THEN 0 ELSE 1 END,
DaysSinceWindowsUpdate DESC;
Watch out for
- Reports that only count successes. A server that can’t be reached doesn’t fail a patch report, it just isn’t on it. Every report here records failed collections, and the monthly pack should count them.
- Get-HotFix isn’t the whole picture. It lists Windows updates, not Office, drivers or SQL Server. Use it as a cross-check, with the patching tool as the source for missing updates.
- Pending reboot checks give false positives. Some software sets the same registry flags. If a server always shows pending, find out what keeps setting it.
- Exceptions without end dates. “Can’t patch, vendor not certified” is a valid exception for a month. After a year it’s a decision nobody remembers making. Every exception needs an owner and an expiry.
- Thresholds nobody agreed. The numbers on this page are my defaults. The ones that matter are the ones the business has signed off, because those are the ones you’ll be measured against.
Where it fits
This page is the overview for my Auditing section. Each report has its own write-up with the script. My SQL Server Patches posts list each new CU and security update as it’s released, which is the input for your target builds. The SQL Server DBA Handbook covers the rest.
The UK National Cyber Security Centre’s Cyber Essentials scheme sets out the 14-day rule for high and critical updates.
Clive Roberts DBA