Introduction

One of the challenges that every database developer would find most of the times is tracking the database schema changes.

The days may be very stressful when an update is to be done on the production server. Every schema changes done after

the previous update need to be carefully tracked and correct update scripts need to be generated for updating the production server.

If you do not do it very carefully, there are all chances that the application will break after deployment of the new version.

I have seen teams who note down the schema changes every day and create a consolidated final list for the update on the

production server. This approach is very time consuming and there are all chances that one or two changes may be missed

due to human errors.

A better solution would be to use a tool which can compare the development database and production database and generate

an update script to be run on the server. This approach seems to be very efficient, less time consuming and there are

less chances of errors. There are several tools available on the Internet today.

This web page lists 11 products that are widely used for tracking DB schema changes. Out of these 11, I have tried

Red-gate's SQL Compare and I found it to be an excellent product. I suppose the other products must be good too.

In this article I am going to present Visual Studio Team Edition

For Database Professionals, widely known as DBPro or DataDude as a solution to the above problem. We will shortly examine the capabilities

of DBPro to track database schema changes and generate update script for the changes.

Creating a DBPro project

Let us create a new Database Project. Go to File, New Project and create a new Database Project.

For the purpose of this example, I have restored the AdventureWorks sample database as AdventureWorksDev. After restoring the

database, I have modified a table, view and stored procedure. I have created a new table and a new stored procedure too. Now let us compare

the databases and generate the update script.

Generating Update Script

Let us start comparing the databases and generate Update Script. Go to Data menu and select New Schema Compare.

Select Database as Source Schema as well as Target Schema.

With DbPro you can compare a Database or Database Project with

another Database or Database Project. For the purpose of this example, we will compare the two databases I mentioned above.

Click on the button New connection and create Source and Target connections.

I want to update AdventureWorks database with the changes done on AdventureWorksDev database. Hence I selected

AdventureWorksDev as source and AdventureWorks as target.

Click on OK button and DBPro will start a schema comparison. After comparison the results will be displayed as follows.

The RED rectangle shows the objects in both databases. The BLUE rectangle shows the scripts of the selected objects in both databases.

Given in GREEN is what we need. You have the Database Update Script there.

Review the Changes

Well, no one wants to update the production database without reviewing the changes. Let us review the changes now. Let us close the script

window so that we have enough space to see the object list and object definition windows. Let us now filter the object list so that

we see only objects which are new, edited or deleted.

After you apply the filter, you will see only objects which are changed, new or deleted. Now let us see what has changed in the object.

Click on an object which is updated and you will see the changes in the Object Definition window below.

The object definition window is a very handy for reviewing the changes. If you scroll one window, it automatically scrolls the other

window too. This way you can see the location and the code which has actually changed.

Now it can happen that you don't want to update all the objects. For some reason, you don't want to update a few objects. To do that,

you can select the object and set the Update Action to Skip.

Updating the changes

Now it is time to update the target database. There are two options to do this. The easiest option is to click the Write Updates

button on the toolbar. This will directly update the target database with the changes.

The other option is to click on the Export to Editor button. This will generate the update script on a new window. Then you can click

on the button on the toolbar to execute the script on the target database.

If you prefer, you can copy the script to SQL Server Management Studio and run it from there too.

Please note that DBPro will not allow you to SKIP an object if some other objects are depending on it. For example, if you want to skip

a table, you need also to skip the stored procedures that uses the table. Unless you skip the stored procedures or other dependent objects, you

will not see the option SKIP on that object.

Conclusions

We have just seen how DBPro helps to compare databases and generate update scripts. I had been using it for a while and found it to be excellent.

In the next few articles, I will try to present other interesting features of DbPro.