Copying Databases to Other Servers

Copying a database from one computer to another is sometimes useful. You can use a copied database for many purposes, including testing, checking consistency, developing software, running reports, creating a mirror database, or, possibly, to make the database available to remote-branch operations.

A number of alternative methods exist for copying a database between servers:

  • Using the Copy Database Wizard
    You can use the Copy Database Wizard to copy or move databases between servers. For more information, see Using the Copy Database Wizard.

  • Restoring a database backup
    To copy an entire database, you can use the BACKUP and RESTORE Transact-SQL statements. Typically, restoring a full backup of a database is used to copy the database from one computer to another for a variety of reasons. For information on using backup and restore to copy a database, see Copying Databases with Backup and Restore.

    Note

    To set up a mirror database for database mirroring, you must restore the database onto the mirror server by using RESTORE DATABASE <database_name> WITH NORECOVERY. For more information, see Preparing a Mirror Database for Mirroring.

  • Copying databases from Microsoft SQL Server 6.5 or earlier
    For information about upgrading databases, see Copying Databases from SQL Server 6.5 or Earlier.

Note

Several methods also exist for copying data between instances of SQL Server without copying a database. For an introduction to these methods, see Copying Data Between Servers.

See Also

Concepts

About Bulk Import and Bulk Export Operations
Managing Metadata When Making a Database Available on Another Server Instance
How to: Upgrade a Database Using Detach and Attach (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance