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:
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:
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:
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:
Script generation is in process... is completed.
2007-12-03 15:41:25. Synchronization script sucessfully created.
2007-12-03 15:41:25. Synchronization started.
The database (local).SampleDatabase will be changed.
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:
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.