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.

Database migration - The Backup & Restore 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 Backup & Restore method can be used to reduce the downtime period during the database migration process by performing a full backup and restore it in the new MSSQL instance while the users are still logged in and working so the downtime can be shorter by applying a differential or transaction log backup (depending on the database recovery model) after all users being disconnected and the database does not have any activity, meaning that downtime is limited to the time of the differential/transaction log backup, copy and restore tasks.

NOTES:

• This migration method is for user databases only. System databases, Jobs, DTSX Packages, Reports and any other database object are not covered in this article.
• A sysadmin role is necessary to execute any of the provided steps.

Before start - Only for Upgrades

In the case of a migration being part of an upgrade then the first pre requisite is to run the SQL Server Upgrade Advisor to analyze the earlier version of the SQL Server database, and then generates a report that identifies issues to fix either before or after the upgrade. Those issues are not covered by this article either.

Step 0 - Preparation

The database files on the destination server will be the same size as the database files on the source server, so for larger files you can shrink them before performing the database full backup so smaller files will be created during the restore on the destination server. For data files use shrink with truncate only option so will only release all free space at the end of the file to the operating system but does not perform any page movement inside the file. It will be faster and will not cause fragmentations.

Step 1

Run a full backup on the source database by using the interface provided by SSMS (right-click on the database name, chose Tasks/Backup...:

The same operation can be executed by the following T-SQL commands:

BACKUP DATABASE [EE] TO DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.bak' WITH NOFORMAT, INIT, NAME = N'EE-Full Database Backup'

Note: "EE" is the database name used for this example. You need to replace it with your real database name.

Step 2

Copy the the full backup file from the source location backup path to the destination server.

Important: In the case a rollback will be necessary this backup file can be used to perform that task.

Step 3

Connect to the destination server and restore the full database backup made in step 1. Specify the WITH NORECOVERY option if you will apply differential and/or transactional log restores then only the restore of differential or transaction log backup in the destination server will be running the WITH RECOVERY option.

You can restore the database by using the interface provided by SSMS (right-click on the Databases folder and chose Restore Database...) and locate the backup files by clicking the "From device" button. Check the Restore cell and provide a name for the new database. In case of providing an existing name then the database will be overwrited:

Click on Options to specify the WITH NORECOVERY state if you will apply  a differential or transaction log restore after, or if you want to change the data and transaction log files location when the path is different from the source server. In case of restoring into an existing database check the WITH REPLACE restore option:

The same operation can be done with the following T-SQL command: 

RESTORE DATABASE [EE] 
FROM  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.bak' 
WITH  FILE = 1,  
 MOVE N'EE' TO N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE.mdf',  
 MOVE N'EE_log' TO N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE_1.LDF',  
 NORECOVERY,  NOUNLOAD
GO

If the full backup was performed during a period that there was no activity on the database so you ran this step with the Recovery option then you can skip the next step. Otherwise if the NoRecovery option was applied your new database should be in Restoring status: 

Step 4 - Only for minimal downtime

Now that the tasks that took more time were done and all the users has been disconnected and no more activities are running on the database is time to perform a transaction log backup or a differential database backup (depending if the database has respectively Full or Simple recovery model set) so the additional database modifications will be applied and stored in the migrated database.

Run a differential or transactional backup on the source database by using the interface provided by SSMS (right-click on the database name, chose Tasks/Backup... The Backup Type combo box has the available options for the backup types. No more options need to be changed since the Recovery option is the default and it will keep the file locations from the previous restore:

The same operation can be executed by the following T-SQL commands:

BACKUP LOG [EE] TO  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.trn' WITH NOFORMAT, NOINIT,  NAME = N'EE-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD

Copy the the new backup file from the source location backup path to the destination server.

Important: In the case a rollback will be necessary this backup file and the previous full backup file can be used to perform that task.

Connect to the destination server and restore this backup by applying the WITH RECOVERY option.

You can restore the database by using the interface provided by SSMS (right-click on the new databases folder and chose Restore...) and locate this backup file by clicking the "From device" button:

The same operation can be done with the following T-SQL command:

RESTORE LOG [EE] FROM  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.trn' WITH  FILE = 1,  NOUNLOAD

The database should be now online and available.

Step 5 - Only for Upgrades

In the case of a migration being part of an upgrade then there is an extra step that is to change the compatibility level of the migrated database so his behaviour will be compatible with the current version of SQL Server.

After that you should rebuild all indexes but if time is an issue then alternatively run DBBC UPDATEUSAGE on the database to correct any incorrect row or page counts and update the statistics for all the tables in the database with the UPDATE STATISTICS command.

Step 6

After database migration you will need to transfer the logins and passwords from the old instance to the new one and map those logins to the database users. Microsoft has a good and complete article about that so I just leave here the link to the article.

After all these steps are done it is highly recommend that you perform a full backup of the migrated database.