Compare Data and Database Schemas

I was thrilled to discover the functionality to compare database data and schemas is available in Microsoft Visual Studio.  It not only allows you to compare two databases, but it will update the target database or write an update script for you to allow you to update the target database.

Most of this functionality has existed in Visual Studio Team System Database Edition since 2005.  Team System Database edition is used by database developers and administrators and includes advanced tools for database change management and testing.  Database schema comparison is now available in Visual Studio Premium and Visual Studio Ultimate since 2010.  Unfortunately it’s not in Professional or Express.

Having the database schema compare functionality in Visual Studio is awesome for developers who straddle several worlds. It’s nice to be able to build an application that affects or relies on a database, and also be able to compare the database schemas among environments, even if building the database doesn’t fall within your purview, all within one tool. 

The comparison is really easy to do. Look in Tools->SQL Server

SNAGHTML12d0a324

Select your source and target database

SNAGHTML12d22eb6

Click Compare and it will compare the databases.  Here I have compared the schemas of two databases.

SNAGHTML12d43ca6

The tool highlights the differences. In this case a new field called [newfield] exists in the table [Threads] in the source database but not the destination database.

Click the script icon to generate a SQL script for the changes to the destination database.

SNAGHTML1339c331

Or click the Update button to actually do the update of the destination database. 

How sweet is that? I know I’ll be using this regularly in all kinds of development.