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.
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.
- Click Start, point to Programs, click Microsoft SQL Server, and then click Enterprise Manager.
- In SQL Server Enterprise Manager, click SQL Query Analyzer on the Tools menu.
- Above the Query window, click the Database list, and then click the restored SMS database.
- In the Query window in the right pane, copy and paste the following code.
sp_configure 'allow updates', 1
reconfigure with override
update sysobjects set replinfo = '0' where replinfo >'0'
- Click the Parse Query button to look for errors in the code.
- Click the Execute Query button to run the query on the restored database.
- 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.
- Run the following code to set the allow updates attribute back to 0 (zero).
sp_configure 'allow updates', 0
reconfigure with override
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.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
Setup /testdbupgrade does not succeed on a restored site server database
You receive error 3724 when you drop a non-replicated object
Article ID: 835860 - Last Review: October 27, 2006 - Revision: 1.2
- Microsoft Systems Management Server 2003