how to delete the unwanted publisher and subscriber

Question

Post reply

how to delete the unwanted publisher and subscriber

MAK-1128556

SSCarpal Tunnel

Points: 4594
More actions
August 17, 2010 at 1:54 pm

#225963

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.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16213 More actions · Answer 1

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

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 🙂

odanwada SSCrazy Points: 2133 More actions · Answer 3

Try using the following Syntax.

sp_removedbreplication [ [ @dbname = ] 'dbname' ]

[ , [ @type = ] type ]

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 4

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

mayank_tyagi Old Hand Points: 325 More actions · Answer 5

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

crazy4sql SSCoach Points: 19590 More actions · Answer 6

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

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 7

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.

crazy4sql SSCoach Points: 19590 More actions · Answer 8

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

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 9

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.

sjimmo SSChampion Points: 11139 More actions · Answer 10

There are 2 options:

1. Run sp_MSunmarkreplinfo which takes a tablename as a parameter.

2. Alternatively, setting sys.objects to 0 in sys.objects for the particular table should do it.

Finally, running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is not also configured as a publisher.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 11

MAK-1128556

SSCarpal Tunnel

Points: 4594

August 19, 2010 at 8:44 am

#1209172

still not deleting, no distributor

sjimmo SSChampion Points: 11139 More actions · Answer 12

I the database that you are having a problem with run

SELECT *
FROM sys.[objects]
WHERE [is_published] = 1

Provide the results.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 13

MAK-1128556

SSCarpal Tunnel

Points: 4594

August 19, 2010 at 9:21 am

#1209202

I got 626 row back

sjimmo SSChampion Points: 11139 More actions · Answer 14

MAK - you need to first get those all set to 0.

See my earlier post

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan