{"id":18845,"date":"2022-09-06T14:51:10","date_gmt":"2022-09-06T12:51:10","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=18845"},"modified":"2022-09-06T14:51:11","modified_gmt":"2022-09-06T12:51:11","slug":"how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/","title":{"rendered":"How to automatically download last SQL Server SP and CU with PowerShell"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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

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:<\/p>\n\n\n\n

#List of build for the RTM version from 2014 to 2019\n$RTMBuilds = @('12.0.2000','13.0.1601','14.0.1000','15.0.2000');<\/code><\/pre>\n\n\n\n
 I need also a share path to save my SP and CU:<\/p>\n\n\n\n
#Path where files will be downloaded\n$Path = \"\\\\THOR90\\Sources\";<\/code><\/pre>\n\n\n\n
I will loop on each build and use the dbatool cmdlet Test-DbaBuild<\/a> 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:<\/p>\n\n\n\n
#Download last CU\n$res = Test-DbaBuild -Build $RTMBuild -MaxBehind \"0CU\";\n#$test special case where the last build is a SP, to download the last CU before this SP\nif ($res.CUTarget -eq $null) {\n    $res = Test-DbaBuild -Build $RTMBuild -MaxBehind \"1SP 0CU\"; \n}<\/code><\/pre>\n\n\n\n
This code applied to the RTM build of SQL Server 2016 returns:<\/p>\n\n\n\n
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<\/p>\n\n\n\n
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<\/a> to retrieve it.
Once I have my KB, I will use another dbatools cmdlet named Get-DbaKbUpdate<\/a> 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.<\/p>\n\n\n\n
$BuildTarget = $res.BuildTarget;\n$kb = (Get-DbaBuildReference $BuildTarget).KBLevel;\n$resKB = Get-DbaKbUpdate $kb;\n    \n#Find the file name and check that it is the x64\n$file = ($resKB.Link | Select-Object -Last 1) -split ('\/') | Select-Object -last 1;\nif ($file.IndexOf(\"x64\") -eq -1)\n    { $file = ($resKB.Link | Select-Object -First 1) -split ('\/') | Select-Object -last 1; }<\/code><\/pre>\n\n\n\n
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<\/a>. If the file already exists on my share I will not download it.<\/p>\n\n\n\n
#Download the file if it is not already done\nif (!(Test-Path \"$Path\\$file\" -PathType Leaf)) {\n    Write-Output \"downloading $file\"\n    Save-DbaKbUpdate $kb -Path $Path\n    } \n    else {write-output \"File $file already exists\" }<\/code><\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
################################### SP\n#Download the last SP if version <=2016\nif ($res.BuildTarget.Major -le 13) {\n    $res = Test-DbaBuild -Build $RTMBuild -MaxBehind \"0SP\";\n    $BuildTarget = $res.BuildTarget;\n    $kb = (Get-DbaBuildReference $BuildTarget).KBLevel;\n    $resKB = Get-DbaKbUpdate $kb;\n        \n    #Find the file name and check that it is the x64\n    $file = ($resKB.Link | Select-Object -Last 1) -split ('\/') | Select-Object -last 1;\n    if ($file.IndexOf(\"x64\") -eq -1)\n        { $file = ($resKB.Link | Select-Object -First 1) -split ('\/') | Select-Object -last 1; }\n        \n    #Download the file if it is not already done\n    if (!(Test-Path \"$Path\\$file\" -PathType Leaf)) {\n        Write-Output \"downloading $file\"\n        Save-DbaKbUpdate $kb -Path $Path\n        } \n        else {write-output \"File $file already exists\" }\n}<\/code><\/pre>\n\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":21,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2691,1133,1888,386,1665,2595,2689,2690,2550],"type_dbi":[],"class_list":["post-18845","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-cu","tag-cumulative-update","tag-dbatools","tag-download","tag-patching-problem","tag-powershell-2","tag-service-pack","tag-sp","tag-sql-server-2"],"acf":[],"yoast_head":"\nHow to automatically download last SQL Server SP and CU with PowerShell - dbi Blog<\/title>\n<meta name=\"description\" content=\"Powershell download SQL Server CU and SP\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to automatically download last SQL Server SP and CU with PowerShell\" \/>\n<meta property=\"og:description\" content=\"Powershell download SQL Server CU and SP\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-06T12:51:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-09-06T12:51:11+00:00\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Savorgnano\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Savorgnano\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\"},\"author\":{\"name\":\"St\u00e9phane Savorgnano\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"headline\":\"How to automatically download last SQL Server SP and CU with PowerShell\",\"datePublished\":\"2022-09-06T12:51:10+00:00\",\"dateModified\":\"2022-09-06T12:51:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\"},\"wordCount\":576,\"commentCount\":2,\"keywords\":[\"CU\",\"Cumulative Update\",\"dbatools\",\"download\",\"patching problem\",\"PowerShell\",\"Service Pack\",\"SP\",\"SQL Server\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\",\"name\":\"How to automatically download last SQL Server SP and CU with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-09-06T12:51:10+00:00\",\"dateModified\":\"2022-09-06T12:51:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"description\":\"Powershell download SQL Server CU and SP\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to automatically download last SQL Server SP and CU with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\",\"name\":\"St\u00e9phane Savorgnano\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Savorgnano\"},\"description\":\"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals & Pharmaceuticals, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to automatically download last SQL Server SP and CU with PowerShell - dbi Blog","description":"Powershell download SQL Server CU and SP","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"How to automatically download last SQL Server SP and CU with PowerShell","og_description":"Powershell download SQL Server CU and SP","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2022-09-06T12:51:10+00:00","article_modified_time":"2022-09-06T12:51:11+00:00","author":"St\u00e9phane Savorgnano","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Savorgnano","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/"},"author":{"name":"St\u00e9phane Savorgnano","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"headline":"How to automatically download last SQL Server SP and CU with PowerShell","datePublished":"2022-09-06T12:51:10+00:00","dateModified":"2022-09-06T12:51:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/"},"wordCount":576,"commentCount":2,"keywords":["CU","Cumulative Update","dbatools","download","patching problem","PowerShell","Service Pack","SP","SQL Server"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/","name":"How to automatically download last SQL Server SP and CU with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-09-06T12:51:10+00:00","dateModified":"2022-09-06T12:51:11+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"description":"Powershell download SQL Server CU and SP","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to automatically download last SQL Server SP and CU with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c","name":"St\u00e9phane Savorgnano","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","caption":"St\u00e9phane Savorgnano"},"description":"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals & Pharmaceuticals, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/18845","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=18845"}],"version-history":[{"count":11,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/18845\/revisions"}],"predecessor-version":[{"id":18856,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/18845\/revisions\/18856"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=18845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=18845"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=18845"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=18845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}