Microsoft KB Archive/299452

= BUG: Copy Database Wizard Exits Without Error if any of the Source Databases are in Single User Mode =

Article ID: 299452

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q299452



BUG #: 353973 (SHILOH_BUGS)



SYMPTOMS
The Copy Database Wizard (CDW) GUI exits without performing the copy if any database that is present on the source server but not on the destination server is in Single User mode.

In the table that follows, Server2 has neither of these databases
 * Northwind
 * Pubs

However, the source, Server1, has both of the preceding databases.

The Northwind database is in Single User mode and the Pubs database is the database to copy. An attempt to copy or move Pubs to Server2 by using the Copy Database Wizard may result in the following error message:

Error Code:570d

Error Category:5700

Source:Microsoft SQL-DMO

Description:[SQL-DMO] Database 'Northwind' is already open and can have only one user at a time.

The error message does not occur if the database that is in Single User mode on the source server exists on the destination server.



CAUSE
You cannot move or copy system databases and replicated databases by using the CDW. The CDW checks for the list of databases from the master..sysdatabases system tables both on the source and destination servers. CDW displays the Move or Copy option for user databases that are present only on the source server that are also not replicated. However, if one of the source databases is in Single User mode, CDW fails to connect and exits.



WORKAROUND
To work around this problem, use any of the methods that follow:
 * Backup and restore the database that you need to move or copy.
 * Use the sp_detach_db stored procedure and the sp_attach_db stored procedure to move or copy the database.
 * Before you use CDW, make sure that none of the databases are in Single User mode. The source databases can be in Single User mode if they exist at the destination.
 * If it is necessary to maintain a database in single user mode, create a dummy database with the same name on the destination to avoid the error.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.

Additional query words: dts cdw

Keywords: kbbug kbpending KB299452

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.