Understanding and troubleshooting the Copy Database Wizard in SQL Server 2000

The Copy Database Wizard is a new utility in SQL Server 2000 that allows you to move or copy a database from SQL Server 7.0 or from SQL Server 2000 to SQL Server 2000. The copy or move process is relatively straightforward and it may seem like the process barely affects anything. This article provides information on how the Copy Database Wizard works and outlines some issues to look out for when you use the wizard.

How the Copy Database Wizard Operates

To open the Copy Database Wizard:

• In the SQL Server Enterprise Manager, click Management, and then click Run a Wizard.
  
  -or-
  
  
• Right-click the Databases folder, click All Tasks, and then click Copy Database Wizard....
  
  -or-
  
  
• At a command prompt, type Cdw.exe and then press Enter.

The wizard detaches the databases that are to be copied or moved, copies the files associated with the database to the destination server and then attaches the database. The detaching and attaching of the databases is performed through the following stored procedures:

• sp_detach_db
  
  -and-
  
  
• sp_attach_db

For more information about these stored procedures, refer to the "sp_attach_db" and "sp_detach_db" topics in SQL Server Books Online.

The actual copying of the file occurs via XP_CMDSHELL which executes a command-line COPY command. This is why it is important that the account that runs the Copy Database Wizard is a memeber of sysadmin. For more information, see "Using the Copy Database Wizard" in SQL Server Books Online.

The wizard performs the following steps:

1. Creates a UNC share point on the source computer where the files are to be copied.
2. Checks for active connections in the source database.
3. Puts the database in the single user mode.
4. Detaches the source database.
5. Checks if the files exist on the destination and then copies the database files to the share point created earlier.
6. Attaches the database back on the source server.
7. Attaches the database on the destination server.
8. Removes the UNC share.
9. Puts the database back in its original mode (single-user, multi-user, and so forth).

Four additional features exist that allow you to move objects that are associated with the database that is being copied or moved.

• Logins (recommended).
  • All logins detected at package run-time.
  • Only logins used by the selected databases.
• Shared stored procedures from the master database (optional).
  • All stored procedures detected at package run-time.
  • User-selected stored procedures.
• Jobs from msdb (optional).
  • All jobs detected at package run-time.
  • User-selected jobs.
• User-defined error messages (optional).
  • All error messages detected at package run-time.
  • User-selected error messages.

Wizard Creates a Data Transformation Services (DTS) Package

The Copy Database Wizard creates a DTS package that runs on the destination server and can be run either immediately or can be scheduled to run later. Alternatively, you can manually create a copy of the database DTS package by using the DTS Designer tool and clicking Transfer Database Task. For more information, see the Transfer Database Objects Tasks topic in SQL Server 2000 Books Online.

The package is saved if it is scheduled to run on a recurring basis or one time. Therefore, the SQL Server Agent on the destination server has to be started. The Transfer Database DTS package must be treated as any other DTS package including the requirements and permissions associated with scheduling and running the package. For more information about how to troubleshoot scheduled DTS packages, see the following article in the Microsoft Knowledge Base: You can use the Copy Database Wizard with Microsoft SQL Server 7.0 where the latter can be a source database server only. You cannot use the wizard with versions of Microsoft SQL Server 6.x or earlier.

Copy Database Wizard Log - DTS Package Log

A log of the CDW events is stored in MSDB..sysdtspackagelog system table and is also viewable by way of SQL Enterprise Manager (SEM). To view the log, follow these steps:

1. On the destination server where the package is stored, open SEM and expand the Data Transformation Services folder.
2. Under Local Packages, locate the package for which you want to view the log. By default, the packages are named CDW_SourceServer_DestinationServer_autonumber.
3. Right-click the package and select the Package Logs option.
4. Under DTS Package versions and log tree, click the plus (+) sign to expand and view the list of logs.

This dialog box allows you to view the log(s) for this package or to select another package from the drop-down box. It also allows you to delete logs that you do not need by clicking the Delete button.

Alternatively, you can configure DTS Package to produce an output file for troublshooting purpose.

1. Right-click on the package, select Design Package.
2. From the Package menu, select Properties.
3. On the logging tab, specify the errr file.
   
   This file will have detailed information on the executation of the package.

Permissions to Copy the Files

The destination SQL Server startup account must be a domain account and must have local administrator privileges on the source SQL Server server computer so that the files can be copied from the source to the destination.

If the destination SQL Server is started under a local system account you may receive the following error message: If the destination SQL Server is started under a domain account that does not have local administrator privileges on the source computer then the following error message may be generated by the wizard: Clicking on the "MORE INFO >>>" button, you will find that permissions to create the remote share are not present.

Be Aware that the Database Detaches

As mentioned earlier, the Copy Database Wizard detaches the source database before copying it to the destination server. Do not run the Copy Database Wizard if the database is suspect or if any other problems exist with the database that may prevent it from detaching or reattaching. Problems such as I/O errors (for example, error 823), data integrity errors (for example, Table Corrupt), or any known hardware issues (for example, SCSI port errors or controller errors in the system event log), to name a few, may be an impediment to a successful reattachment of the database.

Make sure that the source database is not marked 'read only', 'offline', 'suspect', 'emergency', or 'loading' or the detach will fail. The detach would fail because the detach procedure must update the statistics in the database; if the database is in any way not accessible for writes, the statistics update fails. The following error message is generated if the detach fails due to the database being in any unwritable status: The View Error button shows the following: In addition, detaching the database requires that it be in single-user mode or have no users connected to it. If multiple users are using the database, the wizard may fail with the following error: If you click the More Info button, you can see the following root cause of the problem:

Permissions to Detach a Database

Certain permissions are required to detach a database. SQL Server 2000 Books Online identifies the appropriate permissions to use the wizard:

Ensure Destination Directory Exists

When using the Copy Database Wizard (CDW), at the Database File Location screen, click the Modify button and confirm that the destination directory is correct. If the directory does not exist, the CDW may fail with the following error: If you click the "MORE INFO >>>" button, you will find that the directory is not present.

The Move Option Removes the Database

Bear in mind that if you choose to move a database, rather than copying it, the database is removed from the source SQL Server server. More specifically, the database is detached from the source server but is not reattached. However, the original database files (.mdf, .ndf, .ldf) remain on the source server and if you do not need those because of space considerations, you have to manually delete them. The following warning message appears in the Completing the Copy Database Wizard dialog box: However, if you mistakenly select the MOVE option instead of the COPY option, you can safely reattach those files back to the server to recover the database.

Moving Logins

A new feature of the Copy Database Wizard alleviates the issue of manually moving logins. The wizard identifies the logins corresponding to the database users and provides you with the option to move or copy those as part of moving or copying the database. Both SQL Server Standard authentication logins and Microsoft Windows NT and Microsoft Windows 2000 authentication logins are moved by the wizard.

Earlier versions of SQL Server required manual intervention to move the logins associated with the database users of the database being copied or moved. For more information about transferring logins with earlier versions of SQL Server, see the following article in the Microsoft Knowledge Base: Note that local Windows NT/2000 Groups and Users that are granted login access to SQL Server cannot be transferred between different computers. If you attempt to do this, the transfer of the login will fail. If you click the "MORE INFO >>>" button, you can see the following root cause of the problem:

Database/Files Exist or There is Insufficient Disk Space

When transferring a database, the wizard detects whether or not the database exists on the destination server. The Select the Database to Move or Copy dialog box indicates that databases can be transferred with an OK status or that databases cannot be transferred either because the database Already Exists or is a System Database like the master database.

A database transfer can also be stopped if the same database files already exist on the destination server. So, you may have a database named MyPubs with file names equivalent to those of the Pubs database on the destination. You will see those files destinations marked with a red X. To avoid this error message, either modify the filenames to be created on the destination server or change the physical location into which those will be created.

A transfer can also be hampered by a lack of disk space on the destination server. Change drives or delete some files to allow for sufficient space.

Full-Text Search Stops. Full-Text Catalog Files Not Transferred

If the database contains any full-text catalogs there are two effects of using the Copy Database Wizard to transfer it. First, searching the Full-Text (FT) catalogs is stopped by the wizard so that the database can be put in single user mode. Second, the FT catalog files are not moved by the wizard. If you try to run a full text search query on the source or destination SQL server after the Copy Database Wizard is run, you may get the following error message: For more information, see the following article in the Microsoft Knowledge Base: To perform full-text searches on the source and destination SQL Server servers use these steps:

1. Run the following stored procedure to enable full text indexing on the source and/or destination databases:

   sp_fulltext_database 'enable'
   					

2. Perform a full population on all catalogs.

Here is the relevant section from SQL Server Books Online: For more information about how to move Full-Text Catalogs, see the following article in the Microsoft Knowledge Base:

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: