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

Sateesh Yele
Microsoft SQL Server Support

Issue:

Restore of a replicated database fails with the following message.

Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line 32
Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication.

Consider the following scenario.

Transactional Replication is set on the database from a script and sp_addarticle was used to Publish some of the replication systems tables (in this case, MSpeer_lsns table). When you try to restore the database, it would fail with the error above message. If you specify KEEP_REPLICATION setting, database comes online but you cannot remove replication objects using sp_removedbreplication procedure.

Cause:

Is it recommended not to replicate any replication metadata tables. The Create Publication Wizard does not allow system tables to be replicated.  These same checks do not occur when you configure Replication using system stored procedures.

In the source database, if any of the replication system tables are marked for replication (like MSpeer_lsns..) using the stored procedures, restore of this database would fail with the above error message.

Resolution:

1. Restore the database with KEEP_REPLICATION setting.
2. Drop all subscriptions using sp_dropsubscription
3. Drop all the articles using sp_droparticle
4. Remove replication using sp_removedbreplication stored procedure.

posted by: Chris Skorlinski