SQL Server Schema and Data Compare in Visual Studio

Did you know that Microsoft Visual Studio 2013 Premium SQL Data Tools provides functionality that allows you to compare the schema and data of two SQL Server databases? Well, I didn’t either.

It’s simple. Here’s how:

Go to the SQL Server Object Explorer and add your database SQL Servers, by right clicking on SQL Server and choosing Add SQL Server.

Then you right-click on the SOURCE database and choose either Schema Compare option if you want to compare the schema differences between two databases or Data Comparison option if you want to compare the data differences between two databases. In this example, I will choose data compare to show you some of its handy features. This is a nice way to refresh the stale data in your development database with the fresh data from your production database. *** I CANNOT STRESS ENOUGH THAT YOU NEED TO BE FULLY AWARE WHICH DATABASE YOU ARE WRITING TO (TARGET) TO ENSURE THAT YOU DO NOT ACCIDENTALLY OVERWRITE DATA IN YOUR PRODUCTION DATABASE.*** I would never use this tool and use the production database at the TARGET, even if I am filtering what gets updated. Any changes to the production environment, I handle with full knowledge and intention… and a complete backup if necessary!!! It is just safer that way. Just say no. I could go on… but I think you get the point. J
Choose the TARGET database. This is going to be your development database. If you do not have a defined connection to the target database, you would click the New Connection button to create one. Click Next.

If you are dealing with a large size database, and you are interested in comparing only a subset of items, you can actually select the Tables and Views you want to compare. This is nice if you are debugging an issue in development and need only a few tables updated with data from production. The number of tables and views you compare add to the length of time it takes to compare and update the records, and could impact database performance for both development and production while the comparison and updates are running. So I would never select an entire database data compare and update for any sizeable database during standard business hours.

Once the comparison has completed, you can select a table in order to see the differences in its rows.
Again, before you perform any updates, it is always a good idea to back up your databases. Also, look at the top to make sure that the Source database and the Target database are setup correctly. The TARGET database is the one that gets updated. Once you have confirmed these things and are ready to proceed with the update, simply click the Update Target button.

Pretty handy!