{"id":20420,"date":"2022-11-11T15:00:26","date_gmt":"2022-11-11T14:00:26","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=20420"},"modified":"2022-12-06T09:01:38","modified_gmt":"2022-12-06T08:01:38","slug":"patch-a-sql-server-instance-automatically-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/","title":{"rendered":"Patch a SQL Server instance automatically with PowerShell"},"content":{"rendered":"\n<p>In my <a href=\"https:\/\/www.dbi-services.com\/blog\/how-to-automatically-download-last-sql-server-sp-and-cu-with-powershell\/\">last blog post<\/a> I explained how to automatically download the last Service Pack and Cumulative Update for all versions of SQL Server.<br>Here I will show you how to patch your SQL Server instances automatically with some cmdlets from the <a href=\"https:\/\/dbatools.io\/commands\/\">dbatools<\/a>.<\/p>\n\n\n\n<p>First we need a SQL Credential to be able to remotely connect to the server where we will patch our instances:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$OScred = Get-Credential;<\/code><\/pre>\n\n\n\n<p>Once done we will have to find the instances located on the server. Here multiple possibilities: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>you can create just a list with your instances $InstanceList = @(&#8216;Thor90\\SQL19_1&#8242;,&#8217;Thor91\\SQL19_1&#8217;);<\/li>\n\n\n\n<li>you can search the instance on your server with $InstanceList = Get-DbaService | where-object { $_.ServiceType -eq &#8216;Engine&#8217; -and $_.State -eq &#8216;Running&#8217; };<\/li>\n\n\n\n<li>you can also select your instance in your CMS with $InstanceList = Get-DbaRegServer -SqlInstance &#8216;Thor90\\CMS&#8217; -Group &#8220;Prod\\SQL2019&#8221;; (my case)<\/li>\n<\/ul>\n\n\n\n<p>Now that we have our list of instances, we will loop on those instances, run a quick connection test and if it succeeds patch our instance.<br>To patch the instance, I will use 2 dbatools cmdlets which are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/docs.dbatools.io\/Get-DbaBuildReference#Get-DbaBuild\">Get-DbaBuildReference<\/a>: will return information about the instance like the SP, CU and used with the -Update switch will update the local reference with the most up to date one <\/li>\n\n\n\n<li><a href=\"https:\/\/docs.dbatools.io\/Update-DbaInstance\">Update_DbaInstance<\/a>: will start a process which will update a SQL Server instance to a specified version<br>This cmdlet is really powerful with lots of parameter, possible options and also risks, I let you go through its definition.<br>I will use it  in my example with the -version parameter which is the target version I want to reach, with the switch -Restart to automatically restart my server after the installation, the parameter -Path to specif the location where my patches have been downloaded, the -Credential to have permission to log remotely to my server and -Confirm to false to run without confirmation.<\/li>\n<\/ul>\n\n\n\n<p>I&#8217;ll use also some logging cmdlets wrote by myself which are Out-Log, Add-Warning and Add-Error.<\/p>\n\n\n\n<p>The loop on all my instance list will look like:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ForEach ($Instance in $InstanceList){\n     Out-Log -Message \"Patching instance $($Instance.InstanceName)\" -LogFile $LogFile\n        \n    $connection = Test-DBAConnection -SqlInstance $instance.InstanceName -WarningVariable warningvar;\n    Add-Warning -Warning $Warningvar;\n    If ($connection -and $connection.ConnectSuccess) {\n        Out-Log -Message \"Connection to instance $($Instance.InstanceName) succeeded\" -LogFile $LogFile\n\n        #Update the instance build which is used by the cmdlet Update-DbaInstance\n        Out-Log -Message \"Update the build of the instance $($Instance.InstanceName)\" -LogFile $LogFile\n        Get-DbaBuildReference -SqlInstance $instance.InstanceName -Update -WarningVariable warningvar;\n        Add-Warning -Warning $Warningvar;\n\n        Out-Log -Message \"Start patching of the instance $Instance.InstanceName\" -LogFile $LogFile\n        try {\n            $res = Update-DbaInstance -ComputerName $Instance.ComputerName -InstanceName $Instance.InstanceName -credential $OScred -Version CU16 -Path \\\\Thor90\\sources\\SQL2019 -Restart -Confirm:$false -WarningVariable warningvar;\n            Add-Warning -Warning $Warningvar;\n        }\n        catch {\n            Add-Error -Message \"Patching of the instance $instance$Instance.InstanceName not possible, Exception: $($error&#091;0].Exception.Message)\";\n        }\n\n    }\n    Else {\n        Add-Error -Message \"Impossible to connect to the instance $instance$Instance.InstanceName.\";\n    }\n}<\/code><\/pre>\n\n\n\n<p>This script will patch my production SQL Server 2019 instances to CU16 with the KB located on my share drive \\\\Thor90\\sources\\SQL2019 and restart the server once succeeded.<br>Lots of scenario can be covered by using this cmdlet, I let you playing with it, but on your test environment first \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my last blog post I explained how to automatically download the last Service Pack and Cumulative Update for all versions of SQL Server.Here I will show you how to patch your SQL Server instances automatically with some cmdlets from the dbatools. First we need a SQL Credential to be able to remotely connect to [&hellip;]<\/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":[2746,2745,2308,1888,155,272,51],"type_dbi":[],"class_list":["post-20420","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-apply-cu","tag-apply-sp","tag-automatic","tag-dbatools","tag-patching","tag-powershell","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Patch a SQL Server instance automatically with PowerShell - dbi Blog<\/title>\n<meta name=\"description\" content=\"Patching SQL Server to last CU SP with Powershell and dbatools\" \/>\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\/patch-a-sql-server-instance-automatically-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Patch a SQL Server instance automatically with PowerShell\" \/>\n<meta property=\"og:description\" content=\"Patching SQL Server to last CU SP with Powershell and dbatools\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-11T14:00:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-12-06T08:01:38+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=\"3 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\/patch-a-sql-server-instance-automatically-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\"},\"author\":{\"name\":\"St\u00e9phane Savorgnano\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"headline\":\"Patch a SQL Server instance automatically with PowerShell\",\"datePublished\":\"2022-11-11T14:00:26+00:00\",\"dateModified\":\"2022-12-06T08:01:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\"},\"wordCount\":385,\"commentCount\":6,\"keywords\":[\"Apply CU\",\"apply SP\",\"automatic\",\"dbatools\",\"Patching\",\"PowerShell\",\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\",\"name\":\"Patch a SQL Server instance automatically with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-11-11T14:00:26+00:00\",\"dateModified\":\"2022-12-06T08:01:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"description\":\"Patching SQL Server to last CU SP with Powershell and dbatools\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Patch a SQL Server instance automatically 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 &amp; Pharmaceuticals, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Patch a SQL Server instance automatically with PowerShell - dbi Blog","description":"Patching SQL Server to last CU SP with Powershell and dbatools","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\/patch-a-sql-server-instance-automatically-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Patch a SQL Server instance automatically with PowerShell","og_description":"Patching SQL Server to last CU SP with Powershell and dbatools","og_url":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2022-11-11T14:00:26+00:00","article_modified_time":"2022-12-06T08:01:38+00:00","author":"St\u00e9phane Savorgnano","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Savorgnano","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/"},"author":{"name":"St\u00e9phane Savorgnano","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"headline":"Patch a SQL Server instance automatically with PowerShell","datePublished":"2022-11-11T14:00:26+00:00","dateModified":"2022-12-06T08:01:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/"},"wordCount":385,"commentCount":6,"keywords":["Apply CU","apply SP","automatic","dbatools","Patching","PowerShell","SQL Server"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/","name":"Patch a SQL Server instance automatically with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-11-11T14:00:26+00:00","dateModified":"2022-12-06T08:01:38+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"description":"Patching SQL Server to last CU SP with Powershell and dbatools","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/patch-a-sql-server-instance-automatically-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Patch a SQL Server instance automatically 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 &amp; 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\/20420","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=20420"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20420\/revisions"}],"predecessor-version":[{"id":20925,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20420\/revisions\/20925"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=20420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=20420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=20420"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=20420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}