Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Operations
Administration
 Using the Copy Database Wizard

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (0)
Other versions are also available for the following:
SQL Server 2008 Books Online (June 2009)
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.

ms188664.note(en-us,SQL.100).gifNote:
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.

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.

In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

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.

ms188664.note(en-us,SQL.100).gifNote:
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.

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.

ms188664.note(en-us,SQL.100).gifImportant:
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

Community Content   What is Community Content?
Add new content RSS  Annotations
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker