DDL Best Practices question

I am looking for some examples of how to manage DDL scripts among
various versions of a production db and development and testing. I
have tried a few things in the past, and it always gets very muddled
and cumbersome.

I need to be able to build any version of the database from scratch,
BUT I also need to maintain an upgrade path from any version to any
later version. So it is not enough to just maintain a master build
script, but I don't want to maintain 2 different things (modify the
master build scripts AND create a new "ALTER" script for each version
change).

I thought I had seen an article somewhere that layed out a process for
managing this, but I can't find it now (I thought it was in SQL Server
Mag). Does anybody know of this article or have a resource they could
point me to that outlines best practices in this area?

Thanks,
Jason Wood, DBA in training."Woody" <jaydub99@.hotmail.com> wrote in message
news:a895dd46.0311241003.70d5a28d@.posting.google.c om...
> I am looking for some examples of how to manage DDL scripts among
> various versions of a production db and development and testing. I
> have tried a few things in the past, and it always gets very muddled
> and cumbersome.
> I need to be able to build any version of the database from scratch,
> BUT I also need to maintain an upgrade path from any version to any
> later version. So it is not enough to just maintain a master build
> script, but I don't want to maintain 2 different things (modify the
> master build scripts AND create a new "ALTER" script for each version
> change).
> I thought I had seen an article somewhere that layed out a process for
> managing this, but I can't find it now (I thought it was in SQL Server
> Mag). Does anybody know of this article or have a resource they could
> point me to that outlines best practices in this area?
> Thanks,
> Jason Wood, DBA in training.

One possible approach is to maintain only CREATE scripts, and use versioning
in your source control system to ensure that you can always build a given
version from scratch. To generate a upgrade script, you can then create
empty databases for the source and target versions, and use a comparison
tool such as the one from Red Gate to create a migration script. If you have
many versions, then you might do this only on demand; if you have fewer, you
might do it every time you produce a new version.

Whatever approach you take (and I'm sure there are many others which work
fine), a database comparison tool is always a good investment. The Red Gate
one is relatively cheap compared to multi-platform tools like Embarcadero,
and works very well:

http://www.red-gate.com/sql_tools.htm

Simon