Database Schema Compare & Upgrade

I spent a few days playing with Ruby on Rails a while back.  During the learning experience, there was one particular feature that I really liked.  It was the database migration scripts that get automatically generated for you.  I always wished I had something like this in the windows (asp.net) world.  It turns out there is something out there and it is right there within Visual Studio.

When upgrading my production applications, I have always struggled with trying to update my production database schema to match the latest schema.  This has always been a manual, error-prone and time consuming task.  One that I always dreaded and postponed to the last minute.  It usually involved crossing my figures, praying to the SQL Gods and running a hand-made migration script against the production database.  I am not a DB guy, so you can imagine how much un-fun this was.

Long story short, you can do this with a few clicks in Visual Studio 2008. 

Start a new schema comparison

clip_image001

Select your source database (e.g. development database) and your target database (e.g. production database)

clip_image001[4]

Click Ok.  Visual studio will compare the two schemas and display the results in a grid, showing you what objects (tables, views, procs, etc…) have changed and the action you want to take.

clip_image001[6]

Select any item that has changed and you will see the differences between source and target.

clip_image001[8]

The last pane at the bottom contains the update (migration) script that will run against the target to make it identical to the source.  You can quickly scan it to make sure you are not wiping out your entire production database (not recommended).

clip_image001[12]

You can also customize the update by clicking the drop downs in the grid to customize the script

clip_image001[14]

Once everything looks good to go, just hit the button "Write Updates" and you are done.

clip_image001[16]

This has been a sore in my side for a long time and I am glad I discovered this.  I am actually kind of pissed off because I have always seen that menu and never really tried to click it.  Oh well!!!

Hmm…  What to do with all the time I just freed up???

NOTE: According to msdn this feature is only available in the Database Edition and Team Suite versions.  I am running Team Suite (click Help > About Microsoft Visual Studio to find out your version)

image