In this blog post, we will see How to make Copy, Sync between database tables across different Server with simple steps. We can follow the below steps and can copy or sync our Database Tables across different server and same server with different databases.
we need to sync one Database table named ‘States‘ between Dev_Azure_Server and Local_Server.
To Copy or Sync Database Tables Across Different Server
STEP 1 – SQL Server Import and Export Wizard
Search for ‘SQL Server Import and Export Wizard‘ and Click ‘Next‘ on the Home page.
This SQL Server Import and Export Wizard mostly comes with SSMS(SQL Server Management Studio) itself. We can use this wizard to Move,Copy and Sync Tables from Source to Destination Databases. Here, we are going to see how we can use this wizard to Copy or Sync our Database Tables Across Different Server.
STEP 2 – Choose a Data Source
Here, we have to choose our Data Source as ‘Microsoft OLE DB Provider for SQL Server‘ . Also we need to give our Server Name along with User Name and Password and need to choose our Database Name from where we need to copy or where we need to copy or sync our Source Database Name. After Choosing, Click ‘Next‘.
STEP 3 – Choose a Destination
Here, we have to choose our Data Source as ‘Microsoft OLE DB Provider for SQL Server‘ . Also we need to give our Server Name along with User Name and Password and need to choose our Database Name from where we need to copy our data to and need to specify our Destination Database Name. After Choosing, Click ‘Next‘.
STEP 4 – Specify Table Copy or Query
Here, the option is given for us to copy one or more tables and views from source to destination databases. Also we can write a query to make some restrictions in data transfer. Here we are going to choose, ‘Copy data from one or more tables or views‘ Option like below. After Choosing, Click ‘Next‘.
STEP 5 – Select Source Tables and Views
Here, the option given for us is, we can choose one or many Views and Tables by using Multi select buttons and those will be copied from Source to Destination. We can also change the Destination object names (Table names and View names) as per our wish. After Choosing, Click ‘Next‘.
The User given Destination Object Name(Table Name and View Name) should not be already there with the same object name in the Destination.
STEP 6 – Save and Run Package
Here, we need to mention whether we can Run immediately or to Save the SSIS (SQL Server Integration Services) Package. The Option that We have chosen here is Run immediately. After Choosing, Click ‘Next‘.
STEP 7 – Complete the Wizard
Complete the Wizard – This will be like Final Summary Page where we can cross-check the Source and Destination and Table and View names from Source and to destination. After Verifying, Click ‘Finish‘.
This is the Final Page of the Wizard where we can see the Status of the Execution.
Database Table in Local Server
Thus, we synced one Database table named ‘States‘ from Dev_Azure_Server to Local_Server.
For Security purposes, we have renamed our Source and Destination credentials.
With the help of this SQL Server Import and Export Wizard, we can copy data from source to destination.We can also copy structure alone from source to destination in write a query to control data transfer option in Specify table Query Option. We can also make sync between our database tables.