Using the Copy Database Wizard
The Copy Database Wizard allows you to copy or move databases between servers. You can move and copy databases between different instances of Microsoft® SQL Server™ 2000, and you can upgrade databases from SQL Server version 7.0 to SQL Server 2000. For more information, see Database Upgrade from SQL Server 7.0 (Copy Database Wizard).
To upgrade databases online using the Copy Database Wizard
You also can simplify the administration and maintenance of your databases by integrating multiple instances of SQL Server 7.0 or SQL Server 2000 into a single instance of SQL Server 2000 or into several named instances on a single computer.
In addition to copying the actual databases, you also can copy associated meta data (for example, logins and objects from the master database required by the databases being copied).
Note It not possible to copy or move the model, msdb, or master databases using the Copy Database Wizard.
The Copy and Move Process
To use the Copy Database Wizard, you must be a system administrator or a member of the sysadmin role. If you are running Microsoft Windows NT® 4.0 or Microsoft Windows® 2000, your user account must have administrator privileges on both the source server and the destination server.
To use the Copy Database Wizard, you must specify:
- The source server on which the databases to be copied reside.
- The destination server to which the databases are to be copied or moved.
On the destination server, databases keep the same name they had on the source server. Therefore, only databases with source names that do not exist on the destination server can be moved or copied. Databases cannot be renamed during a move or copy operation. Name conflicts must be resolved manually prior to moving or copying databases.
- The databases to be moved or copied.
To avoid data loss or inconsistency, you must be sure there are no active sessions attached to the database being moved or copied. If there are active sessions, the Copy Database Wizard will not run.
- Other objects to be copied or moved (for example, logins, shared objects from the master database, jobs and maintenance plans, and user-defined error messages).
- The schedule for the copy or move operation, if you want it to run at a later time.
When moving databases between different servers or disk drives, the Copy Database Wizard copies the database to the destination server and verifies that it is online. The wizard does not delete the database from the source server. You must delete the files from the source server manually after you have verified that the copy is fully operational. When moving databases between two instances on the same server, the file system move operation is performed.