Automating SQL Server Patching With PowerShell

Patching twenty servers by hand on a Saturday is how mistakes get made at 2 a.m. Automating SQL Server patching with PowerShell makes a tested step repeatable, as long as the script checks the package, stops cleanly on failure and proves the result from the running instance.

Start Automating SQL Server Patching With a Procedure You Already Trust

Do not start automating SQL Server patching with a script that patches every server in the fleet. Rehearse the manual procedure on a lab instance first. Identify the exact CU package, target instances, restart plan, backup evidence, and post patch checks. PowerShell should enforce that approved sequence.

I never automate a procedure I have not done by hand at least twice. Automation makes a good process fast, and it makes a bad one fast too.

Separate orchestration from approval. The script can confirm prerequisites and run Setup. A person still decides which CU the application has tested and when production can move. Keep the approved package and target build in a change record.

Choose whether the installer patches one named instance or all instances on a host. The AllInstances switch is powerful and easy to misuse on a shared machine. A safe script takes an explicit instance name and refuses to continue if it cannot confirm the target.

Stage and Verify the Package Before Automating SQL Server Patching

Download the CU from the official Microsoft release source through your approved process. Keep it in a controlled local folder. Do not have every production server search the internet during the maintenance window. Record the package name and hash in the change ticket.

Before execution, check that the file exists, its signature is valid, and its SHA256 hash matches the approved value. A hash mismatch is a stop condition. A valid signature helps confirm the publisher, while the approved hash confirms you are running the exact file tested in staging.

The example uses local paths. Fill the expected hash from your approved package record. It deliberately throws on a mismatch. Do not replace the placeholder with a made up hash.

# PowerShell
$package = 'C:\SqlPatch\ApprovedCU.exe'
$expectedHash = 'REPLACE_WITH_APPROVED_SHA256'
if (-not (Test-Path -LiteralPath $package)) {
    throw 'Approved CU package is missing.'
}
$actualHash = (Get-FileHash -LiteralPath $package -Algorithm SHA256).Hash
if ($actualHash -ne $expectedHash) {
    throw 'CU package hash does not match approval.'
}

Record the Build Before Setup Runs

Query ProductVersion from the target instance and save it with the host, instance name, and time. Confirm the current build is the one your test used. If the server changed since approval, stop. The patch sequence now has a different starting point.

Check backup completion and restore readiness through your established process. Check free disk space, pending restart state, and running jobs. A script should not silently cancel a backup or force a restart because the calendar says it is time.

For an availability group, the orchestration needs replica awareness. Patch secondaries first and verify synchronization before any failover. A simple remote loop that patches hosts alphabetically is not a rolling update plan.

SELECT
    @@SERVERNAME AS ConnectedInstance,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel;

Run Setup and Wait for It

SQL Server update packages support quiet installation switches. Pass the selected instance explicitly and wait for the process to exit. Capture the exit code. The exact switches should be reviewed against the release’s Setup documentation and tested with the chosen package.

Do not use Start-Process without Wait and then report success because PowerShell reached the next line. An installer can take time and can fail after launching. The process result is one signal; the setup summary and running build are separate checks.

A nonzero exit code stops the rollout. Record the code and log location, then investigate. Do not automatically retry on every error. A failed patch can leave a component state that needs a specific recovery action.

# PowerShell
$package = 'C:\SqlPatch\ApprovedCU.exe'
$arguments = @(
    '/quiet',
    '/IAcceptSQLServerLicenseTerms',
    '/Action=Patch',
    '/InstanceName=MSSQLSERVER'
)
$process = Start-Process -FilePath $package -ArgumentList $arguments -Wait -PassThru
if ($process.ExitCode -ne 0) {
    throw "SQL Server Setup failed with exit code $($process.ExitCode)."
}

Parse What Setup Reported

SQL Server Setup writes a summary and detailed logs under its Setup Bootstrap folder. Find the log folder for the run you just started. Review the overall result and each feature result. A successful Database Engine component does not erase a failure in a shared feature.

Preserve the summary file with the change record. If setup reports failure, capture Detail.txt and the feature specific log before another attempt. The script can collect the files, but a DBA should read the failure before choosing the next action.

Avoid parsing a single word such as “success” from the first matching line. The summary includes multiple sections. Use the documented result fields and verify each intended component. Keep a manual review gate until your parser has been tested against both successful and failed runs.

Verify the Running Instance

Reconnect after any required restart. Query the full ProductVersion and compare it with the approved target. Check Agent, database state, error log, backup jobs, and application paths. The script can automate some checks, while application owners validate business behavior.

If the version is wrong, stop the fleet rollout. Do not label the machine patched because the process returned zero. A service can fail to start or a connection can land on the wrong instance. Include machine and instance identity in the verification output.

Treat success as a set of conditions. Setup completed, intended components succeeded, the engine reports the target build, services are healthy, and application checks pass. Then move to the next server.

SELECT
    @@SERVERNAME AS ConnectedInstance,
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel;

Make Failure Boring When Automating SQL Server Patching

Write each stage to a local log with timestamps. On failure, save the package identity, exit code, setup log path, and last completed checkpoint. Alert the operator and leave the next server untouched. That is a better automation outcome than a fast script that spreads uncertainty.

I want a failed run to look exactly like a successful one, except for the status line. What will your script do when setup returns an exit code you have never seen? Decide that before the first unattended night.

The best patch script I have seen was not clever at all. It stopped at the first surprise and said exactly why.

Test deliberate failures in a lab: missing package, wrong hash, wrong instance, setup error, and version mismatch. Confirm the script stops every time. A safe stop is a feature, not an inconvenience.

After a successful pilot, expand in small groups. Keep an approval gate between groups and a way to pause the schedule. PowerShell is excellent at repeating a known procedure. It should never replace the evidence that makes the procedure safe.

Keep the script output readable during a maintenance window. Print the instance, approved package, stage, and result. Save verbose logs separately. An operator needs a clear stop signal more than a wall of command output.

Related reading on this blog: How to Patch SQL Server Without a Bad Morning and PowerShell: How to Install dbatools?.

Patch automation is not a faster installer, it is a repeatable sequence that knows when to stop.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.