Copy Database Wizard (Select the Transfer Method)

  • Article

Choose between two methods for the Copy Database Wizard.

Issues to Consider

Area Consideration

Full-text catalogs

If you use the SQL Management Object (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.

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.

Options

  • Use the detach and attach method
    Detach the database from the source server, copy the database files (.mdf, .ndf, and .ldf) to the destination server, and attach the database at the destination server. This method is usually the faster method because the principal work is reading the source disk and writing the destination disk. No SQL Server logic is required to create objects within the database, or create data storage structures. This method can be slower if the database contains lots of allocated but unused space. For example, a new and almost empty database that is created allocating 100 MB, copies all the 100 MB, even if only 5 MB is full. The associated job must run on the destination server under an Integration Services Proxy Account that has permission to access the source server database files (ReadOnly for copy and ReadWrite for move) and the destination server database files (ReadWrite).

    Note

    This method makes the database unavailable to users during the transfer.

  • If a failure occurs, reattach the source database
    When a database is copied, the original database files are always reattached to the source server. Use this box to reattach original files to the source database if a database move cannot be completed.

  • Use the SQL Management Object method
    Read the definition of each database object on the source database and create each object in the destination database. Then transfer the data from the source table to the destination table, recreating indexes and metadata.

    Note

    Database users can continue to access the database during the transfer.