ApexSQL Data Diff Command Line Interface (CLI) switches

Applies to
ApexSQL Data Diff

Summary
This article explains the ApexSQL Data Diff CLI switches, and their usage through the examples.

Description

Connection switches

/server1Specifies server and instance name of the source SQL instance
Alias: /s1
Format: /server1:server_name[\instance_name]
/server2Specifies server and instance name of the destination SQL instance
Alias: /s2
Format: /server1:server_name[\instance_name]
Quick tip icon

Quick tip:

If a server is not specified “(local)” instance is assumed

/database1Specifies the name of the source database
Alias: /d1
Format: /database1:SourceDatabaseName
/database2Specifies the name of the destination database
Alias: /d2
Format: /database2:DestinationDatabaseName
/user1Specifies the source login when using SQL Server authentication
Alias: /u1
Format: /user1:sourcelogin
Note: You must use this option together with the
/password1 switch
/user2Specifies the destination login when using SQL Server authentication
Alias: /u2
Format: /user2:destinationlogin
Note: You must use this option together with the
/password2 switch
Quick tip icon

Quick tip:

If a user is not specified a trusted connection is assumed

/password1Specifies the source login’s password when using SQL Server authentication
Alias: /p1
Format: /password1:sourcepassword
Note: You must use this option together with the /user1 switch
/password2Specifies the destination login’s password when using SQL Server authentication
Alias: /p2
Format: /password2:destinationpassword
Note: You must use this option together with the /user2 switch
/backup1Specifies the file name of the source backup
Alias: /b1
Format: /backup1:filepath
/backup2Specifies the file name of the destination backup
Alias: /b2
Format: /backup2:filepath
Quick tip icon

Quick tip:

The full path of a backup file, along with the backup file extension, e.g. .bak, or .trn, has to be defined, so that command could be correct

/backup_set1Specifies the name of the backup set in the source backup
Alias: /bs1
Format: /backup_set1:name
/backup_set2Specifies the name of the backup set in the destination backup
Alias: /bs2
Format: /backup_set2:name
/sourcecontrol_
folder1
Path of a local working folder with script files, when source control is used as source
Alias: /scf1
Format: /sourcecontrol_folder1:folder_path
/sourcecontrol_
folder2
Path of a local working folder with script files, when source control is used as destination
Alias: /scf2
Format: /sourcecontrol_folder2:folder_path
/sourcecontrol_
type1
Type of source control used as the source:

  • git
  • mercurial
  • perforce
  • subversion
  • teamfoundationserver

Alias: /sct1
Format: /sourcecontrol_type1:sc_type
Arguments cannot be combined

/sourcecontrol_
type2
Type of source control used as the destination:

  • git
  • mercurial
  • perforce
  • subversion
  • teamfoundationserver

Alias: /sct2
Format: /sourcecontrol_type2:sc_type
Arguments cannot be combined

/sourcecontrol_
server1
Name of the source control server used as the source (TFS server, Perforce host)

Alias: /scs1
Format: sourcecontrol_server1:[<protocol>
://<hostname>:<portname>/tfs | https://
<accountname>.visualstudio.com
/DefaultCollection]
(example for Team Foundation Server)

Format: /sourcecontrol_server1:<protocol>
://<hostname>:<portname>
(example for Perforce)

/sourcecontrol_
server2
Name of the source control server used as the destination (TFS server, Perforce host)

Alias: /scs2
Format: sourcecontrol_server2:[<protocol>
://<hostname>:<portname>/tfs | https://
<accountname>.visualstudio.com
/DefaultCollection]
(example for Team Foundation Server)

Format: /sourcecontrol_server2:<protocol>
://<hostname>:<portname>
(example for Perforce)

/sourcecontrol_
repository1
Repository of the source control server used as the source (Git, Mercurial, Subversion repository, Perforce depot)

Alias: /scr1
Format: /sourcecontrol_repository1:
<protocol>://<hostname>:<portnumber>/<git_server_name>
/<repository>
(example for Git repository)

Format: /sourcecontrol_repository1:<protocol>
://<hostname>:<portnumber>/<repository>/
(example for Mercurial repository)

Format: /sourcecontrol_repository1:<protocol>
://<hostname>:<portnumber>/svn/
<repository>/
(example for Subversion repository)

/sourcecontrol_
repository2
Repository of the source control server used as the destination (Git, Mercurial, Subversion repository, Perforce depot)

Alias: /scr2
Format: /sourcecontrol_repository2:
<protocol>
://<hostname>:<portnumber>/<git_server_name>
/<repository>
(example for Git repository)

Format: /sourcecontrol_repository2:<protocol>
://<hostname>:<portnumber>/<repository>
(example for Mercurial repository)

Format: /sourcecontrol_repository2:<protocol>
://<hostname>:<portnumber>/svn/<repository>/
(example for Subversion repository)

/sourcecontrol_
user1
Specifies the login used to connect to the source control server used as the source
Alias: /scu1
Format: /sourcecontrol_user1:source_sc_login
/sourcecontrol_
user2
Specifies the login used to connect to the source control server used as the destination
Alias: /scu2
Format: /sourcecontrol_user2:
destination_sc_login
/sourcecontrol_
password1
Specifies the password used to connect to the source control server used as the source
Alias: /scp1
Format: /sourcecontrol_password1:
source_sc_password

Note: You must use this option together with the
/sourcecontrol_user1 switch

/sourcecontrol_
password2
Specifies the password used to connect to the source control server used as the destination
Alias: /scp2
Format: /sourcecontrol_password2:
destination_sc_password

Note: You must use this option together with the
/sourcecontrol_user2 switch

/sourcecontrol_
branch1
Branch name for Git source control type when source control is used as source data source (“master” if omitted)
Alias: /scb1
Format: /sourcecontrol_branch1:
source_branch_name

/sourcecontrol_
branch2
Branch name for Git source control type when source control is used as destination data source (“master” if omitted)
Alias: /scb2
Format: /sourcecontrol_branch2:
source_branch_name

/sourcecontrol_
project1
Project path containing script files on the source control server used as the source (Git, Mercurial, Subversion, TFS folder, Perforce workspace)
Alias: /scj1
Format: /sourcecontrol_project1:$/ProjectName

/sourcecontrol_
project2
Project path containing script files on the source control server used as the destination (Git, Mercurial, Subversion, TFS folder, Perforce workspace)
Alias: /scj2
Format: /sourcecontrol_project2:$/ProjectName

/get_sources_by_
label1
Get script files from the source control project, used as the source, by label (get latest sources if omitted)
Alias: /scl1
Format: /get_sources_by_label1:
SourceLabelName

/get_sources_by_
label2
Get script files from the source control project, used as the destination, by label (get latest sources if omitted)
Alias: /scl2
Format: /get_sources_by_label1:
DestinationLabelName

/collation1Default collation for a source control data source used as the source
Alias: /c1
Format: /collation1: SQL_Latin_1_
General_CP1_CI_AS

/collation2Default collation for a source control data source used as the destination
Alias: /c2
Format: /collation2: SQL_Latin_1_
General_CP1_CI_AS

/compatibility_
level1
Script compatibility level for a source control data source used as the source:

  • SQL2005 [2005]
  • SQL2008 [2008]
  • SQL2012 [2012]
  • SQL2014 [2014]
  • SQL2016 [2016]

Alias: /cl1
Format: compatibility_level1:[2005|2008|2012|2014|2016]
Arguments cannot be combined

/compatibility_
level2
Script compatibility level for a source control data source used as the destination:

  • SQL2005 [2005]
  • SQL2008 [2008]
  • SQL2012 [2012]
  • SQL2014 [2014]
  • SQL2016 [2016]

Alias: /cl2
Format: compatibility_level2:[2005|2008|2012|2014|2016]
Arguments cannot be combined

Specific switches

/projectSpecifies a project file path
Alias: /pr
Format: /project:project.axdd
Quick tip icon

Quick tip:

If a project file is saved outside of the current directory, specify the path where it’s located.

/output_typeSpecifies the output type:

  • sql [s]
  • html [h]
  • html_simple [hs]
  • csv [c]
  • excel [e]
  • csharp [cs]
  • exe_installer [ei]

Alias: /ot
Format: /output_type:[s|h|hp|c|cs|ei]
Note: These arguments can’t be combined

/output_type2Specifies the second output type:

  • sql [s]
  • html [h]
  • html_plain [hp]
  • csv [c]
  • excel [e]
  • csharp [cs]
  • exe_installer [ei]

Alias: /ot2
Format: /output_type2:[s|h|hp|c|cs|ei]
Note: These arguments can’t be combined

/object_typesSpecifies bitwise set of object types to be compared (objects of all types are compared if omitted). Available constants:
4 – Views
8 – Tables
12 – Tables and views

Alias: /o
Format: /object_types:[4|8|12]
Bitwise set constants cannot be combined.

Quick tip icon

Quick tip:

The number 4 is a bitwise constant that represents views, the number 8 is a bitwise constant that represents tables, and the number 12 is represents both views and tables in switches

/output_namePrimary output name/folder path of the comparison results, synchronization script or executable packages (“sync.sql” filename, “report.html” or “report_simple.html” filename, “csv_report” folder, “report.xml” filename, “servername.databasename.exe” filename or “servername.databasename” folder if omitted)
Alias: /on
Format: /output_name:
C:\Projects\synchronization.sql
/output_name2Specifies the path of the comparison report or the synchronization script
Alias: /on2
Format: /output_name2:
C:\Projects\synchronization.sql
Quick tip icon

Quick tip:

Two output files can be generated at once e.g. the synchronization script and the HTML report (e.g. /on:sync.sql /on2:report.htm)
If an output file name is not specified, assumed names are:

  • sync.sql for SQL output format
  • report.htm for HTML output format
  • report.csv for CSV output format
/includeIncludes specific objects in processing. Objects can be specified by first specifying a bitwise set and a regular expression.
Alias: /inc
Format: /include:bitwise_constant:regular
expression1: regular expression2:…”

Note: Multiple includes can be specified with a colon in between.
/excludeExcludes specific objects from processing. Objects can be specified by first specifying a bitwise set and a regular expression.
The /exclude switch is processed after the /include switch (if it is specified)
Alias: /exc
Format: /exclude:bitwise_constant:regular
expression1: regular expression2:…”

Note: Multiple includes can be specified with a colon in between.
/ignore_project_
object_selection
Ignores object selection and mapping specified in the project file
Alias: /ipos

/ignore_new_objectsIgnores newly added objects from a saved project file during the comparison and synchronization process.
Alias: /ino

/data_cache_
location
A folder that is used as temporary storage for data retrieved from SQL Server. By default, this folder is located on system drive. If free disk space on system drive is not enough, select folder on another drive where more free disk space is available.
Alias: /dc1
Format: /data_cache_location:[path to data cache location]
/authorSpecifies the name of the reports Author
Alias: /auth
Format: /author:Name
/legalSpecifies the legal information about the report
Alias: /leg
Format: /legal:Information

General synchronization switches

/encodingSpecifies the output file encoding:

  • ANSI [a]
  • Unicode [u]
  • UTF7 [u7]
  • UTF8 [u8]

Alias: /enc
Format: /encoding:[a|u|u7|u8]
Note: These arguments can’t be combined

Quick tip icon

Quick tip:

If no output encoding was specified, Unicode encoding is assumed

/synchronizeExecutes the synchronization script
Alias: /sync
Format: /synchronize
/backupAllows providing the path for the destination backup to be saved to a specific location, or:

  • None [n] – Prevents the backup from being created
  • Default [dft] – Saves the backup in the installation directory

Alias: /bu
Format: /backup:[Default
|None|”C:\path_to_backup”]

Note: These arguments can’t be combined
Omitting this switch will result in the destination backup being created in the installation directory

Quick tip icon

Quick tip:

The switch /backup should be used along with the switch /synchronize

/batch_every_n_
rows
Number of rows between GO statements in the script
Alias: /benr
Format: /batch_every_n_rows:
number_of_rows_in_batch
/destination_
to_source
Change the synchronization direction
Alias: /dts
/script_formatSpecifies the script formatting options:

  • None [n] – Sets all options to off
  • Script header [sh] – Includes the script header
  • Comments [cmt] – Includes automatically generated comments in the synchronization script
  • PrintStatements [ps] – Includes automatically generated PRINT statements used to display information on the currently executed operation to the synchronization script
  • Default [dft] – Uses the default settings

Alias: /sf
Format: /script_format:
set_of_options_delimited_by_space

Note: These arguments can be combined with each other except for None and Default
Omitting this switch will result in the default settings being used

/prescript_
file_path
Full path to the pre-processing script file path, along with the “.sql” extension.
Alias: /prefp
Format: /prescript_file_path:
C:\script.sql
/postscript_
file_path
Full path to the post-processing script file path, along with the “.sql” extension.
Alias: /postfp
Format: /postscript_file_path:
C:\script.sql
/sourcecontrol_
sync_comments
Comments that should be applied during synchronization to the source control data source
Alias: /scsc
Format: /sourcecontrol_sync_comments:
CommitComment
/sourcecontrol_
sync_label
Label that should be applied during synchronization to the source control data source
Alias: /scsl
Format: /sourcecontrol_sync_label:
LabelName
/exclude_
recomparison_
summary
Excludes the recomparison and synchronization summary after the synchronization process
Alias: /erss

Comparison switches

/columnsSelect columns of objects to compare. The format of the argument is:

ObjectType1:ObjectName1:columnName1:…:columnName1

ObjectTypeK:ObjectNameM:columnNameM:…:columnNameN

Alias: /cols
Format: /columns:set_columns_of_
objects_to_compare

/ignore_columnsIgnore columns of objects in the comparison process. The format of the argument is:

ObjectType1:ObjectName1:columnName1:…:columnNameN …
ObjectTypeK:ObjectNameM:columnName1:…:columnNameP

Alias: /icols
Format: /ignore_columns:
set_columns_to_ignore_in_comparison

/comparison_keysSet comparison keys for groups of objects. The format of the argument is:

ObjectType1:ObjectName1:comparisonKey1

ObjectTypeK:ObjectNameN:comparisonKeyN

Alias: /cks
Format: /comparison_keys:
set comparison keys for groups of objects

/data_comparison_
options
Set data comparison options:

  • None [n]
  • IgnoreROWGUIDColumns [irc]
  • IgnoreBLOBColumns [ibc]
  • IgnoreTimestampColumns [itc]
  • IgnoreCaseInNames [icn]
  • IgnoreCaseInTextDataTypes [icct]
  • IgnoreTrailingSpaces [its]
  • TreatEmptyStringsAndNullsAsEqual [tesne]
  • IgnoreComputedColumns [icc]
  • IgnoreUnderscoresInNames [iun]
  • CompareFloatsWithMaxPrecisuon [cfmp]
  • ShowEqualRows [ser]
  • UseChecksum [ucs]
  • Default [dft] – combination of IgnoreBLOBColumns, IgnoreTimestampColumns and IgnoreROWGUIDColumns options

Alias: /dco
Format: /data_comparison_options:
set_of_the_options_delimited_by_space

Note: These arguments can be combined with each other except for None and Default
Omitting this switch will result in the default settings being used

/schema_mappingFull path to the schema mapping file, along with the “.axmp” extension (default schema mapping will be used for comparing objects if the switch is omitted)
Alias: /sm
Format: /schema_mapping:”SchemaMapping.axmp”

Synchronization switches

/date_formatThe date format applied to all datetime or smalldatetime fields in the DML statements
Default [d] – sets value to yyyyMMdd HH:mm:ss.fff
None [n] – sets value to none.
Alias: /df
Format: /date_format:date_format_string
/records_to_
synchronize
Set record types for synchronization:

  • Missing [m]
  • Additional [a]
  • Different [d]
  • Default [dft] – combination of Missing, Additional and Different options

Alias: /rts
Format: /records_to_synchronize:set_of_the
_options_delimited_by_space

Note: These arguments can be combined

/data_sync_optionsSet data synchronization options:

  • None [n]
  • CheckRowExistanceBeforeInsert [crebi]
  • DisableCheckConstraints [dcc]
  • DisableDMLTriggers [dmt]
  • DisableDDLTriggers [ddt]
  • DisableForeignKeys [dfk]
  • DropNotUniqueIndexes [dnui]
  • DropUniqueIndexes [dui]
  • ReenableCheckConstraintsWithCheck [recwc]
  • Default [dft] – combination of DisableForeignKeys, DisableDDLTriggers, DisableDMLTriggers and DisableCheckConstraints options

Alias: /dso
Format: /data_sync_options:
set_of_the_options_
delimited_by_space

Note: These arguments can be combined

/warnings_handlingSpecifies warnings handling before the application execution:

  • IgnoreWarnings [iw] – warnings will be ignored during the application execution
  • AbortOnMediumOrHighWarnings [amh] – application execution will be aborted on medium or high warnings
  • AbortOnHighWarnings [ah] – application execution will be aborted only on high warnings
  • Default [dft] – sets the IgnoreWarnings option

Alias: /wh
Format: /warnings_handling:
set_warning_handling

Note: These arguments cannot be combined

Reporting switches

/data_html_report_
options
Set primary data html report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • ComparisonOptions [co]
  • ReportOptions [ro]
  • DataRows [dr]
  • CheckedRowsOnly [cr]
  • Default [dft] – combination of EqualObjects, DifferentObjects, ComparisonOptions and ReportOptions options

Alias: /dhtmo
Format: /data_html_report_options:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

/csv_report_
options
Set primary data csv report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • Equal [er]
  • Different [dr]
  • Missing [mr]
  • Additional [ar]
  • Default [dft] – combination of EqualObjects, DifferentObjects, Equal, Different, Missing and Additional options

Alias: /csvo
Format: /csv_report_options:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

/excel_report_
options
Set primary data excel report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • ComparisonOptions [co]
  • ReportOptions [ro]
  • DataRows [dr]
  • IndividualFile [if]
  • CheckedRowsOnly [cr]
  • Default [dft] – combination of EqualObjects, DifferentObjects, ComparisonOptions and ReportOptions options

Alias: /exco
Format: /excel_report_options:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

/data_html_report_
options2
Set secondary data html report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • ComparisonOptions [co]
  • ReportOptions [ro]
  • DataRows [dr]
  • CheckedRowsOnly [cr]
  • Default [dft] – combination of EqualObjects, DifferentObjects, ComparisonOptions and ReportOptions options

Alias: /dhtmo2
Format: /data_html_report_options2:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

/csv_report_
options2
Set secondary data csv report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • Equal [er]
  • Different [dr]
  • Missing [mr]
  • Additional [ar]
  • Default [dft] – combination of EqualObjects, DifferentObjects, Equal, Different, Missing and Additional options

Alias: /csvo2
Format: /csv_report_options2:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

/excel_report_
options2
Set secondary data excel report options:

  • EqualObjects [e]
  • DifferentObjects [d]
  • CheckedOnly [c]
  • ComparisonOptions [co]
  • ReportOptions [ro]
  • DataRows [dr]
  • IndividualFile [if]
  • CheckedRowsOnly [cr]
  • Default [dft] – combination of EqualObjects, DifferentObjects, ComparisonOptions and ReportOptions options

Alias: /exco2
Format: /excel_report_options2:
set_of_the_options_delimited_by_space

Note: These arguments can be combined

Package switches

/package_
compression
Reduces the size of the resulting executable by using compression
Alias: /pkcm
Format: /package_compression
/package_errorSets the package error handling (“ask” if omitted):

  • Abort – package execution is aborted and all changes are rolled back
  • Ignore – all errors are ignored during the package execution
  • Ask – user confirmation is required for each encountered error, whether to abort or continue the package execution

Alias: /pke
Format: /package_error:[Abort|Ignore|Ask]
Note: These arguments cannot be combined.

/package_authorPlaces the author tag into the about box of the package.
Alias: /pkaa
Format: /package_author:Author_name
/package_companyPlaces the company tag into the about box of the package.
Alias: /pkco
Format: /package_company:Company_name
/package_legalPlaces the legal tag into the about box of the package
Alias: /pkal
Format: /package_legal:Legal_notice
/package_commentsPlaces the comments tag into the about box of the package
Alias: /pkac
Format: /package_comments:Comments
/vs_verSets package Visual Studio version (“VS2015” if omitted):

  • VS2005
  • VS2008
  • VS2010
  • VS2012
  • VS2013
  • VS2015

Alias: /vrv
Format: /vs_ver:[VS2005|VS2008|VS2010|VS2012| VS2013|VS2015]
Note: These arguments cannot be combined.

/indicate_default_dbSets the default database the generated package should be executed against.
Alias: /ddb
Format: /indicate_default_db:
Database_name
/create_mfsCreates a manifest file that contains the information regarding OS compatibility versions and sets the Windows user account control level to allow the application to run without any additional permissions.
Alias: /cmf

/create_datCreates a .dat file which contains SQL code for creating new or updating the existing database.
Alias: /cdf

/create_comCreates a CLI (Command Line Interface) executable for package.
Alias: /ccf

/run_exe_nowRuns the created executable installer immediately upon generating.
Alias: /ren

/open_in_vsOpens the generated C# solution in Visual Studio upon creation.
Alias: /ovs

Learn more about additional switches from article on this link.

Return codes

ApexSQL applications have two groups of return error codes:

  1. Common return error codes – learn more about these from here

  2. Specific return error codes – for ApexSQL Data Diff it’s the:

    102 – The output file creating failed

Examples

Note: All examples assume that the current directory is the application directory. If not, the full path to ApexSQLDataDiff.com must be specified.

Compare the data in all tables in two local databases:

ApexSQLDataDiff.com /d1:AdventureWorks2008 /d2:AdventureWorks2012

Compare the data in all tables in two databases on different servers:

ApexSQLDataDiff.com /s1:SourceServer /d1:AdventureWorks2008
/s2:DestinationServer /d2:AdventureWorks2012

Compare the data in all tables in two databases on different servers using SQL Server authentication:

ApexSQLDataDiff.com /s1:SourceServer /d1:AdventureWorks2008
/u1:SourceMaster /p1:passtopass /s2:DestinationServer
/d2:AdventureWorks2012
/u2:DestinationChange /p2:changes

Using a project file, overriding the destination username and password with switches, and specifying the path to the synchronization script:

ApexSQLDataDiff.com /pr:SynchProject.axdd /u2:UserChanges /p2:passiton /on:C:\Projects\SynchScript.sql

Using an argument file, e.g. “argfile.xml”, to execute commands:

ApexSQLDataDiff.com /argfile:argfile.xml

Compare the data only in tables which are named “Person” on different servers, using SQL Server authentication, set the data synchronization options, specify html as output type, specify the path of the comparison report, backup the destination database, synchronize, and display all messages on the processed operation:

ApexSQLDataDiff.com /s1:SourceServer /d1:AdventureWorks2008
/u1:SourceMaster /p1:passtopass /s2:DestinationServer
/d2:AdventureWorks2012
/u2:DestinationChange /p2:changes /inc:8:^Person$ /dso:dcc
/on:C:\Projects\CompReport.htm /ot:hmtl /bu:C:\Backups /sync /v

Compare the data in all tables in two databases on different servers using SQL Server authentication, set data synchronization options, set synchronization record types, display all messages on the processed operation, specify the path of the synchronization script, and overwrite the existing synchronization script:

ApexSQLDataDiff.com /s1:SourceServer /d1:AdventureWorks2008
/u1:SourceMaster /p1:passtopass /s2:DestinationServer
/d2:AdventureWorks2012
/u2:DestinationChange /p2:changes /dso:dfkt dnui dcc /rts:m a
/on:C:\Projects\SynchScript.sql /v /f