Share via

  • Article

Copying Databases to Other Servers

It is sometimes useful to copy a database from one computer to another, for example for testing, checking consistency, developing software, running reports, creating a mirror database, or making the database available to remote-branch operations.

There are several ways to copy a database:

  • 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 SQL Server 6.5 or earlier

    For information about upgrading databases, see Copying Databases from SQL Server 7.0 or Earlier.

  • Using the Generate and Publish Scripts Wizard to transfer databases

    You can use the Generate and Publish Scripts Wizard to transfer a database from either a local instance of the Database Engine or from SQL Azure. You can use the wizard in two ways:

    • Use the wizard to generate a script of the database, and then run the script on either another instance of the Database Engine or SQL Azure.

    • Use the wizard and a publishing provider to publish a database directly to a Web service created by using the Database Publishing Services project from CodePlex.

    For more information, see Using the Generate and Publish Scripts Wizard.

Note

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