Method 1: Move SQL Database by Backup and Restore

It is a two-step method. First, take a backup of your existing SQL database. After that restore the backup file.

Step 1: – Create Backup File

  1. Launch SQL Server Management Studio (SSMS) and go to object explorer.
  2. Click on Databases and right-click on the database that you want to transfer.
  3. Then click on Tasks > Back Up
  4. In the Backup Database dialogue box, set the Back type as full and Destination as Disk.
  5. Click on Add to set your backup destination.
  6. Click on OK to end the backup process.

Your backup will be created in a few minutes. After this backup, you now have to restore it on your new server or system. Follow the steps below to restore the database from one server to another.

Step 2: – Restore Backup Using SSMS

  1. Launch SSMS and connect to your database.
  2. Right-click on your database. Click on Tasks and then on Restore > Database.
  3. A new dialogue box Restore Database will appear.
  4. Select From device under Source for restore section. Then click on browse (…) to select your backup file.
  5. Set Backup media as File in the Specify Backup window.
  6. Click on Add and select your backup file. Click OK.
  7. Click on Options under Select a page panel in the Restore Database window.
  8. Set the Restore options and Recovery state as per your requirement and click on OK.

Your backup will be restored shortly.

Method 2: Move SQL Database on Another Server by Copy Wizard

To transfer a database from one server to another follow the steps below-

  1. Launch SSMS and go to object explorer.
  2. Right-click on a database and then click Task>Copy Database.
  3. Put in the name of Source Server. Also, enter the username and password of your server and hit Next.
  4. Enter the name of Destination Server and click on Use SQL Server Authentication.
  5. Select Use SQL Management Object Method and click on Next.
  6. Check the copy box, Click Next.
  7. Check MDF and LDF files path for the destination server.
  8. Click on Run Immediately to copy the database right away.
  9. Click on Finish.
  10. The database will be copied shortly.
  11. Refresh the destination server to see the newly copied database.

Drawbacks of Manual Methods

As good as these methods are they still have the following drawbacks-

  1. They eat up a lot of your time. If you are short on time these methods are not recommended.
  2. If you are not a very technical person it can be a hassle to go through each and every step one by one.
  3. There are cases where your complete data might not get moved. This will result in an error and a lot of lost time.

Method 3: Transfer SQL Database Using Enterprise Software

To avoid all the difficulties and time requirements required by the above methods you can use professional enterprise software to transfer your database from one server to another.

Apart from helping you transfer your database, it also helps you to:

  • Repair corrupt database files
  • Recover multiple databases at once
  • Decrypt encrypted SQL scripts
  • Analyze log database files