Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

how to delete the unwanted publisher and subscriber Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 1:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 7:44 AM
Points: 176, Visits: 290
we are running SQL 2005,

I create a transactional replication.
when I restore the publisher db “A” into the subscriber server with different name “B” all the publisher that I have in publisher show up into the subscriber server but these publisher associated with “B” db, Also these publisher have no distributor.
Now I try to delete these but I am getting error “there is no distributor” attached to the publisher.



Post #970701
Posted Tuesday, August 17, 2010 3:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 2,742, Visits: 2,953
Have you tried running sp_removedbreplication on database "B" once it was restored to server "B"?

http://msdn.microsoft.com/en-us/library/ms188734.aspx
Post #970766
Posted Tuesday, August 17, 2010 8:33 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 734, Visits: 3,561
MAK-1128556 (8/17/2010)
we are running SQL 2005,

I create a transactional replication.
when I restore the publisher db “A” into the subscriber server with different name “B” all the publisher that I have in publisher show up into the subscriber server but these publisher associated with “B” db, Also these publisher have no distributor.
Now I try to delete these but I am getting error “there is no distributor” attached to the publisher.





Can you elaborate what are you trying to achieve by doing this ?

This will help us to answer more efficiently !

HTH,
Cheers


______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #970846
Posted Tuesday, August 17, 2010 10:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:47 PM
Points: 1,109, Visits: 347
Try using the following Syntax.

sp_removedbreplication [ [ @dbname = ] 'dbname' ]
[ , [ @type = ] type ]
Post #970879
Posted Wednesday, August 18, 2010 6:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 7:44 AM
Points: 176, Visits: 290
Thanks for reply,
I try but getting error "Distributor server is not assign"
I try to delete unwanted publishers that copy into the subscriber when I restore the db into the subscriber but these publishere are associcated with subscribere db
Post #971090
Posted Thursday, August 19, 2010 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 23, 2011 8:19 PM
Points: 175, Visits: 261
Hello Mak ,

I think you had restored the database by this procedure :

1- Take instance offline
2- Move mdf ,ndf and ldf files to target server
3- Reattach these files to restore database

IF (I AM Right)
Begin

solution 1

1- configure Dummy replication to create distribution DB on Target server
2- then u can run sp_dbremovereplication or u can manually remove the Publication .
3- Disable the Dummy replication


End



Post #971717
Posted Thursday, August 19, 2010 4:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 10:45 PM
Points: 876, Visits: 3,951
you will be getting this error as long your agents are running.
Go in activity monitor and kill those connection.
then right click on replication on SSMS and select disable publishing and distributor.... and follow the screen.


----------
Ashish
Post #971735
Posted Thursday, August 19, 2010 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 7:44 AM
Points: 176, Visits: 290
Thanks for reply,

1. I can't use Subscriber server as a distributor.
2. there is no agent job is running on Subscriber server because there is no distributor.
Post #971820
Posted Thursday, August 19, 2010 6:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 10:45 PM
Points: 876, Visits: 3,951
what i can assume is, you restored the backup after replication configuration(if this is not the case then dont read further).
Can you try to restore some old backup of that database on suscriber and then give a try.


----------
Ashish
Post #971832
Posted Thursday, August 19, 2010 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 7:44 AM
Points: 176, Visits: 290
first I create a publisher then take a backup of the db and restore into the Subscriber and got unwanted publisher on my subscriber box.
Post #971839
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse