How to compare database schema and data?

When you are working in development environment, many times you might come across the issue of syncing the development DB changes with QA/Production DB. For this, first you need to compare the DB schema and data between these environments and update the changes to target database.

Here I am going to show you the simplest process on how you can do this using the Visual Studio database comparison tool. This tool compares the source database with target database, and highlight the changes made in source database. This tool also let you update the target database with the changes found in source database.

Note: The DB comparison tool is possibly limited to Visual Studio Enterprise, Ultimate and Premium versions.

Schema Compare Tool:

First go to the SQL Server and create two sample databases called, DevelopmentDB and ProductionDB. I have created one table (Users) in each database as shown below:

Here are the new changes in DevelopmentDB:

• Roles table: added a new table
• Users table: FirstName, LastName columns length changed from 50 to 150, LastName column changed from null to not null and added new column called Address

Now go to Visual Studio and launch the ‘Schema Comparison’ tool as shown below:

Now Visual Studio launches the ‘Schema comparison’ tool. Select the Source and Target databases from it and click on ‘Compare’ button.

As soon you click on ‘Compare’ button, Visual Studio does the schema compare between source and target database. All the changes found in source database would be highlighted in ‘Object Definitions’ window.  

Now click on the ‘Update’ button to reflect the changed found in source database to target database.

In the above screenshot you will notice that all the changes mentioned in DevelopmentDB are highlighted in schema comparison tool.

Once you click on the ‘Update’ button, all the highlighted changes will update to target database.

Now go to the database and have a look at the ProductionDB for the changes reflected.

Data Comparison tool:

Let’s assume our development and production database table records are as below.

You can notice that, production database Users table is missing second record and phone number is different from the Users table of development database.

Now we’ll look into the data in both DevelopmentDB and ProductionDB, and will update the production database with the new/updated records found in development database.

For this, go to Visual Studio and launch the ‘Data Comparison’ tool as shown below:

This will launch the ‘New Data Comparison’ dialog, where you need to select source database and target database. This dialog also let you choose data compare options which used by data comparison tool to get the compare results:

• Different Records – shows all the records which are updated in source database compare to target database.
• Only in Source – shows all the records which are added in source database and not available in target database.
• Only in Target – shows the records which are only available in target database and not found in source database
• Identical Records – shows the records which are having similar records in both source and target databases.

Once you have choosen the databases, click on the ‘Finish’ button.

The data comparison tool will shows the changes between these two databases as shown below:

Click on the ‘Update Target’ button to reflect the changes found in source database to target database.

Now go to that database and you will notice that the target database is updated with the changes found in source.

That's it, your target database is completely updated with all the changes found in source database.

Hope this helps to you. Have any questions? Let’s use the below comment box to discuss!

Thank you for reading J