SQL Server 2012 – How to Reset to Default Configuration

configurationsql-server-2012

I am trying to learn to mirror a test database and in the course of doing this, was typing in some commands into a query box. While trying to create a database mirroring endpoint I submitted a command with a typo. I was returned an error. I tried to resubmit a corrected command, but then was told that the endpoint already existed. So then I figured I'd just delete my test database and restart. I deleted the database in my primary SQL Server, but now my mirror SQL Server shows my test database as restoring, and I am unable to delete that one.

Is there a way to undo everything that I have done, going back to a installation default of my SQL Server? There is nothing I need to save, I'm just trying to learn.

Best Answer

To completely reset everything would involve uninstalling/reinstalling SQL Server. But this is overkill. Instead it sounds like you have two things you need to do.

Thing 1

First thing you should do is clean up your endpoints. I would do this on both your primary and mirror server. The DROP ENDPOINT command is straightforward. If you need to see what endpoints are currently out there, you can select from sys.endpoints.

Thing 2

Drop your old database. If it's stuck in recovery, the first thing to check is your SQL Error Log on the mirroring target and make sure it isn't actually doing anything. You want to look for messages containing the word "Recovery" and "Step n of 3". If these are there, it's still in the process of restoring. Otherwise, you just need to complete the recovery with:

RESTORE DATABASE [foo] WITH RECOVERY;

Once you have completed recovery and the database is online, you should be able to drop it.

Related Question