Microsoft KB Archive/829828

= Setup /testdbupgrade does not succeed on a restored site server database =

Article ID: 829828

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003

-





SYMPTOMS
When you run Setup /testdbupgrade to test a database before you upgrade, the test may not succeed. This issue may occur when you run Setup /testdbupgrade on a Systems Management Server 2003 Site Server database that has been restored to another computer.



CAUSE
This issue occurs when the restored Systems Management Server 2003 Site Server database had replication enabled when it was backed up. When you restore the database to another computer, Setup /testdbupgrade tries to drop the replicated objects and then it does not succeed.



WORKAROUND
To work around this problem, you must remove the SQL Replication attributes from all replicated objects on the restored database.

Note The following procedure is dependent on Microsoft SQL Server system tables. The structure of these tables may vary in different SQL Server versions. Microsoft does not recommend that you select directly from the system tables. Generally, Microsoft does 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 replication attributes on the restored database you must run the following code against the restored database. To do so, follow these steps:  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

go

reconfigure with override

go

begin transaction

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.</li>  Verify that confirmed all objects in the restored database's sysobjects table have the replinfo attribute set to 0, and then verify that only one row was affected. If the intended row in the sysobjects table was updated, commit the transaction, or you can roll back the transaction by using the following appropriate command:

rollback transaction

go

commit transaction

go                 </li>  Run the following code to set the 'allow updates' attribute back to 0:

sp_configure 'allow updates', 0

go

reconfigure with override

go </li></ol>

Note All Database backups that have objects tagged for replication must have the replication attribute removed before you run Setup /testdbupgrade on a restored database.

<div class="references_section">