Patch a SQL Server instance automatically with PowerShell

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 the server where we will patch our instances:

$OScred = Get-Credential;

Once done we will have to find the instances located on the server. Here multiple possibilities:

• you can create just a list with your instances $InstanceList = @(‘Thor90\SQL19_1′,’Thor91\SQL19_1’);
• you can search the instance on your server with $InstanceList = Get-DbaService | where-object { $_.ServiceType -eq ‘Engine’ -and $_.State -eq ‘Running’ };
• you can also select your instance in your CMS with $InstanceList = Get-DbaRegServer -SqlInstance ‘Thor90\CMS’ -Group “Prod\SQL2019”; (my case)

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.
To patch the instance, I will use 2 dbatools cmdlets which are:

• Get-DbaBuildReference: 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
• Update_DbaInstance: will start a process which will update a SQL Server instance to a specified version
  This cmdlet is really powerful with lots of parameter, possible options and also risks, I let you go through its definition.
  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.

I’ll use also some logging cmdlets wrote by myself which are Out-Log, Add-Warning and Add-Error.

The loop on all my instance list will look like:

ForEach ($Instance in $InstanceList){
     Out-Log -Message "Patching instance $($Instance.InstanceName)" -LogFile $LogFile
        
    $connection = Test-DBAConnection -SqlInstance $instance.InstanceName -WarningVariable warningvar;
    Add-Warning -Warning $Warningvar;
    If ($connection -and $connection.ConnectSuccess) {
        Out-Log -Message "Connection to instance $($Instance.InstanceName) succeeded" -LogFile $LogFile

        #Update the instance build which is used by the cmdlet Update-DbaInstance
        Out-Log -Message "Update the build of the instance $($Instance.InstanceName)" -LogFile $LogFile
        Get-DbaBuildReference -SqlInstance $instance.InstanceName -Update -WarningVariable warningvar;
        Add-Warning -Warning $Warningvar;

        Out-Log -Message "Start patching of the instance $Instance.InstanceName" -LogFile $LogFile
        try {
            $res = Update-DbaInstance -ComputerName $Instance.ComputerName -InstanceName $Instance.InstanceName -credential $OScred -Version CU16 -Path \\Thor90\sources\SQL2019 -Restart -Confirm:$false -WarningVariable warningvar;
            Add-Warning -Warning $Warningvar;
        }
        catch {
            Add-Error -Message "Patching of the instance $instance$Instance.InstanceName not possible, Exception: $($error[0].Exception.Message)";
        }

    }
    Else {
        Add-Error -Message "Impossible to connect to the instance $instance$Instance.InstanceName.";
    }
}

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.
Lots of scenario can be covered by using this cmdlet, I let you playing with it, but on your test environment first 😉