Compare and synchronize databases by a schedule (version 2012 and earlier)

To synchronize tables stored on different servers on a schedule, you can use the built-in Command Line Comparison and Synchronization tool.

You can either manually configure the settings of a command line for the console mode utility (SQLECmd.exe for SQL Examiner or SQLDeCmd.exe for SQL Data Examiner) or use the built-in Console Mode Settings Wizard that may be found in the Tools menu of the GUI-version of the program.

Specifying the necessary parameters in a command line

For example, you may want to synchronize the Customer and WorkOrder tables stored on your local server and on the RemoteServer server every night.

Specify the necessary parameters for SQL Data Examiner Command Line tool:

SqlDeCmd.exe /server1:LocalServer /WinAuth1 /Db1:SampleDatabase /server2:RemoteServer /uid2: login /pwd2:p@ssw0rd /Db2:SampleDatabase /synchronize /BackupTargetDB /script:c:\Scripts\%YYYY%MM%DD.sql /include:dbo.Orders, dbo.OrderDetails

In this example:

/server1:LocalServer /WinAuth1 /Db1:SampleDatabase – the name of the source database is SampleDatabase; the database is located on the local server; it is necessary to connect to the server using the Windows Authentication.

/server2:RemoteServer /uid2: login /pwd2:p@ssw0rd /Db2:SampleDatabase – the name of the target database is SampleDatabase; it is located on the RemoteServer server; to connect, use login as the login name and p@ssw0rd as the password.

/include:dbo.Customer, dbo.WorkOrder – all the actions should be performed on the dbo.Customer and dbo.WorkOrder tables.

/synchronize /BackupTargetDB /script:c:\Scripts\%YYYY%MM%DD.sql – synchronize the data of the tables in the target database with the data in the source database.

You may want to log the actions performed by the tool for tracking and auditing purposes. By default, the program logs its actions and saves this log to [Documents and Settings]\user_name\Application Data\TulaSoft\Sql Data Examiner\Logs, if no /NoLog parameter is used. You can specify a different location using the /log parameter.

Customizing the settings using the Console Mode Settings Wizard

The above settings may be customized using the Console Mode Settings Wizard as well.

You can specify the settings on the command line or in a config file, that may be more convenient to use. By putting these settings in a config file, you don’t have to edit a long command line; you only need to enter the necessary changes to a structured config file. Besides, a config-file, that the wizard generates, includes more information than a manually created command line.

To learn more, look through the “Configuration File Format” section of the Help file.

To apply the configured settings, enter the following command:

SQLDeCmd.exe /config:config.xml.

In the first step of the wizard, specify where the settings should be saved. Follow the instructions of the wizard. In its last step, review the generated config file:

<?xml version="1.0" encoding="utf-16" ?> <config> <server1>RemoteServer</server1> <database1>SampleDatabase</database1> <winauth1>True</winauth1> <server2>(local)</server2> <database2>SampleDatabase</database2> <winauth2>True</winauth2> <log>C:\Documents and Settings\Administrator \Application Data\TulaSoft\SQL Data Examiner\Logs\log.txt</log> <force /> <script>c:\Scripts\SampleDatabase-%YYYY-%MM-%DD.sql</script> <synchronize /> <backuptargetdb> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL \Backup\SampleDatabase_db_%YYYY%MM%DD%HH%mm.bak </backuptargetdb> <options> <option name="disabletriggers">True</option> <option name="disableforeignkeys">True</option> <option name="synchronizeidentity">True</option> <option name="usetransaction">True</option> </options> <includes> <include table="[dbo].[Customer]" records="OnlyDb1, OnlyDb2, OnlyDifferent" columns="*" keycolumns="[CustomerID]" /> <include table="[dbo].[WorkOrder]" records="OnlyDb1, OnlyDb2, OnlyDifferent" columns="*" keycolumns="[WorkOrderID]" /> </includes> </config>

Creating a task to run the synchronization on a schedule

Open Control Panel » Scheduled Tasks. Click “Add Task” and follow the standard procedure of creating a task that will run periodically. Here is an example of the settings window with such a task; we have put the necessary settings in a config-file:

Run the task. When it is completed, a log.txt file will appear in the [profile]\Application Data\TulaSoft\[Tool name]\Logs folder. Here is an extract from this file:

Retrieving data:
[dbo].[Customer]: OK
[dbo].[WorkOrder]: OK

Script generation is in process... is completed.
2007-12-03 15:41:25. Synchronization script sucessfully created.
File: c:\Scripts\SampleDatabase-2007-12-03.sql
2007-12-03 15:41:25. Synchronization started.
The database (local).SampleDatabase will be changed.
Synchronization log:

Start synchronization.
Commit synchronization.

2007-12-03 15:42:11. Synchronization completed.

If you now run this task again, new lines will appear in log.txt. Now the program shows other messages:

Retrieving data:
[dbo].[Customer]: OK
[dbo].[WorkOrder]: OK

There are no specified records in the selected tables.

This time no differences between the tables were found,so and there is no need to synchronize anything.

Note: If your tables include millions of records, it will be more convenient to use replication. However, if the tables are not that large, or are located on the same server, it is easier and more cost effective to synchronize them on a schedule using the SQL Data Examiner Command Line tool.