We are a software product firm and we release new versions of our products periodically. Our product relies on a SQL Server database to which we have to constantly update the schema or seed data or the stored procedures..

Currently, we maintain a one big alter script that keeps growing with every release of the product. Developers keep adding more schema changes or seed data changes or patches to existing data. All the changes check for existence of the object by querying catalog tables before applying the change to an existing database.

I am wondering how DB pro can help our process and development efforts.

We need a way to take an existing database at some release point X to a release point Y. Some way to generate the script for the delta without running the whole script from the scratch. It adds to our install time for the product.

We should also be able to gen a script for setting up a gold copy for the current database that new customers would receive..

Database versioning and change script genning from rev A to rev B would be really something nice to do..

What advice or recommendations would you have for us and how can DB pro help in our development efforts

I can provide a sample of our create and alter scripts if required..


Re: Visual Studio Team System - Database Professionals QUESTION : Can DB pro handle our scenario ?

StevenPo - MSFT

SivaS -

This sounds like exactly the sort of problem that Team Edition for Database Professionals was intended to solve. You would import the desired database schema into a database project. You then place that database project under version control. Any time you produce a "release", you mark that version of the database project and its contents with a version control label, so you can always return to that version if you need to do so. When you build and deploy the database project, it handles comparing the target database to the database project and figured out what changes need to be made to synchronize the target server with the project. For a new database, the build script would be the "gold copy" that you'd want, and for updated, the build script contains the necessary set of CREATE/DROP/ALTER statements to sychronize the two.

If you needed to revert a database to a specific version, or upgrade a database to an older version, it's easy enough to get a specific labelled version, then build and deploy that to a target database.

if you have additional questions, we're happy to help. You might want to check out a few of the topics in the documentation such as:

That might help give you an idea of whether DBPro will work for you (it sounds like what you need Smile).