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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
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.

Server1 (SQL Server 7.0 or SQL Server 2000)Server2 (Must be SQL Server 2000)
Northwind (Single-User)N/A
PubsN/A


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.
Modification Type:MajorLast Reviewed:10/31/2003
Keywords:kbBug kbpending KB299452
©2003 Microsoft Corporation. All rights reserved.