'Is there a way to completely reset a SQL Server instance without uninstalling?

This is using Microsoft SQL Server 2012.

I have a program that has multiple versions, each one has some schema changes to the underlying SQL database. In an attempt to do some manual upgrade, some databases from the current version were detached or deleted, and some backups were restored. During the trial and error an unfollowable mess of attaching/restoring/detaching/deleting happened to multiple databases.

We are now trying to start from scratch, we deleted all databases from the instance and tried to rerun the installer. It keeps failing at database creation scripts. I'm unable to easily pinpoint what part of the creation script is failing, but my hunch is that it's related to some sort of objects getting left in the sys tables, but not actually being in the instance so that the database creation scripts skip steps because they think something exists (they are checking the sys tables to determine existence). To be honest, I'm not even sure if that's possible.

Long story short, we are pressed for time and trying to figure out if there is a way we can "factory reset" the existing SQL instance or if we need to find a way to get a SQL installer back on the system and remove the existing instance and add a new one.

sqlsql-serverdatabase


Solution 1:[1]

I think you are going to be forced to do a re-install, and you might luck out with the repair option.

If you are going to have to go that route, reinstall, I would be sure to delete all remaining left over files from the master and temp database just to be safe.

For more information: MSDN Uninstall

I would also take this opportunity to evaluate your backup strategy and implement source control for your SQL Server objects, should you not have such things in place currently.

Solution 2:[2]

If you have a copy of master, model, and msdb, and can restore them, you'll have what you need. You probably won't have to reinstall the binaries (for what you're describing). Without those backups, you'll have to reinstall SQL Server.

https://msdn.microsoft.com/en-us/library/ms190679.aspx

Solution 3:[3]

Putting my answer here years later, but it might help somebody else.

!!! Before attempting any of the below, make sure:
!!! -- either make a clone of the VM in question, work from that
!!! -- if no clone possible: backup your databases and its procedures etc. 

As a first option/bet, selecting a System recovery point from 
within Windows might be the way.
(On any Windows you get to work with, check/enable System Recovery as 
the first step!)

A second way might be to have a dedicated 3rd party uninstaller clean 
up MSSQL (and all the other MSSQL programs?) *completely*, 
before doing a reinstall.
If you have an incompleted uninstall like I do (see below), you could
do a reinstall, and remove the new instance with the dedicated 3rd 
party installer.

A third way might be to locate the install directory and wipe that
completely, then clean up the registry with a registry cleaner.

My case (2021, Win10, MSSQL 2019 dev edition):
I messed up my MSSQL by creating a second user with the same name/pass
within MSSQL, but with no rights yet. In effect, I overwrote/reset 
my admin account to have zero rights. Pretty dumb (and MSSQL should
have warned/refused).
Neither a reinstall of MSSQL(,SSMS) MSSQL wipes the user(!?).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Neo
Solution 2 Dave Markle
Solution 3 mistige

Related Questions