Database Data Synchronization - using dbForge Data Compare for SQL Server

In this short video, we are going to demo for you the specific capabilities of dbForge Data Compare for SQL Server, which allow you to compare and synchronize data in the database.

0:00 Introduction

0:21 Sync of data from source to target for an individual table

1:37 Sync of data based on the condition of one or more column value

2:30 Sync of data based on the column value of another table which is linked by a foreign key

4:04 Note about automatic data synchronization

👉 Check full overview of dbForge Data Compare for SQL Server here: https://www.devart.com/dbforge/sql/datacompare/

🔥 You can download dbForge Data Compare for SQL Server Trial here: https://www.devart.com/dbforge/sql/datacompare/download.html

🔔 Subscribe to our channel and get dozens of tutorials about Devart tools: https://www.youtube.com/c/DevartSoftware

---

Closed Caption:

[Music]
foreign
tool by devart can help you to compare
and deploy database contents quickly and
accurately and transfer data from test
to production or vice versa data
Compares a standalone tool but can also
be launched within the SQL Server
management Studio
first of all let's run a script to make
the data in the two databases different
to sync data between two specific tables
leaving the rest of the database
unchanged we go to the mapping tab of
the new data comparison wizard here we
select only those tables that are to be
compared and consequently synchronized
for instance we know that some changes
did take place in several tables however
we are going to sync only the data in
sales orders on the mapping tab we
select the sales orders table and click
compare
as you can see the comparison has been
performed only for a single table
therefore the synchronization will take
place for the selected table too now we
save the decomp file in a folder of our
choice on our PC to use it in the
command line later on
before performing the synchronization we
generate a bat file
specify the previously saved dcom file
as the comparison project and bike
stores versus bike stores and SQL file
as the synchronization file
as soon as the synchronization is over
the generated script opens in the
internal editor we can execute the
synchronization script directly against
the target database
as you can see the data in both source
and Target are equal after the
synchronization
data compare for SQL Server allows you
to create a comparison condition for
particular data in one or more columns
by defining it in the wear filter
to do that we go to the mapping tab
select the desired table click filter
and enter any comparison condition we'd
like
as a result the filtered values for the
category ID and brand ID columns have
been taken into account during the
comparison
the different roles have been
automatically selected as well
again let's save the d-comp project on
our PC for future use
now we are going to generate a bat file
we specify the previously saved decomp
file as the comparison project and bike
stores versus bike stores and SQL file
as the synchronization file
as you can see the data in both source
and Target are equal after the
synchronization
now we execute the script to insert data
into the Brand's reference table and
products table which are linked with a
foreign key next we select source and
Target
after the comparison we can see that the
data differ in two tables namely the
Brand's reference table and the
product's dependent table
suppose we want to sync only the data of
the product's dependent table let's
begin the synchronization in the data
synchronization wizard that opens we
select both execute the script directly
against the target database and refresh
comparison results after successful
synchronization to see the results
immediately after the process is over on
the options tab we clear the disable
foreign keys checkbox this way all the
foreign keys will be taken into account
during the sync now let's click
synchronize as you can see a conflict
occurred in the Target database during
the synchronization when trying to
insert a row into the products table
that is linked by a foreign key to the
brands table
this happened because there was no
corresponding value of the brand ID
field in the Brand's reference table
let's try to synchronize the data again
but this time we will select the disable
foreign keys checkbox in this
configuration the constraints for the
products table will be removed when the
script is executed after the data is
inserted the constraints will be
recreated next we click synchronize
finally the data has been successfully
synchronized to Target however it's
worth mentioning that the Integrity of a
database may be violated if the disable
foreign Keys checkbox is selected
dbforge data compare is not designed for
regular automatic data synchronization
between source and Target this can be
achieved with the help of two tools
working together dbforge data compare
command line and for example Windows
task scheduler or any other task
scheduler even though this works it's
only a custom solution data compare
itself does not have the functionality
to automatically track changes and
deploy them on the target
by running the bat files that have been
created at the synchronization stage we
will get the needed results in the
corresponding SQL files thus using bat
files we can easily automate the process
just download a 30-day free trial of
data compare and find out how simple and
errorless data deployments can be