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

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.