Applies to
ApexSQL Data Diff
Summary
This article explains the ApexSQL Data Diff CLI switches, and their usage through the examples.
Description
Connection switches
| /server1 | Specifies server and instance name of the source SQL instance Alias: /s1 Format: /server1:server_name[\instance_name] |
| /server2 | Specifies server and instance name of the destination SQL instance Alias: /s2 Format: /server1:server_name[\instance_name] |
|
Quick tip: If a server is not specified “(local)” instance is assumed |
| /database1 | Specifies the name of the source database Alias: /d1 Format: /database1:SourceDatabaseName |
| /database2 | Specifies the name of the destination database Alias: /d2 Format: /database2:DestinationDatabaseName |
| /user1 | Specifies the source login when using SQL Server authentication Alias: /u1 Format: /user1:sourcelogin Note: You must use this option together with the /password1 switch |
| /user2 | Specifies 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: If a user is not specified a trusted connection is assumed |
| /password1 | Specifies 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 |
| /password2 | Specifies 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 |
| /backup1 | Specifies the file name of the source backup Alias: /b1 Format: /backup1:”SourceBackup.bak” |
| /backup2 | Specifies the file name of the destination backup Alias: /b2 Format: /backup2:”DestinationBackup.bak” |
|
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_set1 | Specifies the name of the backup set in the source backup Alias: /bs1 Format: /backup_set1:SourceBackupSetName |
| /backup_set2 | Specifies the name of the backup set in the destination backup Alias: /bs2 Format: /backup_set2:DestinationBackupSetName |
| /sourcecontrol_ folder1 |
Path of a local working folder with script files, when source control is used as source Alias: /scf1 Format: /sourcecontrol_folder1:”SourceControlFolderSource” |
| /sourcecontrol_ folder2 |
Path of a local working folder with script files, when source control is used as destination Alias: /scf2 Format: /sourcecontrol_folder2:”SourceControlFolderDestination” |
| /sourcecontrol_ type1 |
Type of source control used as the source:
Alias: /sct1 |
| /sourcecontrol_ type2 |
Type of source control used as the destination:
Alias: /sct2 |
| /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: “destination_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” |
| /script_folder1 | Specifies the path of the script folder to be used as the source Alias: /sf1 Format: /script_folder1:”SourceScriptFolder” |
| /script_folder2 | Specifies the path of the script folder to be used as the destination Alias: /sf2 Format: /script_folder2:”DestinationScriptFolder” |
| /collation1 | Default collation for a source control data source used as the source Alias: /c1 Format: /collation1: SQL_Latin_1_ General_CP1_CI_AS |
| /collation2 | Default 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:
Alias: /cl1 |
| /compatibility_ level2 |
Script compatibility level for a source control data source used as the destination:
Alias: /cl2 |
Specific switches
| /project | Specifies a project file path, along with “.axdd” extension Alias: /pr Format: /project:”ProjectName.axdd” |
|
Quick tip: If a project file is saved outside of the current directory, specify the path where it’s located. |
| /output_type | Specifies the primary output type of the comparison results, synchronization script, or executable packages:
Alias: /ot |
| /output_type2 | Specifies the secondary output type of the comparison results, synchronization script, or executable packages:
Alias: /ot2 |
| /object_types | Specifies 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: 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_name | Primary output name/folder path of the comparison results, synchronization script or executable packages (“Synchronize_%date%_%time%.sql” filename, “DataDifference_%date%_%time%.html” filename, “csv_export” folder, “DataDifference_%date%_%time%.xml” filename, “servername.databasename.exe” filename or “servername.databasename” folder if omitted) Alias: /on Format: /output_name: ”Synchronize_%date%_%time%.sql” |
| /output_name2 | Secondary output name/folder path of the comparison results, synchronization script or executable packages (“Synchronize_%date%_%time%.sql” filename, “DataDifference_%date%_%time%.html” filename, “csv_export” folder, “DataDifference_%date%_%time%.xml” filename, “servername.databasename.exe” filename or “servername.databasename” folder if omitted) Alias: /on2 Format: /output_name2: ”DataDifference_%date%_%time%.html” |
|
Quick tip: Two output files can be generated at once, for example the synchronization script and the HTML report (e.g. /on:Synchronize_%date%_%time%.sql and /on2:DataDifference_%date%_%time%.html) |
| /include | Includes 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:…” Example: /include:8:”Address$” (includes all tables that end with Address) Note: Multiple includes can be specified with a colon in between. |
| /exclude | Excludes 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:…” Example: /exclude:4:”^Cou” (excludes all views that start with Cou Note: Multiple includes can be specified with a colon in between. The /exclude switch is processed after the /include switch (if it is specified) |
| /ignore_project_ object_selection |
Ignores object selection and mapping specified in the project file Alias: /ipos |
| /ignore_new_objects | Ignores 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: ”DataCacheFolder” |
| /output_elements | Specifies a set of the optional output elements:
Alias: /oe |
| /author | Specifies the name of author in the header of the comparison report or the synchronization script Alias: /auth Format: /author:”John Doe” Note: If the /author switch is specified, its dependent switch and value /output_elements:a need to be provided |
| /legal | Specifies the legal information in the header of the comparison report or the synchronization script Alias: /leg Format: /legal:”© ApexSQL LLC” Note: If the /legal switch is specified, its dependent switch and value /output_elements:l need to be provided |
General synchronization switches
| /encoding | Specifies the output file encoding:
Alias: /enc |
|
Quick tip: If no output encoding was specified, Unicode encoding is assumed |
| /synchronize | Executes the synchronization script Alias: /sync Format: /synchronize |
| /backup | Allows providing the path for the destination backup to be saved to a specific location, or:
Alias: /bu |
|
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 Note: If the switch is omitted, 1,000 rows will be used as a default value |
| /destination_ to_source |
Change the synchronization direction Alias: /dts |
| /prescript_ file_path |
Full path to the pre-processing script file path, along with the “.sql” extension. Alias: /prefp Format: /prescript_file_path:”PreScript.sql” |
| /postscript_ file_path |
Full path to the post-processing script file path, along with the “.sql” extension. Alias: /postfp Format: /postscript_file_path:”PostScript.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” |
| /actions_output | Full path and file name for the synchronization actions output along with the desired extension. Allowed extensions are: “.txt”, “.html”, “.pdf”, “.csv”, “.xlsx”, “.xml” Alias: /aco Format: /actions_output:”Actions.txt” |
| /warnings_output | Full path and file name for the synchronization warnings output along with the desired extension. Allowed extensions are: “.txt”, “.html”, “.pdf”, “.csv”, “.xlsx”, “.xml” Alias: /wao Format: /warnings_output: ”Warnings.txt” |
| /execution_results_ output |
Full path and file name for the execution results output along with the desired extension. Allowed extensions are: .txt, .docx, .pdf and .html Alias: /ero Format: /execution_results_output: ”ExecutionResults.txt” |
| /summary_output | Full path and file name for the synchronization summary output along with the “.log” extension Alias: /suo Format: /summary_output: ”SynchronizationSummary.log” |
| /create_rollback_ script_before_sync |
Create a rollback script before the synchronization Alias: /crbs |
| /rollback_script_ file_name |
Full path and file name for the rollback script, along with the “.sql” file extension Alias: /rsfn Format: /rollback_script_file_name: ”RollbackScript.sql” |
| /backup_script_ folder |
Create a destination script folder backup for script folder or source control project Alias: /bsf |
| /backup_script_ folder_path |
Full path to the folder for the script folder or source control project backup Alias: /bsfp Format: /backup_script_folder_path: ”BackupScriptFolder” |
Comparison switches
| /columns | Select columns of objects to compare. The format of the argument is:
ObjectType1:ObjectName1:columnName1:…:columnName1 Alias: /cols |
| /ignore_columns | Ignore columns of objects in the comparison process. The format of the argument is:
ObjectType1:ObjectName1:columnName1:…:columnNameN … Alias: /icols |
| /comparison_keys | Set comparison keys for groups of objects. The format of the argument is:
ObjectType1:ObjectName1:comparisonKey1 Alias: /cks |
| /data_comparison_ options |
Set data comparison options:
Alias: /dco |
| /schema_mapping | Full 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” |
| /comparison_summary_ output |
Full path and file name for the comparison summary output along with the “.log” extension Alias: /cso Format: /comparison_summary_output: ”ComparisonSummary.log” |
Synchronization switches
| /date_format | The 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:
Alias: /rts |
| /data_sync_options | Set data synchronization options:
Alias: /dso |
| /warnings_handling | Specifies warnings handling before the application execution:
Alias: /wh |
Reporting switches
| /data_html_report_ options |
Set primary data html report options:
Alias: /dhtmo |
| /csv_report_ options |
Set primary data csv report options:
Alias: /csvo |
| /excel_report_ options |
Set primary data excel report options:
Alias: /exco |
| /data_html_report_ options2 |
Set secondary data html report options:
Alias: /dhtmo2 |
| /csv_report_ options2 |
Set secondary data csv report options:
Alias: /csvo2 |
| /excel_report_ options2 |
Set secondary data excel report options:
Alias: /exco2 |
Package switches
| /package_ compression |
Reduces the size of the resulting executable by using compression Alias: /pkcm Format: /package_compression |
| /package_error | Sets the package error handling (“ask” if omitted):
Alias: /pke |
| /package_author | Places the author tag into the about box of the package. Alias: /pkaa Format: /package_author:Author_name |
| /package_company | Places the company tag into the about box of the package. Alias: /pkco Format: /package_company:Company_name |
| /package_legal | Places the legal tag into the about box of the package Alias: /pkal Format: /package_legal:Legal_notice |
| /package_comments | Places the comments tag into the about box of the package Alias: /pkac Format: /package_comments:Comments |
| /vs_ver | Sets package Visual Studio version (“VS2015” if omitted):
Alias: /vrv |
| /indicate_default_db | Sets the default database the generated package should be executed against. Alias: /ddb Format: /indicate_default_db: “Database_name” |
| /create_mfs | Creates 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_dat | Creates a .dat file which contains SQL code for creating new or updating the existing database. Alias: /cdf |
| /create_com | Creates a CLI (Command Line Interface) executable for package. Alias: /ccf |
| /run_exe_now | Runs the created executable installer immediately upon generating. Alias: /ren |
| /open_in_vs | Opens the generated C# solution in Visual Studio upon creation. Alias: /ovs |
Additional switches
| /return_error_ code_on_equal |
Forces return code 102 – no differences detected, if the compared data sources are equal, otherwise the application returns 0 return code – success. Alias: /rece |
Learn more about additional switches from article on this link.
Return codes
ApexSQL applications have two groups of return error codes:
-
Common return error codes – learn more about these from here
-
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:”SyncProject.axdd” /u2:UserChanges /p2:passiton /on:”SyncScript.sql”
Using an argument file, e.g. “argfile.xml”, to execute commands:
ApexSQLDataDiff.com /argfile:”ArgumentFile.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:”CompReport.html” /ot:html /bu:”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:”SyncScript.sql” /v /f
Compare data between a database and source control, define collation and compatibility level:
ApexSQLDataDiff.com /d1:SourceDatabase /sct2:teamfoundationserver /scs2:“https://admin.visualstudio.com/DefaultCollection” /scu2:DestSourceControlUser /scp2:dscupass /scr2:”$/ProjectName” /c2:SQL_Latin1_General_CP1_CI_AS /cl2:2017
Compare data between a source control repository and local script folder, define collation and compatibility level:
ApexSQLDataDiff.com /sct1:git /scr1:“https://Admin@bitbucket.org/Admin/gitcommit.git” /scu1:DestSourceControlUser /scp1:dscupass /scr1:”$/ProjectName” /c1:SQL_Latin1_General_CP1_CI_AS /cl1:2017 /sf2:”DestinationScriptFolder” /c2:SQL_Latin1_General_CP1_CI_AS /cl2:2017
