Undo sp_adddistributiondb

[foxdev]

Short version: sp_adddistributiondb ran on wrong database; how to "undo" that.

I goofed. I had a script that sets up replication on our test box; after a successful test, I modified the server and database names within the script to run on our production box.

Except I goofed on the database name for the call to SP_AddDistributionDB: rather than passing it the name of a new, separate distribution database, I gave it the name of a production database (the database that we want to publish).

Severe panic when the database no longer shows in the list of databases. Fortunately, it is still there, but now flagged as a "system" database in SSMS.

Either my search-fu is broken, or no one else has ever had this problem. The database is operational, but clearly isn't quite right.

How can I undo whatever gets done by sp_adddistributiondb (assumedly to both the database itself as well as various system databases/tables)? The corollary to sp_adddistributiondb is sp_dropdistributiondb; my fear is that would drop the entire database, but maybe it's more benign than that.

Any advice?

 

[SQLBill]

According to BOL, it seems that command will drop the distribution database and since it seems you turned your production database into the distributor....it might get dropped.

I've never had this happen so I don't have a solution, but I do suggest that you backup the database just before you run any commands to undo your work. That way if the database is dropped, you can always restore it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[foxdev]

Thank you for the reply, SQLBill. The database is part of a regular backup scheme, and we're making additional backups just in case.

I think the main issue are the system databases/tables. When we restore this database on a different server (something we do every day anyway to serve as a reporting database), it looks completely normal, and is not flagged as a distributor or system database.

 

[foxdev]

Just a follow-up in case anyone else encounters this issue: the answer indeed was to run sp_dropdistributiondb. Part of the sp_adddistributiondb sproc is to flag the distribution database as having been pre-existing, or created by the sp_adddistributiondb; when sp_dropdistributiondb runs it checks that flag, if set as pre-existing, it does not drop the database.

Running sp_dropdistributiondb got the database back to normalish. I had to manually set the recovery mode back to "full". In addition, there are several leftover distribution objects in the database; these are generally benign, but I ended up going through additional steps to remove them since it seemed they were preventing me from implementing replication on that database.