Home » Blog » SQL Server » Steps to Transfer SQL Database from One Server to Another

Steps to Transfer SQL Database from One Server to Another

author
Published By Deepa Pandey
Ashwani Tiwari
Approved By Ashwani Tiwari
Published On January 19th, 2024
Reading Time 7 Minutes Reading
Category SQL Server

Transfer SQL Database from One Server to Another

This guide can explain how to easily transfer SQL database from one server to another. Users can easily get 3 methods that they can use. They’ll also get an automated tool to use to transfer your database. Moreover, reading the complete guide can help users to move SQL database to another server without any errors.

In this article you will learn:

  • Why you should transfer SQL database from one server to another
  • Different methods that you can follow to transfer your database
  • Why using enterprise software like SQL Server Database Migrator is the best choice.

Content Guide:

  1. Why Transfer Database from One Server to Another
  2. Transfer Database Using Backup and Restore
  3. Copy SQL Database Using Wizard Method
  4. Using Import and Export Wizard
  5. Copy SQL Server Database by Generating Scripts
  6. Use an Enterprise Software to Transfer SQL Database
  7. Conclusion

Why Transfer SQL Database from One Server to Another

Database administrators throughout the world find themselves in situations where they have to transfer their databases from one server to another. These include but are not limited to:

  • Upgradation- You might have decided to finally update your SQL server to a more up-to-date version.
  • Migrating to cloud- Your organization could have decided to finally move your in-house database to a cloud platform.

Pro Tip: You can use an enterprise tool like SQL Database Migration Tool to transfer your database right away.

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.

Stage #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.

Also Read: Migrate Database From SQL Server 2008 to 2019 Version Automatically

Stage #2: Restore Backup Using SSMS to Copy Database from One Server to Another

  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.

Also Read: Upgrade SQL Server 2014 to 2019 Version Safely

Method 2: Copy 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.

Method 3. How to Migrate SQL Server Database Using Import and Export Wizard 

The Import and Export is a built-in option in the Microsoft SQL Server Management Studio.  This method to move database from one server to another can be tricky for users.

Step-1. Open the SSMS application, right-click on the database and select tasks.

Step-2. You can see the small triangle symbol, click on it and select Data source, Server name, and Database.

Step-3. Now, mention where to copy by choosing the destination

Step-4. Choose source tables and views. And click on Next.

Once the process gets complete, check your exported files.

Method 4. How to Copy Database from One Server to Another by Generating Scripts

Step-1. Open SQL Server Management Studio (SSMS).

Step-2. Right-click on the database >> tasks >> Generate Scripts >>Next

Step-3. Click on the Choose Objects option given on the left side and click on select specific database objects radio button.

Step-4. Below the Set Scripting Options, select output type and click Advanced. Choose Save to the new query window.

Step-5. Under Advanced Scripting Options, select Schema and Data from the drop-down list and click OK.

Drawbacks of Using Microsoft SQL Server Management Studio

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.

Also Read: SQL Server Database Stuck in Recovery Pending State – Solved

Method 5: 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 SQL database to a different network or same network, it also helps you to:

  • Repair corrupt database files
  • Recover multiple databases at once

Steps to Transfer SQL Database to New Server

Users must rely on the automated solution as it is advanced. It can execute the entire operation in minimum time possible.

Download Now Purchase Now

  1. Install and Launch SQL Server Database Migrator on your system.
  2. Click on Open to load the database files in the software.
  3. Select the database file (MDF) from the destination location and click on Open to add the file to the software.
  4. Choose Online or Offline mode.
  5. The Software will display the scanning process with complete database details.
  6. After scanning the file, software previews the database Objects (Tables, Functions, Stored Procedures, and Views, etc.) created in the database file.
  7. Software exports SQL database in two different ways, first export to direct SQL server database and second Export data as SQL Server Compatible Scripts

Note: Click Here To See Full Steps to Move SQL Database to Another Server

Conclusion

I hope this article was able to help you move your data. A lot of database administrators face difficulties while moving their database from one server to another. In this article, you learned who you can move your data using 3 different methods. The first method employs the Backup and Restore Wizard in SSMS. In the second method, you can use the copy wizard to copy your database. Both these methods are not the best ones to move database from one server to another so far.

However,  if you lack the time and technical expertise, following these methods is not recommended. Evidently, this move us to the final method to use automated professional software like SQL Server Database Migrator. If you have any other take on how to transfer a database from one server to another, leave them in the comment section below.