Microsoft SQL Server 2012 Bible

Copy Database Wizard

The Copy Database Wizard generates a SQL Server Integration Services (SSIS) package that can copy or move one or more databases from one server to another. If the database is moved to a server on the same network server, this is the premiere method. This method won't work to copy a database from SQL Server 2012 to an older version of SQL Server. In addition, destination server must have the SQL Server Agent running. If the destination server's SQL Server Agent is not running, the wizard will prompt you to start it. The Copy Database Wizard offers the most flexibility and capability. The wizard offers you one of two choices: The detach and attach method or the SQL Management Object (SMO) method.

Warning

The detach and attach method may be faster but requires the database to go offline because the database is being removed from the existing instance. You should be extremely careful with this method for upgrade purposes because this method is a “one-way” process. If you detach a database from a lower version of SQL Server ( e.g. 2008 R2) and attach it to a higher level such as SQL Server 2012, upon attachment to the new instance, the database gets internally upgraded to be compatible with the new version. Due to this automatic upgrade process, the database files can no longer be attached to a lower-level version. Before you complete any upgrade or migration process make sure you get good full backups of your databases so you can roll back in case there are ...