Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijaysrnid
Starting Member
2 Posts |
 Posted - 2014-03-11 : 14:45:52
|
| We had accidentally deleted the distribution database from a SQL instance with publication enabled for few databases. A new distribution database was restored using a backup from another instance(of the same version 10.50.4000). But when we try to disable the distribution (from the context menu of replication item in SSMS), we get this error: sp_MSdrop_logreader_agent can only be executed in the "distribution" database.Is something still missing in the distribution database? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-11 : 14:54:06
|
| We had a similar issue a few years ago. My recollection of the case that I opened with Microsoft is that I had to create a new distribution database with a different name. I called it distribution2.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
vijaysrnid
Starting Member
2 Posts |
Posted - 2014-03-13 : 03:01:35
|
| tkizer, thank you for reply.The restored distribution database was not marked as one used for distribution. We had a backup of master database(taken prior to deletion of actual distribution DB). We restored it and the "Disable Distribution" wizard was able to identify the new distribution database.We were then able to setup a clean distribution.Note: I guess a database is marked as a distribution database using the is_distributor column in master.sys.databases table |
|
|
|
|
|
|
Unable to disable distribution/publication