Compare Data Using The Command Line Utility

The SQL Studio Data Compare command line interface helps to automate database comparison and synchronization. It allows pre-configured data compare and synchronization jobs to be scheduled and executed at off-times. The command line interface can use saved projects or to create projects on the fly.

Here is the syntax for using the SQL Studio Data Compare command line utility:

DataCompare_cmd.exe <list of options>

Here are the options that can be used with the SQL Studio Data Compare command line interface:

/?               Shows this information
-x               Executes the generated script
-v               Shows the generated script on the standard output
-f [FILE]        Saves the generated script to FILE
-s [PROJECT]     Uses the configuration from the saved PROJECT for the synchronization
-log [FILE]      Specifies the operation log file
/SR              Sync differences to right
/SL              Sync differences to left
/ALL             Sync all to left/right; this option will trigger Sync for all records (full synchronization), not only differences
/SKIP            Skip tables with no Primary Key
/INCLFK          Includes all (if any) not included foreign key tables in the database sync
/DUMP            Generate INSERT statements only
/CMP             Perform a Database compare (valid only for Database projects)
-rpt [FILE]      Specifies the location and name of the report file with the results of the Database compare
/LINKL           Specifies that the left server will be linked during compare. NOTE: After specifying this option you should also specify the '-isecl' option for using Integrated Security or use the '-ul' and '-pl' options for specifying valid SQL credentials for the left server
/LINKR           Specifies that the right server will be linked during compare. NOTE: After specifying this option you should also specify the '-isecr' option for using Integrated Security or use the '-ur' and '-pr' options for specifying valid SQL credentials for the right server
-template [FILE] Specifies a custom report template
-ds [PROJECT]    Deletes PROJECT from the list of saved projects
-as [PROJECT]    Adds PROJECT to the list of saved projects with the following settings:
-sl [SERVER]     Left Server
-ul [USER]       User for login to left server
-pl [PASSW]      Password for login to left server
-isecl           Use integrated security for left server
-dbl [DB]        Database from left server
-schl [SCHEMA]   Schema name for the table from left database
-tbll [TABLE]    Table from left database
-sr [SERVER]     Right Server
-ur [USER]       User for login to right server
-pr [PASSW]      Password for login to right server
-isecr           Use integrated security for right server
-dbr [DB]        Database from right server
-schl [SCHEMA]   Schema name for the table from the right database
-tblr [TABLE]    Table from right database

For more information and examples of using the SQL Studio Data Compare command line utility see the following knowledge base article:
http://forum.sqlstudio.com/kb.aspx?id=3

How to Compare Data in Tables Using Data Compare 2.5

Comparing at the table level helps to focus on synchronization of two tables in two databases. It is the fastest way to see the changes in two tables.

In Table Compare screen you can view the differences of two tables compared by row.

The background color of the table shows the status of the current row:

- If the background color of the row is white then the two rows in both tables are the same.

- If the background color of the row is light red then the rows have some differences. The different fields are in red color.

- If the background color of a cell is light blue and the color is blue then the row exists in one of the tables but does not exist in the other one.

- If the background color of the row is light yellow then the row is changed and the changes are not committed yet.

For full (automatic) synchronization of the table use the Sync to Left and Sync to Right buttons. For example, Sync to Right will make the content of the Right table to be the same as the Left side table. That way the Left side table becomes the source and the Right side table is the target for the synchronization process.

For more technical details see:
http://www.sqlstudio.com/Data_Compare_Technical_Details.aspx

How to Compare Data in Databases Using Data Compare 2.5

Comparing at the database level helps to synchronize all tables in two databases. It is a fast and convenient way to merge data changes. Another option is to make a custom selection of tables to compare.

The database compare starts at the Compare Wizard by selecting the Databases Compare type and then making a selection of Custom table selection or Compare all tables.

You can view the list of all matching tables (matched by name) on the Database Compare form. If Compare all tables option is selected in the wizard then the number of matching, different, missing and additional rows in each table will be shown automatically. In case when the Custom table selection option is selected in the wizard you have to make a custom selection of the tables that need to be compared and then click on Summary Compare Selection button.

There is statistical information about the number of matching, different, missing and additional records besides each table name. The background color of the rows shows the compare status of each table:

- If the background color is white then the tables are same or they are not compared yet.
(Note: tables that are not compared do not have summarized numbers.)

- If the background color is red then one (or both) of the tables do not have a Primary Key.
(Note: only tables with primary key can be synchronized. If the table has unique identifier it may be used as a primary key for compare.)

- If the background color is light red then the tables are different.

For full (automatic) synchronization of selected tables use the Sync to Left and Sync to Right buttons. For example, Sync to Right will make the content of the Right database tables to be the same as the Left side database tables. That way the Left side tables become the source and the Right side tables are the target for the synchronization process.

For more details see:
http://www.sqlstudio.com/Data_Compare_Overview.aspx

Welcome

Welcome to our team Web log! We dedicate this space for posting technical information, tips and tricks, and how to articles about SQL Studio Data Compare. Any comments and ideas are appreciated.