Finding & Downloading Required SQL Server Updates

A little while back, I offered up a one-liner to scan your SQL Server instances and report which ones are out of date. But what if you need to take the next step, determining which updates need to be downloaded? That’s exactly what Josh asked on the SQL Community Slack recently.

Can Test-DbaBuild also bring back the KB number? From reading the docs it looks possible as it does return the compliant version form the .json file, or at least I think it does 😀. The intention is if the KB is returned, then one could pipe it through to Get-DbaKbUpdate and/or Save-DbaKbUpdate to have the KB’s automatically downloaded to the desired path.

I found the question interesting, so I decided to dig in a bit.

Let’s rewind to that previous post. But this time, we’ll add three more parameters to the output - KBLevel, BuildLevel and BuildTarget

The KBLevel property of Test-DbaBuild’s output tells you the KB corresponding to the current service pack or cumulative update you’re running. Not terribly useful if you need to sort out which updates need to be downloaded - it’s telling us where we are, not where we need to be. BuildTarget, on the other hand, is where we want to be.

1
2
3

Test-DbaBuild -SqlInstance $Localinstances -Update -MaxBehind 0CU `
  | Select-Object SqlInstance, NameLevel, KBLevel, SPLevel, SPTarget, CULevel, CUTarget, BuildLevel, BuildTarget, Compliant `
  | Format-Table -AutoSize;

We can hop over to SQLServerUpdates.com to check those build and KB numbers against the CU numbers.

What Next?

We can use Get-DbaBuildReference to sort out what KB we need to look up and download. Get-DbaBuildReference scans through the build information that dbatools has and can report build numbers, KB number, names, etc. using build number, KB number, CU or SP name as input. Let’s see what it knows about build 14.0.3335, which is the target for the SQL Sever 2017 instance.

1

Get-DbaBuildReference -Build 14.0.3335 | Format-Table -AutoSize;

OK, now that’s something we can work with. We’ve got the KB number corresponding to CU21.

Download Via Browser? Nope!

Another of Chrissy LeMaire’s (blog | twitter) projects is KBUpdate. Compared to dbatools it’s a pretty compact module, but it’s incredibly useful - it’ll seek out information about KB updates and even download them for you! She’s also rolled these functions into dbatools for convenience, so we don’t need to install or import that module separately.

Get-DbaKbUpdate provides a wealth of information about the KB number that’s passed into it.

1

Get-DbaKbUpdate 4557397

Title : SQL Server 2017 RTM Cumulative Update (CU) 21 KB4557397
Description : CU21 for SQL Server 2017 RTM upgraded all SQL Server 2017 RTM instances and components installed
through the SQL Server setup. CU21 can upgrade all editions and servicing levels of SQL Server
2017 RTM to the CU21 level. For customers in need of additional installation options, please visit
the Microsoft Download Center to download the latest Cumulative Update
(https://support.microsoft.com/en-us/kb/957826). To learn more about SQL Server 2017 RTM CU21,
please visit the Microsoft Support (http://support.microsoft.com) Knowledge Base article KB4557397.
Architecture :
NameLevel : 2017
SPLevel : RTM
KBLevel : 4557397
CULevel : CU21
BuildLevel : 14.0.3335
SupportedUntil : 2027-10-12 00:00:00
Language :
Classification : Updates
SupportedProducts : Microsoft SQL Server 2017
MSRCNumber : n/a
MSRCSeverity : n/a
Hotfix : false
Size : 533.3 MB
UpdateId : 6bc92812-80ea-4c05-833c-433d60457e64
RebootBehavior : Can request restart
RequestsUserInput : No
ExclusiveInstall :
NetworkRequired : No
UninstallNotes : SQL Server 2017 RTM CU21 may be removed by selecting KB4557397 from "View Installed Updates" in
the Programs and Features section of the Control Panel, under SQL Server 2017.
UninstallSteps : n/a
SupersededBy : n/a
Supersedes : {SQL Server 2017 RTM Cumulative Update (CU) 1 KB4038634, SQL Server 2017 RTM Cumulative Update
(CU) 2 KB4052574, SQL Server 2017 RTM Cumulative Update (CU) 3 KB4052987, SQL Server 2017 RTM
Cumulative Update (CU) 4 KB4056498…}
LastModified : 7/1/2020
Link : {http://download.windowsupdate.com/d/msdownload/update/others/2020/07/32055076\_0e9fcf41f92570c69b61
5407ef8cae1083e1600c.cab, http://download.windowsupdate.com/d/msdownload/update/software/updt/2020/
07/sqlserver2017-kb4557397-x64\_e91bfa33a34accf82a0c374c9e8b7d0ce3b7ce05.exe}

Great, but I need to download the installer. It’s covered by Save-DbaKbUpdate.

Save-DbaKbUpdate -Name 4557397 -Path C:\Users\Andy\Downloads\

Putting it all together

Recapping real quick, here’s what we can do with the snippets above:

• Scan SQL Server instances to find out if they’re running the latest update
• Find the KB number for the latest build of each version of SQL Server
• Find and download the update installer based on the KB number

Great! Let’s glue the pieces together.

$DownloadPath = "C:\Users\Andy\Downloads\SQLUpdates";
$LocalInstances = @('flexo\sql17', 'flexo\sql19');

$BuildTargets = Test-DbaBuild -SqlInstance $LocalInstances -MaxBehind 0CU -Update | Where-Object { !$PSItem.Compliant } | Select-Object -ExpandProperty BuildTarget -Unique;

Get-DbaBuildReference -Build $BuildTargets | ForEach-Object { Save-DbaKBUpdate -Path $DownloadPath -Name $PSItem.KBLevel };

First Line

• Test-DbaBuild - I covered this in the aforementioned blog post
• Where-Object {!$PSItem.Compliant} - Only pick out the instances which are not compliant with my MaxBehind check. I care about the compliant instances, I’m just not interested in them for this exercise.
• Select-Obect BuildTarget -Unique - I might have multiple instances that are returned, but I only want one copy of each BuildTarget (target build number).

Second Line

• Get-DbaBuildReference -Build $BuildTargets - Look through the build reference and get the corresponding KB numbers. While this function does take a -Update parameter to update the build reference file, we already did that on the previous line with Test-DbaBuild so it’s unnecessary here.
• ForEach-Object {Save-DbaKBUpdate} - Iterate over the build references that I found and download their KB items into my download folder.

There we go! I can just let this run and go refill my water bottle while I wait for the downloads.

Things to Note

1. I’ve only tested this with SQL Server 2017 and 2019. These versions won’t have Service Packs, as opposed to the “stepped” approach for earlier releases with SPs followed by CUs and other updates. If you need to support older SPs of SQL Server 2012/2014/2016, you may need to adjust this some.
2. Under PowerShell 7, it should be possible to use Foreach-Object -Parallel on that final step. I was getting permissions errors depending on my target directory, and didn’t spend much (any) time trying to sort it out.
3. I originally conceived of this as a one-liner, and it does work that way. But the pipeline isn’t always the greatest way to do things and Get-DbaBuildReference doesn’t take any inputs via the pipeline, so it kind of looses its lustre running that way.