How to automatically download last SQL Server SP and CU with PowerShell

One of my customer asked me some days ago to create a PowerShell script to patch its SQL Server instances. After a reflection time, the first step will be to download the last Service Pack and the last Cumulative Update depending of the SQL Server version and after to apply those patches to the SQL Server instances. In this blog post I will explain how I managed to run those downloads.

As usual, each time I have to deal with SQL Server and PowerShell I have a look to the famous dbatools pages to check if I can find useful cmdlets. Jackpot, somes attired my attention, I will present you those later on.

I want to download the last Cumulative Update and Service pack for the SQL Server versions 2014 to 2019. I create a list with the RTM build of each version:

#List of build for the RTM version from 2014 to 2019
$RTMBuilds = @('12.0.2000','13.0.1601','14.0.1000','15.0.2000');

I need also a share path to save my SP and CU:

#Path where files will be downloaded
$Path = "\\THOR90\Sources";

I will loop on each build and use the dbatool cmdlet Test-DbaBuild to find the last Cumulative Update for the RTM version in the loop. This cmdlet used with the parameters -Build with my RTM build number and -MaxBehind “0CU” provides me the target build number of the last Cumulative Update for my SQL Server version. There is just a small exception for the moment with SQL Server 2016 where the last patch is a Service Pack and there is no CU for the moment for this Service Pack. In this case, I need to check for the version -1 of the last SP and find the last CU for this SP, it means change the parameter -MaxBehind to “1SP 0CU”. Here is the code:

#Download last CU
$res = Test-DbaBuild -Build $RTMBuild -MaxBehind "0CU";
#$test special case where the last build is a SP, to download the last CU before this SP
if ($res.CUTarget -eq $null) {
    $res = Test-DbaBuild -Build $RTMBuild -MaxBehind "1SP 0CU"; 
}

This code applied to the RTM build of SQL Server 2016 returns:

Build : 13.0.1601
BuildLevel : 13.0.1601
BuildTarget : 13.0.5888
Compliant : False
CULevel :
CUTarget : CU17
KBLevel :
MatchType : Exact
MaxBehind : 1SP 0CU
NameLevel : 2016
SPLevel : RTM
SPTarget : SP2
SupportedUntil : 1/9/2018 12:00:00 AM

It means that the last CU available for SQL Server 2016 is the CU17.
Now, I need to find the KB number corresponding to the build number of my CU17. I will use the dbatools cmdlet Get-DbaBuildReference to retrieve it.
Once I have my KB, I will use another dbatools cmdlet named Get-DbaKbUpdate which will parse the catalog.update.microsoft.com and grab details for KB files.
I can have multiple links to download my KB such as a link for the 32 bits version and another one for the 64 bits. I will use the 64 bits and select just the file name to download it with the last dbatools cmdlet used in this blog.

$BuildTarget = $res.BuildTarget;
$kb = (Get-DbaBuildReference $BuildTarget).KBLevel;
$resKB = Get-DbaKbUpdate $kb;
    
#Find the file name and check that it is the x64
$file = ($resKB.Link | Select-Object -Last 1) -split ('/') | Select-Object -last 1;
if ($file.IndexOf("x64") -eq -1)
    { $file = ($resKB.Link | Select-Object -First 1) -split ('/') | Select-Object -last 1; }

As I have now my file name, I will download this file from Microsoft and save it on my share with the dbatools cmdlet Save-DbaKbUpdate. If the file already exists on my share I will not download it.

#Download the file if it is not already done
if (!(Test-Path "$Path\$file" -PathType Leaf)) {
    Write-Output "downloading $file"
    Save-DbaKbUpdate $kb -Path $Path
    } 
    else {write-output "File $file already exists" }

This first part of the script download the last CUs, to download the last SPs I will use exactly the same script when the major build version is lower or equal to 13 (SQL Server 2016) and save them also to my share:

################################### SP
#Download the last SP if version <=2016
if ($res.BuildTarget.Major -le 13) {
    $res = Test-DbaBuild -Build $RTMBuild -MaxBehind "0SP";
    $BuildTarget = $res.BuildTarget;
    $kb = (Get-DbaBuildReference $BuildTarget).KBLevel;
    $resKB = Get-DbaKbUpdate $kb;
        
    #Find the file name and check that it is the x64
    $file = ($resKB.Link | Select-Object -Last 1) -split ('/') | Select-Object -last 1;
    if ($file.IndexOf("x64") -eq -1)
        { $file = ($resKB.Link | Select-Object -First 1) -split ('/') | Select-Object -last 1; }
        
    #Download the file if it is not already done
    if (!(Test-Path "$Path\$file" -PathType Leaf)) {
        Write-Output "downloading $file"
        Save-DbaKbUpdate $kb -Path $Path
        } 
        else {write-output "File $file already exists" }
}

Finally, I have all my Cumulative Updates and Service Packs available on my share. I can schedule the complete script every month to have always on my share the last available CU and SP for the SQL Server versions I have on my environment.
The next step will be to apply those patches to my SQL Server instances.
It will be the subject of my next blog post.