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.
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.
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.