Database migration - The Copy Database Wizard method

Migrating a database is moving it to another instance, within the same server or to another server. Many of the database migrations also includes an upgrade, meaning that the database will be moved to a more recent version of the database engine.

The Copy Database Wizard method like the name says is a wizard that can be used for easily moving databases from one instance to another. During the wizard you will be asked to provide the source and destination servers and you will be able to select one or more databases to migrate at once, specifying the target location. Additional objects as Logins, SSIS packages, SQL Agent Jobs, Endpoints, User-defined error messages and Stored Procedures from master database can also be included and transferred during the process.

The Copy Database Wizard (CDW) isn't available in SQL Server Express Edition since this tool requires SQL Server Agent. Another limitation is that CDW can't be used to migrate system databases and databases marked for Replication, Inaccessible, Loading, Offline, Recovering, Suspect or in Emergency Mode.

The SQL Server Agent must be started and running in the target server and the user that runs the CDW jobs must have sysadmin server role in source and target servers.

The Copy Database Wizard is available in SQL Server Management Studio by expanding Databases Folder and right-clicking on a database name then chose Tasks / Copy Database... option in the context menu:

The wizard will start by requesting the credentials for source and target servers and then will ask for the Transfer Method:

The Use the detach and attach method will basically do what I already described in my previous article SQL Server database migration - The Detach/Attach method so just follow the wizard for this option and read my article to be aware of the details before starting to Copy the database(s). Just keep the check on "If a failure occurs, reattach the source database" option so you won't lose the original database in case of any failure.

The Use the SQL Management Object method will use SMO (SQL Management Object) to transfer the objects and data from the source server to the target server so it will be more slower but the source database will be online during all transfer process.

The next step is to select the database(s) to be transferred. The transfer can copy or move operation and when you select move it means that the source database will be dropped after transfer done. The Status informs you if the database can be transferred (OK) and will display the reason why the database cannot be transferred (System database or the database status).

After choosing the database(s) to be transferred the next step is to configure the database(s) in the target server. In the Configuration screen you can change the destination database name, path and filename. You can also chose what to do if a database or filename already exists in the target server. This screen will appear for each database selected previously.

If the Source and Target servers are different then the next step give us the opportunity to chose server objects that we want to be transferred with the database. Logins used by the source database are selected by default but can be removed if wanted. Others possible server objects are presented in the following screen:

NOTE: Extended stored procedures and their associated DLLs are not eligible for automated copy.

If you selected the Use the detach and attach method then the next step is to specify for each database the correspondent file share containing the source database files:

NOTE: The user that will run this transfer package will need Read & Write permissions in the target folder.

CDW creates a SSIS package to transfer the database and the next step is where you can customize the package as your preference by accepting or changing the default suggested name and choosing to log the operation on not. If yes, you can store the log in Windows Event Log system or into a Text File that will be created by default in the same location as the target database files will be but you change the location.

Last step is to chose between run the job immediately or schedule it for later run. Do not forget that the user choose for Integration Services proxy account should have sysadmin role in the target server or the job will fail with an Access Denied error.

To complete the Wizard review all the choices made and if all ok click on Finish and the job should run successfully and the selected database(s) transferred from the source instance to the target one.

Post migration considerations:

 - If you use the detach and attach method to migrate from an older SQL Server database version you will need to change the compatibility level of the new migrated database to the target SQL Server version. This step is not necessary if you use the SQL Management Object method to migrate the database since it will create the new database with the current SQL Server version.

 - Rebuild all indexes is recommended after transferring a database. It's also recommended to perform a full database backup.

  - If you use the SQL Server Management Object method to transfer a database with full-text catalog, then you will must repopulate the index after the transfer.