Dropped the Distibution database before disabling publishing and distribution, how to proceed?

Question

I'm setting up my first Distributor database on SQL Server 2012. I successfully created one and then wanted to blow it away and perform the task again just for practice.

This was a brand new Distributor and nothing had been done with it yet.

In my naivete I ran the the following commands before disabling the publishing and distribution:

alter database distribution set offline;
drop database distribution;

Now when I try to disable it, I get the following error:

SQL Server could not disable publishing and distribution on '<server>'.
------------------------------
Database 'distribution' does not exist. Make sure that the name is entered correctly.
Changed database context to 'master'. (Microsoft SQL Server, Error: 911)

Is there a way I can fix this?

Answer 1

I Googled it -- http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/ -- try to follow his advice!

Answer 2

The good thing is that you offline the database and then dropped it. So the files will be around. Just attach them and you will be good to go.

Always backup your database before doing anything like modifying, dropping, etc.

Also, How to Rebuild the Distribution Database KB describes the steps if you dont have the files as well.

Answer 3

I have been the same issue faced and I used the below delete command and tried to remove the Distribution.

select * from msdb.dbo.MSdistpublishers

delete from msdb.dbo.MSdistpublishers