How To Compare Two Databases, Find Differences And Update Using Visual Studio

Introduction

In this article, 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 between the two databases.

• Update or generate script to update the target database. 

In the last article, we learned how to create and published 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) to the Project.

• Publish to create a new database in the SQL server.

Please find the article: How To Create SQL Server Database Project With Visual Studio (c-sharpcorner.com)

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

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 require 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 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 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 and 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. Afterward, 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 the source database and updates 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 updated Scripts

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

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 projects in visual studio which are completely free and handy tools. 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.