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

Article ID: 835860 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

Back to the top | Give Feedback

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', 1
go
reconfigure with override
go
begin transaction
update 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 transaction
go
    rollback transaction
go
  8. Run the following code to set the allow updates attribute back to 0 (zero). 
    sp_configure 'allow updates', 0
go
reconfigure with override
go
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.
Back to the top | Give Feedback

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
829828
(http://support.microsoft.com/kb/829828/ )
Setup /testdbupgrade does not succeed on a restored site server database
326352
(http://support.microsoft.com/kb/326352/ )
You receive error 3724 when you drop a non-replicated object
Back to the top | Give Feedback

Properties

Article ID: 835860 - Last Review: October 27, 2006 - Revision: 1.2
APPLIES TO
  • Microsoft Systems Management Server 2003
Keywords: 
kbhowto KB835860
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top