Compare Two Databases, Find Differences and Update using Visual Studio

Introduction

In this tip, we are going to learn how to compare two databases using SQL Server Database Project (Template) available in Visual Studio. I will cover the following points:

• Introduction of SQL Server Database Project
• Connect two databases: source and target
• Compare and identify differences among the two databases
• Update or generate script to update the target database

In the last article, How To Create SQL Server Database Project With Visual Studio, we learned how to create and publish the SQL server database project.

• Create New SQL Server Database Project.
• Import database schema from an existing database, a SQL script file, or a Data-tier application (.bacpac) the Project.
• Publish to create a new database in SQL server.

The database plays the most important role in any application and it becomes difficult to manage the project when the number of tables, views, stored procedures increases. 

Background

Consider scenarios where multiple developers are working on a project for the next release, some are working on bugs or adding new features which again requires some or many changes in the database. Most of the time, developers take note of DB (Database) changes manually. Sometimes, they miss some changes which cost in production.

There are many cases that Dev, UAT, and production DB are different which is again a hassle to identify the differences. 

There are a number of tools available in the market for comparing DBs, but they are costly or paid solutions. 

So, in this article, we will discuss and learn about SQL Server Database Project with is available in Visual Studio which is free. Yes — Free!! 

Prerequisites 

Visual Studio (2013, 2015, 2017, or 2019) and MS SQL Server. I am using VS2019 and SQL Server 2017 Developer for illustration.

 You can create a new database project and import a database schema from an existing database, a .sql script file, or a Data-tier application (.bacpac). You can then invoke the same visual designer tools (Transact-SQL Editor, Table Designer) available for connected database development to make changes to the offline database project and publish the changes back to the production database. The changes can also be saved as a script to be published later. Using the Project Properties pane, you can change the target platform to different versions of SQL Server (including SQL Azure) (copied MS Docs).

Compare Two Database and Find Differences

In this section, we will cover how to compare two databases to identify the differences like Dev, UAT or UAT, Prod likewise.

First, open the SQL Server Database project with Visual Studio, right-click on it, and choose compare schema as depicted.

Then, we will select the source and target databases and provide a connection to those.

We can prove a connection to the local database, Network database, or Azure database either in source or destination. In other words, we can compare the two databases of different sources, i.e., Development to Production, Development to UAT, development to Production, or any two SQL server databases. Another important point, we can compare any destination database with the project included database as well.

Then next, we will click on compare as shown:

Normally, it takes some time based on network speed or database specifications. Afterwards, we will see the result of a comparison, as shown below:

You will notice three different types of changes: Delete, Edit and Add.

Delete: Delete objects are those which are deleted in source database and update will delete from the destination as well. Objects: Tables, views, stored procedures likewise.

Edit: Edit objects are a list of objects in which there are some changes in the source and updates will modify the objects as per the source.

Add: Add objects are newly added to the source database. However, there is a check box for each of the objects in the comparison list through which we can uncheck any of those if we want to skip the update in the destination database.

Update or Create update Scripts: After a successful comparison, we will get some more options. Besides a compare icon, there are few more options to update or generate update scripts as shown below:

Compare, Update or Create Update Scripts

Direct Update option: This option will update the destination or target database directly using the connection string provided during the schema comparison. Therefore, we need to ensure that proper permissions are assigned to connection credentials.

Generate Script: Again, this option will provide an SQL script for applying the changes to the destination or target database. From the above consideration, we don’t need to track database changes or create migration scripts manually between the databases which is a time-consuming task and error-prone. We can utilize the free tools provided in Visual Studio.

Conclusion

In the article, we have learned how to compare two databases and identify the differences between those using SQL server database project in Visual Studio which is completely free and a handy tool. Additionally, we have generated an update script and can update directly in destination based on continence. Furthermore, we can modify the update based on requirements as shown in the above article.

History

• 5^th April, 2021: Initial version