Restore of Replicated Database fails with “Cannot drop the table because it is being used for replication.”

Asked May 14 '13 at 03:21

Active May 24 '13 at 05:24

Viewed 702 times

0

published database restoration failed once you setup transactional Replication using system Store procedures. Few work around to this problem is restore with KEEP_replication option and than drop the publication and subscription.

Is there anything we can do while setting up the Transactional replication using System SP that can avoid this issue ?

transactional-replication

asked May 14 '13 at 03:21
Piyush Patel

71

3

12

1 Answers1

0

Use below query to avoid replication related SP in Transactional Replication.

SELECT name 'ROUTINE_NAME','T' 'ROUTINE_TYPE' FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 1 AND name not like 'MSpeer%'

answered May 24 '13 at 05:24
Piyush Patel

71

3

12

Question

published database restoration failed once you setup transactional Replication using system Store procedures. Few work around to this problem is restore with KEEP_replication option and than drop the publication and subscription.

Is there anything we can do while setting up the Transactional replication using System SP that can avoid this issue ?

score 0 · Accepted Answer · answered May 24 '13 at 05:24

0

Use below query to avoid replication related SP in Transactional Replication.

SELECT name 'ROUTINE_NAME','T' 'ROUTINE_TYPE' FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 1 AND name not like 'MSpeer%'

answered May 24 '13 at 05:24

Piyush Patel

71
3
12