Using the Copy Database Wizard
The Copy Database Wizard provides a convenient way to transfer, move or copy, one or more databases and their objects from an SQL Server 2000 or SQL Server 2005 instance to an instance of SQL Server 2005.
You can use the Copy Database Wizard to perform the following tasks:
- Transfer a database when the database is still available to users by using the SQL Server Management Objects (SMO) method.
- Transfer a database by the faster detach-and-attach method with the database unavailable during the transfer.
- Transfer databases between different instances of SQL Server 2005.
- Upgrade databases from SQL Server 2000 to SQL Server 2005.
Important
The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.
Permissions
To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files.
Issues to Consider
Consider the following issues before you use the Copy Database Wizard.
| Area | Consideration |
|---|---|
master, model, msdb, tempdb and distribution databases |
System databases cannot be transferred by using the Copy Database Wizard. |
Move Database option |
If you select the Move option, the wizard automatically deletes the source database after moving the database. The Copy Database Wizard does not delete a source database when you select the Copy option. |
Full-text catalogs |
If you use the SMO 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. |
64-bit editions of Microsoft SQL Server 2005 |
Use the detach-and-attach method on 64-bit processor configurations. |
SQL Server Agent |
SQL Server Agent must be running on the destination server. |
Encrypted objects |
Encrypted objects, including certificates, keys, and stored procedures must be transferred manually. |
Database extended properties |
The SMO method will transfer database object extended properties but not extended properties for the database itself. The detach-and-attach method will copy all extended properties. |
SQL Server authentication logins |
Transferred logins that were created by using SQL Server authentication must be enabled and have passwords reset on the destination server. |
Vardecimal storage format |
If the vardecimal storage format was enabled for a database on the source server and the SMO transfer method is used, the vardecimal storage format must be reenabled on the destination server. |
Copy Database Wizard SQL Server Integration Services Custom Tasks
The Copy Database Wizard builds a SQL Server 2005 Integration Services (SSIS) package that runs on the destination server. After the wizard screens have been completed, SQL Server 2005 automatically names and saves the package on the destination server. The Object Explorer Integration Services node path of the package is \Stored Packages\MSDB\<destination instance name>\DTS Packages\Copy Database Wizard Packages\<package name>. The package is saved whether it is run immediately, scheduled for a later date, or scheduled on a repeating basis.
Starting the Copy Database Wizard
To start the Copy Database Wizard
- Open 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 destination database name and the path of each data file for each database to be transferred. If the destination database name to be transferred already exists on the destination server, the wizard will append _new, _new1, and so on to the database name and database file names.
- The schedule for the copy or move operation, if you want it to run later.
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. To avoid data loss or inconsistency, active sessions must first be disconnected from the database being moved or copied. If any active sessions exist, the Copy Database Wizard will not perform the move or copy operation using the detach-and-attach method.
When you copy or move databases by using the detach-and-attach method, a file system copy of each source database file is made to the destination server and verified. If a move operation is successful, the source database files are deleted from the source file system. This occurs in all detach-and-attach scenarios whether the source and destination instances are the same instance, on the same server or on different servers.
Note
For the SMO method, active sessions are allowed.
Upgrading to SQL Server 2005 with the Copy Database Wizard
You can use the Copy Database Wizard to upgrade from a SQL Server 2000 database to a SQL Server 2005 database.
You can also 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 into several named instances on a single computer.
When you use 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. You can rename the database during this operation.
Important
After you upgrade databases from an earlier version of SQL Server, run sp_updatestats against the database on the destination server to update statistics and make sure that the copied database is performing optimally.
See Also
Tasks
How to: Upgrade to SQL Server 2005 with the Copy Database Wizard
Concepts
Copying Databases to Other Servers
Copying Databases from SQL Server 6.5 or Earlier