How to Transfer Database from One Server to Another

Amy Dennis

Jan 27, 2024 • Filed to: Take Data Backup • Proven solutions

Like the process of file transfer between two devices, one can carry out the process to transfer a database from one server to another. The simplest way to accomplish the procedure is i.e., copy database from one server to another. Moreover, while transferring a database between servers, make sure you know the targeted servers' passwords. This article explains the methods to transfer database from one server to another.

By definition, a database is a collection of data that is stored in the computer with administrator rights. Here the data can be accessed by all the devices working in the network or server. The database contains all the information regarding the server and connected devices. In case of a system crash or failure, or corruption of the device's hard drive, acting as an administrator can lead to a conclusion, i.e., transfer database from one server to another. This process can be initiated i.e., copy database from one server to another. This article explains the two process to transfer database from one server to another. Here are the topics that would be discussed in detail.

Part 1: How to Copy Database from One Server to Another Using Copy Database Wizard?

If you intend to copy the database from one server to another, a simple way is to use the copy database wizard. For a server expert or administrator, using this in-built utility is an easy way to transfer the database from one device to another i.e., from server A to server B. the copy database wizard is an in-built feature for the SQL server management studio. If you are opting for the copy database wizard, there are some limitations in the way. Here are some of them:

1- If you are using an express version of the SQL server, the copy database wizard is not available.

2- If you are using a new SQL Server version, the database cannot be transferred or copied to older versions.

3- The databases that are marked for replication, kept Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode, or have data or log files stored in Microsoft Azure storage cannot be copied or transferred via copy database wizard.

In case you are using the SQL server and want to copy a database from one server to another, then here are the steps to follow:

Step 1: Launch the SQL server management studio. From the menu, select the targeted database that is aimed for transfer.

Step 2: Right-click the target database and the drop-down menu, select the option of tasks, and then select the copy database option.

Step 3: In the copy database wizard window, click next to continue.

Step 4: In the next window, add the name of the source server. For entering the source server, choose windows authentication or SQL server authentication mode. Add username and password and click next to continue.

Step 5: Repeat the same step to add the destination server's name and apply for authentication information. Click next to continue.

Step 6: Select Use the SQL management object as a method and click next to continue.

Step 7: In the next window, select the target database and choose the option of Move or Copy. Mark the option of Copy to keep the database in the original server, whereas tick the option of Move for transferring the database to other servers.

Step 8: Check the MDF and log file path in the destination device. Complete the wizard and click Finish to complete the action. Once the process is complete, the database will be transferred to the destination server.

Part 2: How to Move Database from One Server to Another Using Backup and Restore

Apart from using the SQL server management studio to copy the database from one server to another, there is a direct method to complete the action. One can use the Copy and move technique to transfer the database from one server to another. This technique can be performed via backup and restore. Create a backup for the database of one server and restore it to the other server. Here are the steps to follow:

Step 1: Launch the SQL server management studio, select the target database, and right-click on it. In the drop-down menu, choose the option of tasks, followed by selecting backup.

Step 2: Select the full backup and click add to add the backup with location and name.

Step 3: Create a full backup of the source server and copy the database backup created. Copy it to another device.

Step 4: Now launch the SQL server management studio in the destination server device. Right-click the SQL server and select the option of restore the database from the drop-down menu.

Step 5: In the next pop-up, select the device i.e., the full backup created on the source server device, and restore the backup on the destination server device.

Once the transfer process is complete, the target folders restored as the full back up on the source server device will be shown on the destination server device.

Hence, several reasons may lead to the transfer of the database from one server to another. It may include any situation, i.e., system failure/system crash or hard drive corruption on any device in a network using the server. Other reasons may include syncing two devices using different servers. This article explained detailed stepwise procedures to copy the database from one server to another. The transfer of a database from one server to another can be performed via copy database wizard and simple backup and restore. In the former technique, a built-in feature of SQL server management studio i.e., Copy database wizard, is used. In the latter, full back up for the database is created, which is then restored to the other server in the destination device.