Hello,

I have restored a Database to Computer B from a Backup of Computer A. The
backuped database was replicated with lots of Articles and subscribers.
The restored Database is full of replication stuff system tables.

Tables like:
conflict_RTM_REPL_MERGE_Aktion_TS_S_AktionAktivitaeten
MS_bi0359AEA771B44176B0A4CB9DED6893D4

Views like:
ctsv_D0F73F1F195A4BC281E283743C2DC9F5

MS_bi0359AEA771B44176B0A4CB9DED6893D4_v_8C3D790A33BF4F729C3DA58C74C52F84
MSbivw_577AB9CED90F4AD79E69B8B6FCC88F16
syncobj_0x4345323445313537
tsvw_14C7F8586B8146F09B8BE08E8A6BB157

Stored Procs like:
sp_sel_577AB9CED90F4AD78C3D790A33BF4F72
sp_ins_339B58386D2741E48C3D790A33BF4F72

"normal" drop table/view in Enterprise Manager wont work with system objects

I want not to copy all User Objects to a new database, because of the amount
of data and the complexity of the structure (lots of dependencies)

Has anybody a clue to get rid of those system objects?

(Why cant system adimistrator do this? I know usualy you wreck the database)


Thanks in advance
Juergen Finke

Have a look at sp_removedbreplication. It'll remove most of these objects
(only run if you really want to try to remove all traces of replication). Any
remaining objects can be removed using the drop command or a custom script.
HTH,
Paul Ibison

Hello Paul
Thanks for your hint.
sp_removedbreplication wont work, but the custom script.
Seems like i'm a little bit addicted to Enterprise Manager and Query
Analyser.
But in almost all cases this savety mechanism works fine for me.

this Script helps me to generate the delete script:
select 'drop view [' + Name + ']' as Name from dbo.sysobjects where
OBJECTPROPERTY(id, N'IsView')=1 and (category=2)AND (base_schema_ver<>0)
UNION ALL
select 'drop procedure [' + Name + ']' from dbo.sysobjects where
OBJECTPROPERTY(id, N'IsProcedure')=1 and (category=2)
UNION ALL
select 'drop table [' + Name + ']' from dbo.sysobjects where
OBJECTPROPERTY(id, N'IsTable')=1 and (category=2050)
Order by Name

A little bit fine tuning (delete some important system procedures)
and work is done.
(to all others, DONT try this at home. It can leave your database INOPERABLE)

Thanks again
Juergen (greetings from Hamburg, Germany)

[quoted text, click to view]