How to Compare Two SQL Server Databases Using SQL Server Data Tools

1. Overview

The new-edge tools have taken over the IT space like a storm. Many tools bring revolutionary changes and one of them is SQL. SQL Server data tools include a Schema Compare utility which is used to develop and compare different databases. For a software development company, there are many scenarios where an SQL programmer or database administrator needs to compare two databases. There are many tools available for the comparison of schema & data of the production database.

But, if we have SQL Server Data Tools (SSDT), we don’t need any other additional tool. It is like a one-stop-shop to compare and synchronize databases. So, in this blog, we will learn everything about SQL server data tools from installation usage to comparison and other essential aspects. Let’s begin with the installation process.

2. Installation

SSDT can be installed as part of Visual Studio Installation. Visual Studio Community Edition also supports this and facilitates developers who are working on database development.

3. Compare Schemas between Two SQL Databases

Following are steps of the comparison process of two SQL databases:

1. From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Schema Comparison. It will open a screen for Schema Comparison.

2. Click on “Select source”. After clicking on this, You will see a pop-up window. This window will have three options to choose from.
   • SQL Server Database Project can be selected. This project file (.sqlproj) should be there in the current solution.
   • Databases from Microsoft SQL Server can be selected.
   • Data-tier application file (.dacpac) can be selected.
3. Select similarly for target schema.
4. Click on compare to compare SQL databases. It will show a comparison result. Here, it will show all types of database objects (Table, View, Stored Procedure, Function, etc.) which are not the same in both databases. It will show results by Group of Action:
   • Delete: This displays the objects which are already available in the target database not unavailable in the source database. These objects will be deleted from the target schema in the update process.
   • Change: Objects are available in both databases, but the schema is different in both databases. These objects will be modified in the target schema during the update process.
   • Add: In this process Objects which are available in the server database, but not in the target database. These objects will be created in the target schema during the update process.

We can check/uncheck the objects, those changes will be applied to the Target database.

Next click on “Update” to apply changes to the target database.

Or User can select an option to generate a script.

Notes

• Dependencies must be maintained in Object selection. However, SSDT automatically selects relevant objects.
  For Example
  In AdventureWorks Database, EmployeeDepartmentHistory depends on Department, Employee and Shift tables.
  Now consider that this table exists in the source databaseEnable Script option if the target database schema, but not in the target schema.
  1. If you have selected the EmployeeDepartmentHistory table only, those 3 tables will be automatically selected. But, checkboxes of these 3 tables will be disabled.
  2. If all four tables are included in the selection and if you exclude the Employee table, it will disable the checkbox for the Employee table. It means The Employee table is still included. However, once we exclude EmployeeDepartmentHistory, it will enable the checkbox again for the Employee table.

Similar validation will be done for Change or Delete Action objects also.

Disabled checkbox might be shown with indeterminate or checked status based on Action.

• Enable Script option if the target database name is available. Not available for SQL Server Database Project or Dacpac file.
• By default, equal objects are not shown in results, but it can be visible by clicking “Show Equal Objects” from the toolbar.
• Objects are default grouped by Action (Delete/Change/Add). It can be changed to Schema or Type (Table/View/Function etc.) from other options in the toolbar.
• Comparison can be saved as a SchemaCompare file (.scmp). Excluded objects will remain excluded while reopening the file.

4. SQL Data Compare between Two Databases

Following are steps to compare data of two databases:

• From Visual Studio, Open Tools 🡪 SQL Server 🡪 New Data Comparison. It will open a new wizard screen for Data Comparison.

• Select the Source Database and Target database.
• Select Data Compare Options (Note- With Data Compare for SQL Server tool, you can compare and synchronize data in SQL Server databases. You can analyze your SQL Server databases using SQL Compare and ensure that schema synchronization is error-free using generated SQL scripts): Which type of records should be compared:
  1. Different Records
  2. Only in Source
  3. Only in Target
  4. Identical Records
• By clicking the next button, it will list Tables & Views in the next Screen, which are there in both the tables within the same schema. Here, you can select/unselect tables & views, which will be compared. Also, if you wish to exclude some fields of tables, those fields can be omitted and this will not be compared.Click on Finish to start the comparison.If we choose the Finish option from the first screen from the wizard, it will consider all selections and start the comparison.

• It will show the result for all tables/views.

  After the Table name is entered in the first column, the next columns will show Row count for Different Records, Only in Source, Only in Target, Identical Records.Once we select one table from the list, it will show compared data in different tabs of the bottom pane.

  First, we need to select the table name from the above list, and then need to select required rows from the bottom pane.

  By default, all tables will be selected which have unique records or at least one record in the database. All the records except identical records (which cannot be selected) will be selected by default.

  Different records, the primary key column(s) will be displayed a single time, then it will show all columns one by one for source and target database.

  For Example

  In the given Image, For Lineage table, Lineage Key is the primary key, which is displayed only once, then Table Name (Source), Table Name (Target), Data Load Completed (Source), Data Load Completed (Target) so on.

  If data is the same, it is shown in Grey Color, while different data is displayed with Black Bold font.

• To complete the whole creation of the database click on Finish selection, then click “Generate Script” or “Update Target” to complete the process.

Note: Both the processes mentioned above have default options. Some options can be changed while others can be compared using a comparison file or from Tools 🡪 Options 🡪 SQL Server Tools in Visual Studio. Also, features may vary in different versions/editions of Visual Studio.

5. Conclusion

Through this blog, we hope, we were able to elucidate users on Microsoft SQL Server Data tools and their comprehensive usage. This step-by-step guide will help you compare the schema, strings & data of two different databases. It is very helpful for SQL developers & database administrators for managing databases in different situations.