Home » migration » Copy Data From One Server To Another In SQL Server

Copy Data From One Server To Another In SQL Server

👤 Naveen Sharma | Modified: May 8th, 2023|migration | 4 Minutes Reading

In SQL server, Users create duplicate copy of database, in order to perform recovery during disaster. In addition, they find it relevant to copy data from one server to another server so as to maintain a record of the entire server. Therefore, there is a need of a solution by which users get to know how to copy data from one server to another in SQL server.

There are basically seven main steps by which one can transfer data from one server to another and those 7 steps are pointed below:

  1. Creating a local login on both server i.e., source and destination
  2. Establish a link between the source server and destination server
  3. Running distributed coordinator service
  4. Enabling DTC in Windows firewall
  5. Modifying default settings of Local DTC security
  6. Fixing collation error or issues
  7. Copy data from one server to another via script

Let us learn about all these copying steps in detail.

Step 1: Creating a Local Login In Source & Destination Server

NOTE: This step is to be followed on both the server machines i.e., source and destination

    • Login into your SQL server account
    • Navigate to Server >> Security >> Logins >> New Logins. Enter all the valid credentials of the server

one-server-to-another

  • From the server roles tab, provide users sysadmin server role

Step 2: Link Your Source Server With the Destination Server

Note: Ensure that the destination server name and Database name is updated properly

    • Execute the below mentioned command script on source server for creating a link between two servers

one-server-to-another-2

    • After successfully finishing the execution of above-mentioned script, now its time to examine the connection. To do so, navigate to Server >> Server Objects >> Linked Server >> My Destination and then right click on Test Connection
    • If the connection is established between two server, then you will encounter a linked server message box; else repeat the same procedure for establishing connection

one-server-to-another-3

  • Click on OK button to proceed to next step

Step 3: Running Distributed Transaction Coordinator Services

NOTE: This process is to be followed on both the server machines i.e., source and destination

    • Go to Services.msc and start DTC Services
    • Set the settings of Startup Type to Automatic

one-server-to-another-4

Step 4: Enable DTC in Windows Firewall

NOTE: This step will be performed on both of the server ends

    • Navigate towards Control Panel >> Windows Firewall >> Allow an app or features through Windows Firewall
    • Now enable domain and private communication by scrolling down in DTC feature window

one-server-to-another-5

Step 5: Modifying Local DTC Security Settings

NOTE: This process is to be followed on both the server machines i.e., source and destination

    • Open Control Panel >> Administrative Tools >> Component Services
    • Click on expand button in order to see the components of Component Services. Then go to Computers >> My Computers >> Distributed Transaction Coordinator >> right-click on Local DTC

one-server-to-another-6

    • Select Properties from the menu and go to Security tab
    • Enable network DTC access, by allowing inbound and outbound communication without any authentication

one-server-to-another-7

Step 6: Fixing Collation Issues, If Any

In actual scenario, you may encounter different collations in columns of the source and destination server. The collation can be fixed on a particular column ‘COLLATE DATABASE_DEFAULT’, has to be added while comparing columns.

one-server-to-another-8

Step 7: Copy Data From One Server To Another Via Script

For instance, we take in consideration a table named as EmployeeDetails with 5 major columns – Unique Id, Employee Id, Phone number, Name and Address

one-server-to-another-9

    • Create a table with help of following script

one-server-to-another-10

    • Now copy data from one server to another in SQL server with help of following script

one-server-to-another-11

NOTE: While executing this procedure you may encounter following collation error message:

one-server-to-another-12

Therefore, to remove such issue, execute the following commands script

one-server-to-another-13

    • Onto the source server, create a trigger on EmployeeDetails table. You can create this trigger with help of following commands script:

one-server-to-another-14

The trigger will perform its functioning when some new data is being inserted on the source server.

Conclusion

While copying data from one Server to another in SQL Server, many issues related to link Server and collation may result to an interruption during the procedure. If the above procedure takes to too much time to copy data from one Server to another in SQL Server, One can go with SQL Server Database Migration Tool for faster transferring their database.