OpenDBDiff – OS tool for SQL Server Schema Comparisons

I can’t think of the last project I was involved in where I didn’t have to do schema comparisons between differing databases to generate diff scripts.  Sometimes I have a local copy of the DB on my laptop that I need to synch with a dev database.  Other times I have to create the scripts that we will use to deploy schema changes between dev and staging.  Sometimes I just want to see if there are any differences between my local DB and another to see why some tests aren’t passing.  Regardless, I find myself consistently managing this task..

There are a variety of tools available to help with this task: RedGate and Data Dude to name a couple.  Recently a couple of colleagues put me on to OpenDBDiff.  I say a couple of colleagues because the lot I work with tell me about “helpful” OS tools and projects almost daily.  When I hear 2 or more folks use the same tool, my antennas go up.

OpenDbDiff is an open source database schema comparison tool for SQL Server 2005 / 2008.  In my opinion, the beauty of OpenDBDiff is the simplicity.  Without looking at ANY documentation, you can do a full schema comparison between 2 databases, choose the object types you want to script and generate schema comparisons.  Let’s have a look:

Getting Started

Here are the simple steps to get started with OpenDBDiff:

1. Download OpenDBDiffl  Simply go to the codeplex site and click download.  You will download a zip file. 
2. Unzip the contents into a folder in your Tools directory (or wherever you keep your tools). 
3. Run DbDiff.exe.  It is that easy…

The Demo

I have both SQL Server and SQL Server Express running on my box and this will do for our simple demo.  On my SQL Server instance, I have a database called HelpDeskDB that I used in my Entity Framework Training Series.  The first step is for me to is to create an empty HelpDeskDB on my express instance and spin up DBDiff.  I’ll use it to copy the schema to my new database.  Here is a screenshot:

The Options gives you typical SQL Compare options.  Here is a view of the filter options:

Once you press “Compare”, you can see a summary of the differences in the left hand list box:

Or you can have a look at the sync script:

In my case, I am going to copy the script to the clipboard and run it on my target database to sync them. 

Syncing Changes

With that done, I now have 2 databases with the same schema.  Next, I want to show you an example of syncing changes between 2 databases.  The first thing I will do is make a schema change to my new database.  I am going to add a non-nullable** column to my Contacts table.  Here is the change:

Now, I will reverse the databases in OpenDBDiff, setting my “new” database (with the updated schema) on SqlExpress as the source and my original database as the destination and press “Compare”.  Here is the SQL Script that was generated (omitting the dropping and creating constraints for brevity sake):

As you can see, the SQL generated does the following:

• (not shown here) Drop the Constraints
• Creates a temp table with the new, non-nullable column
• Inserts the data from the original table into our new table (it is even smart enough to default the datetime with GetDate())
• Drop the original table
• Rename the new table
• (not shown here) Create the Constraints

Conclusion

As you can likely see, OpenDBDiff is a handy tool for your toolbox.  It is open source, easy to use and does a great job at  schema comparisons.

**Note: you might not want to introduce a new column as non-nullable in one release.  This is a topic for a future post.