Knowledge Base

FIX: Merge System Tables not Dropped When Restoring Merge-Published DB Over Non-published DB Across Servers

Article ID: 243377

Article Last Modified on 3/14/2006

APPLIES TO

Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q243377

BUG #: 56522 (SQLBUG_70)

SYMPTOMS

When restoring the backup of a database, which is enabled for Transactional and Merge Replication (Sysdatabases.Category = 5), to a non-published database on a different server, the merge replication system tables are not dropped and the user tables on the destination server's database cannot be dropped. If the backup is restored on the same server (different database), then the restore works fine and the replication information is removed.

Also, the restore works fine across servers when the source database is enabled either for Transactional or Merge Replication only (Sysdatabases.Category = 1 OR 4).

WORKAROUND

To work around this problem, you can either:

Enable the source database for Transactional or Merge Replication alone (Sysdatabases.Category = 1 OR 4).

-or-

Drop the merge system tables using the DROP TABLE syntax.NOTE: Contact Microsoft Product Support Services if you need to drop the user tables and are unable to do so.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Steps To Reproduce The Problem

Enable the database for both Transactional and Merge Replication (Sysdatabases.Category = 5).
The published database contains Conflict_<table_name> tables and MSmerge_% tables.
Backup the database.
Restore the backup to a database on a different server. The name of the database on the destination server does not matter.

RESULT: The replication information is not completely removed. The merge replication system tables are present. Following is a list of the tables that are present:

   conflict_<table_name>
   MSmerge_contents
   MSmerge_delete_conflicts
   MSmerge_genhistory
   MSmerge_replinfo
   MSmerge_tombstone

If you try to drop these tables manually from the SQL Enterprise Manager (SEM), you get the following error message:

Error 20515: [SQL-DMO]System tables cannot be altered, dropped or otherwise modified.

However, you can drop these tables using the DROP TABLE syntax from the SQL Query Analyzer.

If you try to drop the user tables in the destination database, you get this error message:

Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table '<table_name>' because it is published for replication.

Contact Microsoft Product Support Services if you need to drop the user tables and are unable to do so.

Additional query words: Error 3724

Keywords: kbbug kbfix KB243377

Microsoft KB Archive/243377

Contents