How to remove SQL Server replication attributes from all replicated objects in a restored Systems Management Server 2003 site database

This article has been archived. It is offered "as is" and will no longer be updated.
INTRODUCTION
This article describes how to remove Microsoft SQL Server replication attributes from all replicated objects in a restored Microsoft Systems Management Server (SMS) 2003 site database.

You must remove SQL Server replication attributes before you do the following:
  • Upgrade your previously restored SMS site database.
  • Test an SMS site database that has been restored to a different server by using the SMS Setup program with the /testdbupgrade switch.

MORE INFORMATION

How to remove SQL Server replication attributes from all replicated objects in a restored SMS site database

We do not recommend that you, or any user, change system tables directly. For example, do not try to modify system tables by using DELETE, UPDATE, or INSERT statements or by using user-defined triggers.

To remove the SQL Server replication attributes, follow these steps.

Note The following procedure depends on SQL Server system tables. The structure of these tables may vary with different SQL Server versions.
  1. Click Start, point to Programs, click Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, click SQL Query Analyzer on the Tools menu.
  3. Above the Query window, click the Database list, and then click the restored SMS database.
  4. In the Query window in the right pane, copy and paste the following code.
    sp_configure 'allow updates', 1goreconfigure with overridegobegin transactionupdate sysobjects set replinfo = '0' where replinfo >'0'
  5. Click the Parse Query button to look for errors in the code.
  6. Click the Execute Query button to run the query on the restored database.
  7. Verify that all objects in the restored database's sysobjects table have the replinfo attribute set to 0 (zero), and then verify that only one row was affected. If the intended row in the sysobjects table was updated, commit or roll back the transaction by using one of the following commands.
    commit transactiongo
    rollback transactiongo
  8. Run the following code to set the allow updates attribute back to 0 (zero).
    sp_configure 'allow updates', 0goreconfigure with overridego
You can re-create the replication objects after you upgrade the SMS site database or after you run the the SMS Setup program with the /testdbupgrade switch to test the database.
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
829828 Setup /testdbupgrade does not succeed on a restored site server database
326352 You receive error 3724 when you drop a non-replicated object
Properties

Article ID: 835860 - Last Review: 01/16/2015 01:34:35 - Revision: 1.2

Microsoft Systems Management Server 2003

  • kbnosurvey kbarchive kbhowto KB835860
Feedback