Using the Copy Database Wizard
The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another, with no server downtime. Using this wizard, you can do the following:
Pick a source and destination server.
Select databases to move or copy.
Specify the file location for the databases.
Create logins on the destination server.
Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
Schedule when to move or copy the databases.
In addition to copying databases, you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database.
Note
The model, msdb, and master databases cannot be copied or moved by the Copy Database Wizard.
Also, you can move and copy databases between different instances of SQL Server, and you can upgrade databases from SQL Server 2000 to SQL Server 2005 or later. The destination server must be SQL Server 2005 or later. For more information, see "Upgrading SQL Server by Using the Copy Database Wizard" later in this topic.
Issues to Consider
Consider the following issues before you use the Copy Database Wizard.
Area |
Consideration |
|---|---|
Required permissions |
You must be a member of the sysadmin fixed server role on both the source and destination servers. |
Required components |
SQL Server 2005 Integration Services (SSIS) or later. |
model, msdb and master databases |
The model, msdb, and master databases cannot be copied or moved by the Copy Database Wizard. |
Database on source server |
If you select the Move option, the wizard deletes the source database automatically after moving the database. The Copy Database Wizard does not delete a source database if you select the Copy option. |
Full-text catalogs |
If you use the SQL Server Management Object method to move the full-text catalog, you must repopulate the index after the move. If you use the detach-and-attach method, full-text catalogs must be moved manually. For more information about how to move full-text catalogs, see Moving Database Files. |
Starting the Copy Database Wizard
In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
Copying and Moving Databases
To use the Copy Database Wizard, you must specify the following:
The source server where the databases to be copied reside.
The destination server to which the databases are to be copied or moved.
The databases to be moved or copied.
The name of a target database, if different than the name of the source database.
The source database name can be use for the copied or moved database only if name conflicts do not exist on the destination server. If name conflicts exist, you must resolve them manually on the destination server before you can use the source database name there.
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.
If you are not a system administrator, you must specify a SQL Server Agent Proxy account that has access to the Integration Services (SSIS) Package execution subsystem.
The detach-and-attach method, detaches the database, moves or copies the database .mdf, .ndf, .ldf files and reattaches the database in the new location. For the detach-and-attach method, to avoid data loss or inconsistency, active sessions cannot be attached to the database being moved or copied. If any active sessions exist, the Copy Database Wizard does not execute the move or copy operation.
Note
For the SQL Server Management Object method, active sessions are allowed because the database is never taken offline.
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. When moving databases between two instances on the same server, the file system move operation is performed.
Managing Metadata When Restoring to Another Server Instance
When you copy a database to another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.
Upgrading SQL Server by Using the Copy Database Wizard
You can use the Copy Database Wizard to upgrade from a SQL Server 2000 database to a SQL Server 2005 or later database.
You also can simplify the administration and maintenance of your databases by integrating multiple instances of SQL Server 2000 into a single instance of SQL Server 2005 or later or into several named instances on a single computer.
When using the detach-and-attach method of the Copy Database Wizard to upgrade a database, make sure that no applications or services are trying to access the database. Do not use read-only mode; doing so results in an error. You can rename the database during this operation.
Important
After upgrading databases from an earlier version of SQL Server, run sp_updatestats against the database on the destination server to update statistics and ensure optimal performance of the copied database.
To upgrade to SQL Server by Using the Copy Database Wizard