Inventory of SQL schemas and data compare output files in ApexSQL tools

Applies to
ApexSQL Data Diff, ApexSQL Data Diff for MySQL, ApexSQL Diff and ApexSQL Diff for MySQL

Summary
This article explains the inventory of the ApexSQL Data Diff, ApexSQL Diff and ApexSQL Diff for MySQL output files, their names, types, and descriptions.

Description

All four tools, ApexSQL Data Diff, ApexSQL Data Diff for MySQL, ApexSQL Diff and ApexSQL Diff for MySQL offer creating variety of the output files in different stages of the comparison and synchronization processes.

Quick tip: Creating output files isn’t mandatory, except for the synchronization script when the Synchronize now action is used. In this case, the synchronization script will be created as a temporary file, as it’s needed for executing synchronization, but it will be automatically deleted when the new project is started

All of the below-mentioned output files have default file name templates that can be changed in the Options window, under the Output tab:

Defaults

Note that all of the following examples shown below show the default aka factor settings for file names. These names can be customized by changing the file name template, as shown above, to be anything that you want them to be for example, you can add the source and destination server and database names to identify the comparison pair in each file name. You can also change the name of the file, for example from “DataDifference” to “Changed_data”. Date and time tags are picking up Windows settings format.

Comparison outputs

Once data sources are compared, the following outputs can be created to export comparison results:

• The following three output files are reports and their file name template consists of the file name qualifier “Data/SchemaDifference”, followed by the “date/time” stamp:

  • HTML report – an interactive HTML comparison report, that allows better navigation through report sections. The file extension is .html, as this is an HTML file:

    DataDifference_20170920_01-31-37.html (for ApexSQL Data Diff)
    SchemaDifference_20170920_01-32-16.html (for ApexSQL Diff)

  • HTML simple report – as its name states, this is a simple version of the above-mentioned HTML report, created for printing purposes. The file extension is .html, as this is also an HTML file:

    DataDifference_20170920_01-43-29.html (for ApexSQL Data Diff)
    SchemaDifference_20170920_01-47-24.html (for ApexSQL Diff)

  • Excel report – contains all information as HTML simple report, along with the data/schema differences. The file extension is .xls, as this is an Excel file:

    DataDifference_20170920_01-48-42.xls (for ApexSQL Data Diff)
    SchemaDifference_20170920_01-49-37.xls (for ApexSQL Diff)

• CSV export – allows exporting only data differences of the compared data sources in ApexSQL Data Diff and ApexSQL Data Diff for MySQL into individual files, for each object. The file name template consists of the schema name and object name, followed by the difference type qualifier “Missing/Different/Additional/Equal”. The file extension is .csv, as this is an CSV file:

  Person.Address_Missing.csv

• XML schema difference – allows exporting only schema differences of the compared data source in ApexSQL Diff and ApexSQL Diff for MySQL. The file name template consists of the file name qualifier “SchemaDifference”, followed by the “date/time” stamp. The file extension is .xml, as this is also an XML file:

  SchemaDifference_20170920_01-51-33.xls

• Comparison summary – It represents a simple data difference comparison summary, that contains information of the number of missing/different/additional/equal rows in compared objects along with their names.

  This output file is intended only for the CLI and it basically saves the part of the verbose application output, once the comparison process is finished. The file extension is .log, as this is a log file and there is no file name template, as its name needs to be specified along with the path in the /cso switch.

Pre-synchronization outputs

Once the Synchronization wizard is initiated, in the last step – Summary and warnings, two outputs can be created:

• Synchronization actions – allows exporting of actions that will be taken during the execution of the synchronization script. The file name template consists of the file name qualifier “Actions” followed by the action grouping qualifier “by order/object/type” and “date/time” stamp. Allowed extensions are .txt, .html, .pdf, .csv, .xls and .xml:

  Actions_by_order_20170920_01-53-28.txt

• Synchronization warnings – allows exporting of warnings that are detected before the synchronization starts. The file name template consists of the file name qualifier “Warnings” and “date/time” stamp. Allowed extensions are: .txt, .html, .pdf, .csv, .xls and .xml:

  Warnings_20170920_01-58-19.pdf

Post-synchronization outputs

Upon finishing all steps of the Synchronization wizard, the following outputs can be created:

• Synchronization script – its purpose is to synchronize the destination database that was previously compared. The file name template consists of the file name qualifier “Synchronize” and “date/time” stamp. The file extension is .sql, as this is a SQL file:

  Synchronize_20170920_02-01-37.sql

• Rollback script – allows creating of a SQL script that can rollback any data/schema changes made upon the synchronization process is finished. The file name template consists of the file name qualifier “Rollback” and “date/time” stamp. The file extension is .sql, as this is a SQL file:

  Rollback_20170920_02-03-58.sql

• Snapshot file – allows creating of a snapshot file as a schema backup for a database/script folder/source control project, when these are used as a destination data source. The file name template consists of the file name qualifier “Snapshot” and “date/time” stamp. The file extension is .axsnp, as this is an ApexSQL snapshot file:

  Snapshot_20170920_02-06-29.axsnp

• Script folder backup – allows creating a script folder backup that will contain all object scripts and static data, when the script folder or source control project is used as a destination. The file name template consists of the script folder name or source control project name, followed by the folder name qualifier “backup” and “date/time” stamp:

  StageSF_backup_20170920_02-10-07

• Executable installer – represents executable installation package wrapped up in GUI that allows execution of the synchronization script on any SQL Server database and doesn’t require any additional software. The file name template consists of the destination server name and destination database name. The file extension is .exe, as this is an executable file:

  MyServer.MyDatabase.exe

• C# solution – represents the source code of the executable installer that can be used for editing and building in Visual Studio. The filename template consists of the destination server name and destination database name. The file extension is .sln, as this a Visual Studio solution file:

  MyServer.MyDatabase.sln

  • Note: Rollback script, Snapshot file, Script folder backup, Executable installer and C# solution are note applicable for ApexSQL Data Diff for MySQL and ApexSQL Diff for MySQL

• Execution results – allow exporting of the information from the Results window upon the finished synchronization process. Allowed extensions are: .txt, .doc, .pdf and .html:

  Execution_results_20170920_02-06-27.doc

• Synchronization summary – it represents a simple data synchronization summary, that contains information of the number of rows that are synchronized to a destination database. This output file is intended only for the CLI and it basically saves the part of the verbose application output, once the synchronization process is finished. The file extension is .log, as this is a log file and there is no file name template, as its name needs to be specified along with the path in the /suo switch