Top 4 methods to transfer SQL database from one location to another

Are you looking for a simple method that transfers your SQL server database from one location to another? It is the right place for you; here I will tell you top 4 simple methods that help you. Each method has its own benefits over other. You may finish by one method or need to try all four and the top four methods are...

1. Backup & Restore Method
2. Detach & Attach Method
3. Generate Create Script Method
4. Copy Database Wizard using SSMS

1. Backup & Restore Method: As the name suggests; first backup the database from the old location and restore it on desired location. It creates a new copy of database in the desired location. Database administrator can't perform this method during database is offline.

2. Detach & Attach Method: It is purely an offline operation that means database administrator can't perform this method when SQL server database is online. To do this first detach the SQL server database from old location & attach it on desired location. This method moves database permanently from one location to another instead of copying.

3. Generate Create Script Method: It is another best method for transferring SQL server database from one location to another. To do this follow below steps:

• Select the database from old location in SSMS
• Right click on the database and select “tasks”
• Now choose 'generate script' to launch the Generate create script wizard.
• Click next to close generate the script wizard.
• Connect with the new desired server and create a new blank database in it.
• Go to the new query windows & paste the above generated script
• Now execute it with desired database context.

4. Copy Database Wizard using SSMS: Perform bellow steps to transfer database from one location to another.

• Select the database from the old location using SQL server management studio
• Right click on the database and select 'tasks'
• Now choose 'copy database wizard' from the list to launch wizard.
• Enter the old & new server credentials.
• Select either attach/detach or any other SMO type.
• Click next to run & finish to execute the script.

Apply For: All 4 methods can be applied for SQL server 2008 database.