SQL SERVER – SQL Server Data Compare Tool

This article shows how to use ApexSQL Data Diff, a SQL data compare tool. You can download it here, install, and play along. Let us learn about SQL Server Data Compare Tool.

At the beginning, let me say something about this database software.

ApexSQL Data Diff is a SQL Server development tool that is used to compare and analyze data stored in tables, or views, between SQL databases or their backups, and for simplifying data migrations between databases. It can present a detailed view of differences between objects and encrypted objects, and also a row level difference for each object.

When SQL Server data comparison is complete, you can automatically synchronize specified objects between databases, or select a specific row for synchronization. You can isolate the differences that are important with advanced filtering of objects and rows.

The new ApexSQL Data Diff 2014 comes along with SQL Server 2014 support and the new UI style. In the ApexSQL Data Diff Community edition (free), the user can compare and synchronize data in SQL Server Express and Windows Azure SQL databases.

Right after the program is started, a Project management dialog shows up. You can use it to create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

The first option in the Project dialog window is for selecting a source and a destination data source type. You can select two data types from a list: Backup and Database. Also, this dialog offers an option to revert a source and a destination, and to clone information between them.

If you select Database as a data source type, the details you need to provide are login information, server name, and select a database from the drop down list of all available databases on the specified server. If you select Backup as a data source type, you need to provide a path to a backup file.

Beside the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Object filter, Object mapping, and Additional scripts.

In the Object filter tab, there are various options to filter objects and views.

The first option is to edit the Comparison key, which uniquely identifies each single row in a table, or a view, for both the source and destination data sources. Click on the arrow in the Comparison key cell of the selected object, the drop-down menu will open, and you can select the Edit custom comparison key option.

The new window will appear with a list of the selectable comparison keys. By clicking on the check box on the left side of the Column name, you can check/uncheck all of the keys, or to select only the keys which are significant for SQL Server data compare.

This option can help you in a situation when the comparison process shows incomparable objects, because there are no unique indexes or primary keys, and when additional information is required.

You can also select which columns will be included for comparison process. With a click on the arrow in the Columns cell, for the selected object, a drop-down menu will appear.

There, you can select which columns you want to include in the comparison process. In this example, only one column is unchecked, and this will automatically change the number in the Columns cell by reducing the number to one.

You can use the Row filter option to enter the conditions for SQL Server data compare to filter the rows that meet given condition. In the following example, we’ll select the row with the object SalesTerritory, then select the Row filter cell, and click on the three dots button. This will open the Row filter dialog for SallesTerritory, in which you can enter any T-SQL condition.

Click on the Refresh row count button, at the bottom of this dialog, to get the Row count, which is the number of the rows thatmatch previously mentionedconditions.

The last option in object filter is the Filtereditor for the listed objects. By clicking on the Edit filter button, in the top right corner of the object list, a new dialog opens, and you can select custom filter criteria for the tables, or the views. You can include/exclude some of the objects for comparison. With the Edit filter option, you have the ability to specify custom conditions to exclude the irrelevant tables from SQL Server data compare.

Object mapping is used for a custom mapping of tables and indexed views with different names and schemas. Tables and indexed views with the same names and schemas are automatically mapped. You can perform custom table/column mapping. There are two cases for performing the custom table/column mapping:

1) To exclude automatically mapped tables/columns from the comparison

2) To include differently named SQL Server tables/columns with the same schema names

Finally, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use script at all, to use an embedded script, or to use a saved script.

At the end, you can save your selections to a project and use it again. To run this saved project, select it from the Project management dialog and it will load all previously saved settings.

When all options are set, you can click the Compare button. After comparing is done, compared objects are shown in the Main grid. There are several columns in the Main grid: Schema, Object type, Total rows, and difference information which contains: Missing, Identical, Different, and Additional records. Any of those columns can be used for sorting the results, just by clicking on them.

All compared objects are shown, along with basic information and a number of differences between compared objects. Furthermore, the row level differences are shown in the difference details pane, from which you can choose specific rows for the synchronization.

To display certain objects in the Main grid you can use the inline Filter row option. To enable it, go to the View tab, and select the Filter row option. This option is used for filtering compared objects based on different information, object name, total rows, or schema, by entering appropriate condition.

Working with the results grid can be easier by using some of the options, which are located in the View tab, under the Grid group.

There are buttons for grouping the comparison results. The results can be grouped by an object type (tables, views, procedures, etc.). The Collapse group and the Expand group options allow you to automatically collapse, or expand, all selected groups in the Main grid. The Multi column sort option provides more sorting options and you may select up to four columns by which the results are going to be sorted. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

The Difference details pane provides viewing and selecting row level differences for synchronization. It’s located beneath the Main grid. If you want to choose some specific rows for synchronization, you have to select the checkbox for those rows.

One of the options that difference details pane provides for users is to view only the rows with specific difference type, by selecting the tabs at the bottom of difference details pane. Those different types can be: Missing types – when records are in the source, but not in the destination database; Identical types – when records are in both the source and the destination database; Different types – when records are in both the source and the destination database, but are not identical; Additional types – when records are not in the source, but are in the destination database.

You can select desired objects, from the Main grid, or you can select all objects by going to the Home tab, and click the Check all button.

After all desired objects are selected, the synchronization process can be started with a click on the Synchronize button, from the Home tab. The Synchronization wizard will appear with the first step, where the source and destination data source are shown, along with the synchronization direction, which can also be reversed.

In the next step, you will be prompted to select the output options. You may choose to Create a synchronization script, or Synchronize a database.

One of the options is to save the synchronization script. The other option is to open the script in an editor and you may choose to open it with the Internal editor, or to assign the other one.

The final step is the Summary and warnings window, where you can see all actions that are going to be performed, along with the possible problems that might occur during the synchronization. You may group the actions by the execution order, by an object, or by the action type.

Finally, select the Create script button, and after you reviewed it, you may execute it.

In ApexSQL Data Diff, you can create reports based on obtained differences, by selecting the Export option from the Home tab. You can export results in three ways: as an HTML plain report, an HTML report, and a CSV data export.

An HTML plain report, which has a simple structure, without user interaction, except when SQL scripts are included in the report. In this report you can see which data comparison options were checked/unchecked, which report options are selected, and data comparison results with all needed information. An HTML plain report is preferred for printing.

An HTML report is similar to an HTML plain report, by its look and presented information, but it also has expand/collapse region with data comparison results. In this way you can easily expand, or collapse, information that you want to see. This report is preferred for browsing.

The last of them is CSV data export, which can generate results into an XML file, for further detailed analysis. An XML file allows using the same document for different systems and platforms, where results can be further analyzed/used in different programs to inspect differences or creating some new report for objects.

Reference: Pinal Dave (https://blog.sqlauthority.com)