June 10, 2009 at 12:56 pm
Dear All,
I have used to drop the replication configuration a stored procedure called sp_removedbreplication , But by mistake which is a big mistake, i have executed this procedure in the Distribution database that dropped many system tables from distribution database that is important for replication like (MSmerge_subscriptions, MSmerge_sessions and others).
The question is how can i restore these tables, because when i create a publication and try to view the log reader agent status it give me the follwing error :
Unable to display agent status.
Invalid object name 'dbo.MSmerge_subscriptions'
pls need help for this...
Thanks,
June 11, 2009 at 5:37 am
ahmad.ghazi (6/10/2009)
Dear All,I have used to drop the replication configuration a stored procedure called sp_removedbreplication , But by mistake which is a big mistake, i have executed this procedure in the Distribution database that dropped many system tables from distribution database that is important for replication like (MSmerge_subscriptions, MSmerge_sessions and others).
The question is how can i restore these tables, because when i create a publication and try to view the log reader agent status it give me the follwing error :
Unable to display agent status.
Invalid object name 'dbo.MSmerge_subscriptions'
One thing confuses me about your story. It seems like you're using merge replication. But merge replication makes no use of the Logreader agent. The logreader agent is only used with transactional replication, so could it be that you're just looking in the wrong place?
In the worst case scenario, that you really miss some system objects I would advice to completely remove replication, disable publishing and distributing. Then you can drop the distribution database and recreate everything from scratch.
[font="Verdana"]Markus Bohse[/font]
June 11, 2009 at 2:48 pm
Thank you Markus for your explanation, Its wokred 🙂
You are right Markus I'm using transactional replication, But why LogReader tell me there is invalid object name 'dbo.MSmerge_subscriptions' !!!
Anyway, What i did is the following:
1) Drop Distribution database using following script:
Use master
Alter DATABASE distribution SET OFFLINE
DROP Database distribution
2) Then i have deleted any related data from msdb and master databases.
3) Add Distribution database and publisher using following script:
USE master
sp_adddistributor @@servername
GO
sp_adddistributiondb 'distribution'
GO
sp_adddistpublisher @@servername, 'distribution'
GO
thats all.
November 8, 2011 at 9:38 am
What did you remove form MSDB and/or master? I am having the same issue as you.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply