Data Management · Data Migrations · Real Scenario Logic's

To Copy and Sync Database Tables Across Different Server

Introduction

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.

Scenario

we need to sync one Database table named ‘States‘ between Dev_Azure_Server and Local_Server.

States TABLE in DEV_AZURE_SERVER
States TABLE is not in LOCAL_SERVER

To Copy or Sync Database Tables Across Different Server

STEP 1SQL Server Import and Export Wizard

Search for ‘SQL Server Import and Export Wizard‘ and Click ‘Next‘ on the Home page.

Search for ‘SQL Server Import and Export Wizard
Home Page of ‘SQL Server Import and Export Wizard

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 2Choose 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‘.

Choose a Data Source

STEP 3Choose 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‘.

Choose a Destination

STEP 4Specify 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‘.

Table Copy Option

STEP 5Select 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‘.

Select Source Tables and Views

NOTE –

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 6Save 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‘.

Save and Run Package

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‘.

Complete the Wizard

Final Page

Final Page in Wizard

This is the Final Page of the Wizard where we can see the Status of the Execution.

Database Table in Local Server

Database Table in Local Server

Thus, we synced one Database table named ‘States‘ from Dev_Azure_Server to Local_Server.

NOTE –

For Security purposes, we have renamed our Source and Destination credentials.

Conclusion

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.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s