It turns out I was doing this all wrong for months.
For the longest time, I’ve been checking my SQL Server instances to see what needs patching with
Test-DbaBuild from the dbatools PowerShell module. But the result was always the same – it never returned a Service Pack or Cumulative Update target. I glossed over it because I knew what the right answer was already, but recently I decided that wasn’t good enough. We need a reliable report to give to other people.
Close, but No Cigar
Here’s what I’d been running all along.
Test-DbaBuild -SqlInstance $Localinstances -Update -Latest | Select-Object SqlInstance, NameLevel, SPLevel, SPTarget, CULevel, CUTarget, Compliant | Format-Table -AutoSize
I was pulling the latest dbatools SQL Server Build Reference1(Get-DbatoolsConfig assets.sqlbuildreference).Value. The most recent time I ran the above, I knew that reference was up to date because I filed the pull requests to get it updated and had received the emails telling me that they’d been merged.
Instead of using this to find out just how far behind I was, I’d just take it as “ok, I’m not on the latest.” But after a while, that’s just lazy.
Let’s Figure This Out
The help for the
-Latest parameter says:
-Latest [<Switch>] Shortcut for specifying the very most up-to-date build available.
Isn’t that what I want? I want to test against the latest build. And I am, but why am I not getting a
CUTarget (to know what to download) even though the instances aren’t compliant with being the latest and the build reference is up to date? It turns out that I wasn’t alone in my confusion. Kirill Kravstov (blog | twitter) had the same confusion and logged an issue on Github for it. I had to read through twice to fully grasp what was going on.
To get a
CUTarget in your output, and to have that be the latest release, specifying
-Latest isn’t the way to go. You need to tell
Test-DbaBuildReference that your checked instances need to be behind latest by a maximum of 0 CUs. So instead, use
-MaxBehind 0CU (and this value is case-sensitive).
Test-DbaBuild -SqlInstance $Localinstances -Update -MaxBehind 0CU | Select-Object SqlInstance,NameLevel,SPLevel,SPTarget,CULevel,CUTarget, Compliant | Format-Table -AutoSize
This looks a lot better, and it’s far more informative. Now I can see exactly how far behind I am.
One Step Further
This is great and all, but what if I have a sprawling SQL Server estate and other folks want a report on the current state of patching? Let’s throw the results into an Excel file with the handy ImportExcel module.
Test-DbaBuild -SqlInstance $Localinstances -Update -MaxBehind 0CU | Select-Object SqlInstance, NameLevel, SPLevel, SPTarget, CULevel, CUTarget, Compliant | Export-Excel -path c:\tmp\PatchLevels.xlsx -AutoSize -FreezeTopRow -BoldTopRow
Send that file off to whomever needs it and call it a day!