Removing Replication

[Peter Daniels]

I'm a replication newbie and I used sp_removedbreplication as a first
step to try to tear down replication for one of my DBs. It did remove
all of the replication tables from my user DB, but all of the agents
are still there, etc.

I've tried running sp_droppublication, but of course it fails because
the syspublications table is gone from my user DB.

I can't remove publishing altogether because I have several other DBs
replcating and tearing them down is not an option. What can I do to
manually clean up the rest of this mess?

TIA

-Peter

[Michael Shao [MSFT]]

Hi Peter,

Please try to perform the following statements using Query Analyzer on your
side.

Syntax:

[distribution].[dbo].[sp_MSdrop_publication] @publisher, @publisher_db,
@publication

Example:

[distribution].[dbo].[sp_MSdrop_publication] 'Testpublisher',
'testdatabase', 'testpublication'

'Testpublisher' is the name of publisher.
'testdatabase' is the name of publisher database.
'testpublication' is the name of publication.

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[Peter Daniels]

Thanks for the idea, but I have already performed a manual cleanup of
the data in the distribution, msdb, and my user databases. Here is the
SQL I used:

-- publisher_db = 'FULFILLMENT'
-- publication_id = 6
-- publisher_id = 0

DECLARE @strDBName sysname

SET @strDBName = 'FULFILLMENT'

-- MSsubscriptions
DELETE FROM
distribution.dbo.MSSubscriptions
WHERE
publisher_db = @strDBName

-- MSArticles
DELETE FROM
distribution.dbo.MSArticles
WHERE
publisher_db = @strDBName


-- logreader agent cleanup *******************************************
-- Msrepl_errors for logreader agents
DELETE distribution.dbo.MSrepl_errors
FROM
distribution.dbo.Msrepl_errors AS re
INNER JOIN distribution.dbo.[MSlogreader_history] AS lh ON
(re.[id] = lh.agent_id)
INNER JOIN distribution.dbo.MSLogreader_agents AS la ON
(lh.agent_id = la.[id])
WHERE
la.publisher_db = @strDBName

-- MSlogreader_history
DELETE distribution.dbo.[MSlogreader_history]
FROM
distribution.dbo.[MSlogreader_history] AS lh
INNER JOIN distribution.dbo.MSLogreader_agents AS la ON
(lh.agent_id = la.[id])
WHERE
la.publisher_db = @strDBName

--MSlogreader_agents
DELETE FROM
distribution.dbo.MSlogreader_agents
WHERE
publisher_db = @strDBName


-- Snapshot agent cleanup *******************************************
-- Msrepl_errors for snapshot agents
DELETE distribution.dbo.Msrepl_errors
FROM
distribution.dbo.Msrepl_errors AS re
INNER JOIN distribution.dbo.[MSsnapshot_history] AS sh ON
(re.[id] = sh.agent_id)
INNER JOIN distribution.dbo.MSsnapshot_agents AS sa ON
(sh.agent_id = sa.[id])
WHERE
sa.publisher_db = @strDBName

-- MSsnapshot_history
DELETE distribution.dbo.[MSsnapshot_history]
FROM
distribution.dbo.[MSsnapshot_history] AS sh
INNER JOIN distribution.dbo.MSsnapshot_agents AS sa ON
(sh.agent_id = sa.[id])
WHERE
sa.publisher_db = @strDBName

--MSsnapshot_agents
DELETE FROM
distribution.dbo.MSsnapshot_agents
WHERE
publisher_db = @strDBName


-- distribution agent cleanup
*******************************************
-- Msrepl_errors for distribution agents
DELETE distribution.dbo.Msrepl_errors
FROM
distribution.dbo.Msrepl_errors AS re
INNER JOIN distribution.dbo.[MSdistribution_history] AS dh ON
(re.[id] = dh.agent_id)
INNER JOIN distribution.dbo.MSdistribution_agents AS da ON
(dh.agent_id = da.[id])
WHERE
da.publisher_db = @strDBName

-- MSdistribution_history
DELETE distribution.dbo.[MSdistribution_history]
FROM
distribution.dbo.[MSdistribution_history] AS dh
INNER JOIN distribution.dbo.MSdistribution_agents AS da ON
(dh.agent_id = da.[id])
WHERE
da.publisher_db = @strDBName

--MSdistribution_agents
DELETE FROM
distribution.dbo.MSdistribution_agents
WHERE
publisher_db = @strDBName


-- MSrepl_identity_range
DELETE FROM
distribution.dbo.MSrepl_identity_range
WHERE
publisher_db = @strDBName

-- MSrepl_backup_lsns
DELETE distribution.dbo.MSrepl_backup_lsns
FROM
distribution.dbo.MSrepl_backup_lsns AS bl
INNER JOIN distribution.dbo.MSPublisher_databases AS pd ON
(bl.publisher_database_id = pd.[id])
WHERE
pd.publisher_db = @strDBName

-- MSrepl_commands
DELETE distribution.dbo.MSrepl_commands
FROM
distribution.dbo.MSrepl_commands AS rc
INNER JOIN distribution.dbo.MSpublisher_databases AS pd ON
(rc.publisher_database_id = pd.[id])
WHERE
pd.publisher_db = @strDBName

--MSrepl_transactions
DELETE distribution.dbo.MSrepl_transactions
FROM
distribution.dbo.MSrepl_transactions AS rt
INNER JOIN distribution.dbo.MSpublisher_databases AS pd ON
(rt.publisher_database_id = pd.[id])
WHERE
pd.publisher_db = @strDBName

-- MSrepl_originators
DELETE distribution.dbo.MSrepl_originators
FROM
distribution.dbo.MSrepl_originators AS ro
INNER JOIN distribution.dbo.MSpublisher_databases AS pd ON
(ro.publisher_database_id = pd.[id])
WHERE
pd.publisher_db = @strDBName


DELETE distribution.dbo.MSpublication_access
FROM
distribution.dbo.MSpublication_access AS pa
INNER JOIN distribution.dbo.MSpublisher_databases AS pd ON
(pa.publication_id = pd.[id])
WHERE
pd.publication_db = @strDBName


-- MSPublications
DELETE FROM
distribution.dbo.MSPublications
WHERE
publisher_db = @strDBName

-- MSPublisher_databases
DELETE FROM
distribution.dbo.MSPublisher_databases
WHERE
publisher_db = @strDBName

-- msdb
DELETE FROM
msdb.dbo.sysreplicationalerts
WHERE
publisher_db = @strDBName

-- tempdb - this will clean up the infamous Red Xs!
DELETE FROM tempdb.dbo.MSreplication_agent_status
WHERE
publisher_db = @strDBName


-- Updates***************************************************

-- More cleanup in the user DB - set replinfo flag
Update sysobjects
Set replinfo = 0
Where replinfo > 0

-- sysdatabases
Update master.dbo.sysdatabases
Set category=0
Where [name] = @strDBName



-Peter Daniels
-DBA/Data Architect
-***@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!