SQL Server Database Migration

SQL Server Database Migration
In this post, will learn about how to move\copy\migrate SQL database from one Server\Instance to another Server\Instance or one drive to another drive.
There are many ways to move\copy a database from one SQL Server\Instance to another Instance\server using SQL Server Tools and utilities.

1. Detach\Attach: Detach the database from the old server and attach it in the new server. This is purely offline operation and it moves the database instead of copying it.

To detach a database

• Connect to the Database Engine. 
• From the Standard bar, click New Query. 
• Copy and paste the following example into the query window and click Execute. This example detaches the AdventureWorks2012 database with skip checks set to true.

EXEC sp_detach_db 'AdventureWorks2012', 'true';

To attach a database

• Connect to the Database Engine.
• From the Standard bar, click New Query
• Use the CREATE DATABASE statement with the FOR ATTACH close.
• Copy and paste the following example into the query window and click Execute. This example attaches the files of the AdventureWorks2012 database and renames the database to MyAdventureWorks.

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'), 
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf') 
    FOR ATTACH; 

2. Backup\Restore: Back up the database from old server and restore it to the destination server. This can be performed during online and it creates a new database in the destination server.
Prefer to take Copy only full backup and restore it to destination server, because copy only backup will not disturb your backup cycle.

Refer Backup Restore post:
http://rdbmsknowledge.blogspot.in/2015/07/sql-server-backup-restore.html for more information.

3. Copy Wizard: Using copy database wizard in SQL Server Management Studio.

• Select the database in the source server in SSMS
• Right click ->Tasks->Copy Database Wizard to launch the copy database wizard.
• Enter the source and destination credentials and select either attach/detach or SMO type
• click next and you can schedule or run immediately
• Click finish to execute it

Refer http://msdn.microsoft.com/en-us/library/ms188664.aspx for more information.

4. Generate Script: The last type is to generate the create script using Generate Script Wizard (SSMS)  and execute it in the destination server.
 This is least prefer option because it requires more manual work.

• Select the database in the source server in SSMS
• Right click ->Tasks->Generate Scripts Wizard to launch the wizard.
• Select the various scripting options needed and select the objects needed to generate the scripts for them. Make sure script data = true in the scripting option to generate script for data as well (INSERT statements)
• click next ->next and finish to generate the script (new query window or clip board or file)
• connect to the destination server and create the new database in it.
• Click new query window and paste the script generated using GSW above and execute them with the destination database context.

Refer http://msdn.microsoft.com/en-us/library/ms181421.aspx for more information