SQL Server Data Compare Tool is a powerful tool that allows developers and database administrators to compare and analyze data stored in tables or views between SQL databases or their backups. It simplifies the process of data migration between databases and provides a detailed view of differences between objects and encrypted objects.

One popular SQL Server Data Compare Tool is ApexSQL Data Diff. This tool supports SQL Server 2014 and comes with a new user interface style. The Community edition of ApexSQL Data Diff is free and allows users to compare and synchronize data in SQL Server Express and Windows Azure SQL databases.

When using ApexSQL Data Diff, you start by creating a new project or opening a previously saved one. You can select the source and destination data source type, which can be either a backup or a database. If you choose a database as the data source type, you need to provide login information, server name, and select a database from the drop-down list of available databases on the specified server. If you choose a backup as the data source type, you need to provide the path to the backup file.

ApexSQL Data Diff provides advanced filtering options for objects and rows. You can edit the comparison key, which uniquely identifies each row in a table or view, for both the source and destination data sources. This is useful when there are no unique indexes or primary keys, and additional information is required for the comparison process. You can also select which columns will be included in the comparison process.

The tool also allows you to filter rows based on specific conditions using the row filter option. This can be helpful when you only want to compare rows that meet certain criteria. Additionally, you can use the object filter option to include or exclude specific objects from the comparison process.

Object mapping is another useful feature of ApexSQL Data Diff. It allows you to perform 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, but you can perform custom table/column mapping to exclude or include differently named SQL Server tables/columns with the same schema names.

ApexSQL Data Diff also provides the option to use additional scripts before and/or after the synchronization script is run. This allows for more flexibility in the synchronization process.

Once all the options are set, you can click the Compare button to start the comparison process. The compared objects are shown in the main grid, along with basic information and the number of differences between compared objects. You can sort the results based on different columns in the main grid.

The difference details pane provides a view of row-level differences for synchronization. You can select specific rows for synchronization by checking the checkbox for those rows. The difference details pane also allows you to view rows with specific difference types, such as missing, identical, different, and additional records.

After selecting the desired objects, you can start the synchronization process by clicking the Synchronize button. The synchronization wizard will guide you through the process, allowing you to select the output options and save the synchronization script if needed. The summary and warnings window provides an overview of all actions that will be performed during the synchronization process.

ApexSQL Data Diff also allows you to create reports based on the obtained differences. You can export the results as an HTML plain report, an HTML report, or a CSV data export. The HTML plain report is preferred for printing, while the HTML report allows for easy browsing of the comparison results. The CSV data export generates results into an XML file for further detailed analysis.

In conclusion, SQL Server Data Compare Tool, such as ApexSQL Data Diff, is a valuable tool for comparing and analyzing data stored in SQL databases. It simplifies the data migration process and provides a detailed view of differences between objects. With its advanced filtering options and customizable mapping features, it offers flexibility and efficiency in managing SQL Server databases.