Automate data comparison and synchronization from the command line

The guide describes the following topics:

You can automate SQL data comparison and synchronization using the command line.

Run comparisons from the command line

Note

To run the Command Line in Windows 10:

From the Desktop Mode

  • Move the mouse cursor to the exact lower left corner until the desktop shortcuts menu appears.
  • Right-click to see the shortcut menu and than click Run.

From the Charms Bar

  • Move your mouse to the upper right corner until the Charms Bar appears.
  • Select Apps from the list and type ‘run’ in the search box.
  • Click Run from the search results.

Use Windows + R Shortcut Keys

  • In desktop mode, press the Windows + R keys, to show the Run command line.
  • In the window that opens, type cmd and press Enter.

1. Type a path to the datacompare.com file located in the dbForge Data Compare for SQL Server installation folder on your PC, and press the SPACE bar.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server\datacompare.com

Note

The path to the executable file will differ depending on which tools suite you have installed Data Compare with - Compare Bundle or SQL Tools. Make sure that you have specified a correct path. If you have installed Data Compare as part of SQL Tools, change the path accordingly:

C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Data Compare for SQL Server\datacompare.com

2. Specify command line arguments: connection strings and actions you want to run. For example, specify the type of the operation.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /?

3. Specify the objects that will be compared and synchronized.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /compfile:"D:\mycompdoc.dcomp"

4. Synchronize data directly into the database and generate a synchronization script file.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /compfile:"D:\mycompdoc.dcomp" /sync:"D:\mysync.sql"

5. Generate a comparison and synchronization protocol into a file.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /compfile:"D:\mycompdoc.dcomp" /sync:"D:\mysync.sql" /log:"D:\mylog.log"

6. Compose a comparison report.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /compfile:"D:\mycompdoc.dcomp" /sync:"D:\mysync.sql" /log:"D:\mylog.log" /report:"D:\myreport.html"

7. Specify the report format. HTML and EXCEL formats are available.

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /datacompare /compfile:"D:\mycompdoc.dcomp" /sync:"D:\mysync.sql" /log:"D:\mylog.log" /report:"D:\myreport.html" /reportformat:HTML

8. Press ENTER to run the process.

Tip

To see a quick help on the arguments available in the command line, type the following:

datacompare.com /?

datacompare.com /datacompare /?

Generate a file with command-line arguments

To generate a file with command-line arguments, do the following:

1. Open the Data Comparison wizard using one of the following ways:

  • On the standard toolbar, click New Data Comparison.
  • On the Start page, click New Data Comparison.
  • On the File menu, click New > New Data Comparison.
  • On the standard toolbar, click Edit Comparison if you want to generate a file for the already executed schema comparison and want to change comparison properties.

2. On the Source and Target page of the wizard, select the source and target data sources you want to compare.

3. Optional: You can through other wizard pages to tune the data comparison.

4. On the Options page, click Save settings to a command line arguments file in the upper right corner. In the Save As window that opens, specify the file name and location to save the file and then click Save.

The selected Source and Target connection strings along with comparison options will be saved as a .txt file command-line arguments. You can edit the values of comparison options in the file. For more information, refer to Options used in the command line.

Note

The generated file does not contain password information for security purposes. You should edit the file and enter the passwords manually.

The file does not contain password information

For more information about the available additional arguments you can use with the /datacompare arguments, see Switches used in the command line.

The command line string with /argfile parameter will be written as follows:

C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server>datacompare.com /argfile:file_name.txt

Generate a file name dynamically

To begin with, you need to create a batch file that will generate a file name dynamically during the execution. The timestamp for the current day will be added to the file name automatically.

To do this, perform the steps:

1. In any third-party text editor, for example, Notepad or Visual Studio Code, open a new blank document and enter the following script:

set TimeStamp=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%


"C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Data Compare for SQL Server\datacompare.com" /datacompare /source connection:"Data Source=DBFSQLSRV\SQL2022;Initial Catalog=AdventureWorks2022;Integrated Security=False;User ID=yourusername" /target connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=AdventureWorks2022Test;Integrated Security=False;User ID=yourusername" /log:"D:\Log_File_%TimeStamp%.log" /sync:"Update_Script_%TimeStamp%.sql"

The following data should be replaced with your actual parameters:

Parameter Description
D:\Log_File_%TimeStamp%.log Path to the output file that stores log data
Update_Script_%TimeStamp%.sql Name of the file that stores the generated synchronization script. The file will be saved in the same directory as the batch file

2. Save the file with the .bat extension.

After the batch file has been created, navigate to the file location and double-click the file to execute it. The result will be written to the log file with a timestamp added to its name for the current day. It might be as follows:

Result

See also: How to set up email delivery for data comparison logs

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Data Compare for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Data Compare in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Data Compare for your business, request a demo to see it in action.
Ready to start using dbForge Data Compare for SQL Server?
Download a free 30-day trial