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

Advertisements

0 thoughts on “Database Schema Compare & Upgrade

  1. The Database comparison tools in DB Pro really aren’t migrations. If you’re looking for that, check out my post comparing some .NET Migrations here:.NET Database Migration Tool RoundupThe Schema Comparison tool cannot help you when you are deploying a volatile change, such as adding a new NOT NULL column to a table that has existing data. You have to customize the script. It doesn’t handle refactorings where you change the datatype of a column. Again you have to futz with the script.Migrations capture the evolution of the database and applies these changes safely and in order.Also I’d like to plug RedGate’s SQL Compare, which is far better than DB Pro’s Schema Comparison. It does cost money, but then again so does DB Pro.

    Like

  2. The Database comparison tools in DB Pro really aren’t migrations. If you’re looking for that, check out my post comparing some .NET Migrations here:
    .NET Database Migration Tool Roundup

    The Schema Comparison tool cannot help you when you are deploying a volatile change, such as adding a new NOT NULL column to a table that has existing data. You have to customize the script. It doesn’t handle refactorings where you change the datatype of a column. Again you have to futz with the script.

    Migrations capture the evolution of the database and applies these changes safely and in order.

    Also I’d like to plug RedGate’s SQL Compare, which is far better than DB Pro’s Schema Comparison. It does cost money, but then again so does DB Pro.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s