In this tutorial, we will learn how to compare two SQL Server Databases, and find out the differences between the two SQL Server Databases.
Let’s assume we have our Students database in UAT and the Production environment, and we suspect that there is some inconsistency between these two database versions. To debug this issue we need to compare both our versions of the databases.
I have created a sample Student-UAT database assuming it is the UAT version and a Student-Production database assuming it is the Production version.
Both these databases have a StudentsInformation table, but a column is missing (FatherMobileNo) in the production version of this database, and a stored procedure (SP_DeleteStudent) is also missing in the production version.
To perform this comparison we will use Visual Studio. So let’s get started.
Step 1 – Create a new SQL Server Database Project in Visual Studio
Step 2 – Right click on the project name in the Solution Explorer and select the “Schema Compare” option.
Now, we need to specify our source and target schema, i.e., our UAT DB and Production DB.
Step 3 – Make a connection to both the UAT and Production database.
Step 4 – Click on the options icon and select the object which you want to compare.
In the “Object Types” tab, select the object which you want to compare. For this example, I am selecting only “Tables” and “Stored Procedures”
Click OK.
Step 5 – Compare the Databases.
To compare the databases, click the “Compare” button on the top left side of the window.
After clicking on the “Compare” button, below is the result.
As you can see, it is displaying us the irregularities between the two databases i.e. the missing “FatherMobileNo” column and a missing “SP_DeleteStudents” Stored Procedure, it also provides us the scripts for the missing objects.
Hope this tutorial has helped us in learning how to compare two versions of the database.
Don’t forget to check out this awesome Power BI course on Udemy.
Also, do check out this article: Exception Handling and Rollback Transaction in SQL Server Stored Procedure
Thank You
