Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tracking Database Schema Changes with DbPro

By Jacob Sebastian,

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.

Total article views: 6277 | Views in the last 30 days: 6
 
Related Articles
FORUM

Script to check for last updates in database objects

Script to check for last updates in database objects

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

ARTICLE

Change the Settings of a Database Object Using Powershell

This post tells you how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object...

FORUM

Database Object Permissions Lost

Database Object Permissions Lost

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones