SQL Data Compare command-line XML argument file examples

Category: How do I?
Date: 16 Jul 2009
Product: SQL Data Compare
Versions: 6.x,7.x, 8.x

The command-line component of SQL Data Compare that may be used to run comparisons on a schedule or as part of a batch of synchronization processing can be configured using an XML file to specify the objects that will be included and the options for comparing data. The XML command files correspond roughly to what is available via command-line, but there are a few subtle differences. This article contains examples of XML argument files for SQL Data Compare's command-line component.

To specify an XML argument file to SQLCOMPARE.exe or SQLDATACOMPARE.exe, the /argfile:<filename.xml> argument is used in place of any other command-line arguments.

Including only certain columns in a data comparison of a table:

<?xml version="1.0"?>
<commandline>
      <database1>TestDataBase</database1>
   <database2>TestDataBase2</database2>
   <columns>testTable:testdate,test_id</columns> 
</commandline>

Including all tables tables except WidgetReferences and excluding all views:

<?xml version="1.0"?>
<commandline>
<server1>localhost</server1>
<server2>localhost</server2>
<database1>WidgetDev</database1>
<database2>WidgetLive</database2>
<include>Table</include>
<exclude>Table:\[WidgetReferences\]</exclude>
<verbose/>
</commandline>

Exclude all tables owned by Support, or in the Support schema, using SQL authentication on the first server and Windows on the second:

<?xml version="1.0"?>
<commandline>
<server1>localhost</server1>
<username1>sa</username1>
<password1>P@ssw0rd</password1>
<server2>localhost</server2>
<database1>WidgetDev</database1>
<database2>WidgetLive</database2>
<exclude>Table:\[Support\].\[</exclude>
</commandline>

Compare all tables and views whose names begin with 'Widget', except for WidgetReferences:

<?xml version="1.0"?>
<commandline>
<server1>localhost</server1>
<server2>localhost</server2>
<database1>WidgetDev</database1>
<database2>WidgetLive</database2>
<exclude>Table:\[Widget^(References)</exclude>
</commandline>

Specifying a custom comparison key: Cannot be done from the command-line. For this, use the SQL Data Compare Interface to create a project file, and use this project file from the command-line program with the /pr argument.

Specifying a WHERE clause from an XML command file is not supported either, and must be accomplished using a project file in the same ways as specifying a comparison key.

Erasing the default comparison options and adding the options 'output comments' and 'disable triggers' and outputting a synchronization script:

<?xml version="1.0"?>
<commandline>
<database1>WidgetDev</database1>
<database2>WidgetLive</database2>
<options>n,oc,t</options>
<scriptfile>migration.sql</scriptfile>
</commandline>

Adding the verbose switch to cause more information about the comparison process to be written:

<?xml version="1.0"?>
<commandline>
<database1>WidgetDev</database1>
<database2>WidgetLive</database2>
<verbose/>
</commandline>

Comparing a live database to a backup file (version 6 and up):

<?xml version="1.0"?>
<commandline>
   <backup1>D:\MSSQL\BACKUP\BackupOfFirstDatabase.sqb</backup1>
   <database2>SecondDatabaseName</database2>
</commandline>