Checking for SQL Server Updates with dbatools

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.

1
2
3

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 Reference (the default URL can be found via (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).

1
2
3

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