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.

»»  Read More...

How to transfer SQL database from one server to another??

I have found an interesting topic in the morning that discussed about “how to backup the database from one server to another ?”. We all want our database will always safe and corruption free. In this modern tech era every databases are very pron to the corruption. As database administrator our main task is maintain the our database safe and high availability for the users. A SQL server database got corrupted or damaged by the several reasons like metadata structure corruption, virus attack, human errors, hardware related issues and many more. In the case of corruption we can restore our database from the secondary server thats why we want to transfer our SQL database from one server to another. But it is very expensive because we will have to maintain the both servers. Most of the companies or organizations prefer their database safe rather that expenses. Microsoft provides several options from transferring SQL database from one server to another server, which are given below:

• Log Shipping
• Database Mirroring
• Snapshot Replication

Log Shipping: In this method you can not perform any operation on the database that is on secondary server. If you want your secondary database to be accessible then you will have to backup, copy the SQL server database on the network and restore from there.

Database Mirroring: In this methods also you can not perform any operation on the secondary server. It just create the copy of your SQL server database. You can use the second database in any corruption on the primary database.

Snapshot replication: If you want to transfer your database from one server to another server on the regular basis then this method is very helpful and effective. This method does not slow the SQL server performance.

At last you can also use database copy tool for the transferring SQL server database from one server to another server.

Note: The main motive behind this transferring operation is recover MS SQL database if any corruption happens at the primary database.

»»  Read More...