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.

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 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:tmpPatchLevels.xlsx -AutoSize -FreezeTopRow -BoldTopRow

Send that file off to whomever needs it and call it a day!

The post Checking for SQL Server Updates with dbatools appeared first on FLX SQL with Andy Levy.

Original post (opens in new tab)
View comments in original post (opens in new tab)