Steps to Transfer SQL Database from One Server to Another

Ashwani Tiwari | Published: September 15, 2020 | Migration

Summary:

In what follows, I will explain how you can easily move the SQL database from one server to another. I will provide you with 3 methods that you can use. I will also provide an automated tool that you can use to transfer your database.

In this article you will learn:

  • Why you should move a database from one server to another
  • Different methods that you can follow to transfer your database
  • Why using enterprise software like SysTools 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. Move SQL Database Using Wizard Method
  4. Use an Enterprise Software to Transfer SQL Database
  5. Conclusion

Why Transfer 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 Server Migrator 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.

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

Steps to Transfer SQL Database to New Server

Download Software
Windows OS
Buy Software Now
(100% Secure)

  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 Quick Scan for normal scanning of the database file and Select Advance Scan for Highly corrupted database files.
  5. Check the Auto Detect option, if you want to detect the SQL Server version of your MDF file automatically.
  6. The Software will display the scanning process with complete database details.
  7. After scanning the file, software previews the database Objects (Tables, Functions, Stored Procedures, and Views, etc.) created in the database file.
  8. Click on Export to move the table’s data from one database to another.
  9. Software exports SQL database in two different ways, first export to direct SQL server database and second Export data as SQL Server Compatible Scripts

Export to Direct SQL Server Database

  • Step 1: – If you choose to Export data directly to SQL Server then select SQL Server Database Option.
  • Step 2: – To Export data direct to SQL Server Database, you need to fill Server Authentication like Server Name & choose the Authentication type as Windows Authentication or SQL Server Authentication.
  • Step 3: – Next, Software provides two different options to migrate database objects from one database to another.

Create a New Database

  1. If you choosing to Create a New Database option, then you need to give Database Name to new database forex. Sample_migration and software will create a new database on the destination SQL Server and creates the database files (MDF & LDF) in its default location.
  2. Now, select the desired objects you want to move. By default, software marks check on all database objects.
  3. Choose either of the options: export the MDF file With the Only Schema or With Schema & Data.
  4. If you save the database components (tables/views/Procedures) With the Only Schema then the software will export the schema/structure of selected database components.
  5. An ‘Export Completed’ Window will appear on screen confirming the successful export of the database. Click on OK.
  6. By Clicking on Close, SQL Server Database Migrator asks for export the details of exported items in a .csv file. Click on yes to save the report.
  7. Browse the folder to save .csv detail in the desired location or software allow you to Make New Folder and save the .csv detail in it.
  8. Open the exported .csv file to view exported items.
  9. The exported records count shows 0 when you export the database objects With Only Schema option.
  10. Now launch SQL Server again and expand the databases to check the newly migrated database.
  11. Expand the newly created database to check all the migrated objects (Tables, functions, stored procedures, views, etc.)

Export to Existing SQL Database

  1. If you want to migrate the database objects to an existing database then check the Export to Existing Database option.
  2. Here we have created a new database named EMPTY to show how to migrate database objects in an existing database.
  3. Now forex. Let’s migrating a single table dbo.productdetail from the SAMPLE database to the EMPTY database.
  4. Checking the available records in dbo.productdetail by executing a select statement.
  5. In database Name, selecting the destination database (EMPTY) to migrate selected or all database objects from the SAMPLE database.
  6. Now selecting dbo.productdetail for migration.
  7. Next, Software allows us to migrate data With Only Schema or Schema & Data.
  8. The software will ask for Export Deleted Records as well. Click on Yes, if you want.
  9. The Export completed popup will prompt on the software screen once the export process completed successfully.
  10. After clicking on Ok, Software will display the exported database object with a record count and Status.
  11. By Clicking on Close, the Tool asks for export the details of exported items in .csv file. Click on Yes to save the report.
  12. Browse the folder to save .csv detail in the desired location or software allow you to Make New Folder and save the .csv detail in it.

Export Data as SQL Server Compatible Scripts

  • Step 4: – A .sql file will get created if you choose to export & save the file as SQL Server Compatible SQL Script.
  • Step 5: – Now check the desired components to be exported from the recovered database.
  • Step 6: – Next, choose either of the options: export the MDF file With the Only Schema or With Schema & Data.
  • Step 7: – If you save the database components (tables/views/Procedures) With the Only Schema then the software will export the schema/structure of selected database components.
  • Step 8: – Next, the software will confirm the data to be exported With Only Schema option.
  • Step 9: – Browse the location to save the .sql file.
  • Step 10: – An ‘Export Completed‘ Window will appear on screen confirming the successful export of the database. Click on OK.
  • Step 11: – After the completion of the recovery process, the software will let you know about the completion process. Click on Close.
  • Step 12: – Navigate from the location where you saved the recovered SQL database.
  • Step 13: – If you want to save the database components with both schema and data then select With Schema & Data and Click on Export, the software will export the schema with the data of selected components.
  • Step 14: – Next, the software will ask you to export a deleted record as well. Click on Yes.
  • Step 15: – Browse the location to save the .sql file.
  • Step 16: – An ‘Export Completed‘ window will appear on screen confirming the successful export of the database. Click on OK.

And that’s it. This is how you can move the database from one server to another in SQL 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 move your database.

But if you lack the time and technical expertise, following these methods is not recommended.

And this brings us to the third and final method, which is 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.