Repair a SQL Server instance

In this post I’ll go over the steps to repair a SQL Server instance, via both GUI and PowerShell, and when you might need to do it.

I’d also like to thank the Windows team over at MS for giving me the opportunity to write this blog post with their recent 24H2 update for Windows 11 Professional.

Why you might need to repair a SQL Server instance

I’ve needed to run the repair process in the following cases:

• An instance or a SQL Server installation’s components become corrupted or missing after OS patching.
  One of the most common examples I’ve ran into is SQL Server Configuration Manager being gone from the machine.
  Not to be confused with database corruption.
• A failed or cancelled SQL Server instance upgrade or patching.
• SQL Server failing to uninstall.
  From what I’ve seen, this tends to be a side-effect of the first scenario in this list going undetected.

Prerequisites

In order to proceed with the SQL Server instance repair process you’ll need the following:

1. Local Administrator role membership on the machine.
2. The installation media/kit for the version of SQL Server that you intend to repair
3. Some downtime. The instance will not be accessible during the repair process and you’ll also need to restart the machine when done.

Repairing a SQL Server instance

I’ll cover both the GUI and the command line methods.

I’m using SQL Server 2019 in this example but the steps are the same for pretty much any modern version of SQL Server.

Using the GUI

In the installation media folder, locate setup.exe, right click on it and click Run As Administrator.

Note:

1. You might spot some additional files and folders in my screenshot.
   Specifically, CUInstallKit, SSMSInstallKit and the two 2019_ configuration files. They’re not normally part of the SQL Server 2019 installation media, but they are part of my automated SQL Server install via PowerShell.
2. I don’t like the new context (right click) menu, so Microsoft will have to pry the classic one from my cold dead hands.

Once the SQL Server Installation Center window opens, go to the Maintenance menu and then click on Repair.

The repair process goes through some pre-checks and then will prompt for the instance that should be repaired.

I select the instance using the drop-down menu and then click Next.

Afterwards, the Ready to Repair screen will be displayed.

A configuration file with the options used for this run of the repair process will be saved in the path shown in the screenshot, that’s also the location where all the check reports and log files will be saved for this run.

When ready, click on Repair.

Once the repair process finishes, a summary of the repair operation will be shown.
This contains a list of the features/components that were repaired and the status of the operation.

Any possible error messages will be shown in the Details section and more information about the issue is normally found in the Summary log file.

Click Close to close this window, then close the SQL Server Installation Center window and reboot the machine.

Using the command line

I’ll be using PowerShell, but you can use Command Prompt as well, it works the same way.

Open PowerShell as Administrator. And then navigate to where setup.exe is.

And then execute setup.exe while specifying the repair action and the SQL Server instance that it should repair.

Options explained

Once the repair finishes, the machine can be rebooted.

Conclusion

Ending up with a corrupt/broken SQL Server instance isn’t fun, luckily the repair process does a good job of fixing it.