Problem: Your development, test and production databases tend to accumulate cruft (aka. old stored procedures or schema) and you need a way to manage changes in a simple automated way.

Solution: I've had the great fortune to work with some excellent teams that have crafted elegant solutions to this problem. The following is a boiled down simple approach that provides a great way to tame database source control and upgrade issues.

Development Tools of Choice: VS.Net 2003 Ent., VSS, Nant, SQL Server 2K

Steps:

1. Using VS.Net and VSS, create 2 database projects. One to contain all objects that can be recompiled without data-loss (Stored Procs, UDFs, Views, Triggers). The second database project to contain a database_release.sql file to contain your schema changes for each release (make sure they are repeatable eg. "if exists ....") Use these 2 projects to manage all you database changes from release to release. Be sure to clean up any re-named or deleted objects from VSS.

2. Create an Nant build file that does the following:

• Allows you to call it with "dev", "test", or "prod"
• Does a recursive get from VSS on the database projects
• Applies an auto-revisioned version label to these projects in VSS
• Stage these files to a well known network location to appropriate sub-directory eg. "dev\AppXDatabase_1.03.22.31"
• Drops all re-runnable objects from the corresponding target database for dev, test or prod
• Apply repeatable schema changes for release
• Recursively apply all the re-runnable objects to the database

3. For each release you can wipe and build the release schema project as needed with new schema for the upcomming release.

Benefits of Approach: By doing this you get a versioned history of changes to your database. You also get versioned stage directories for dev, test and prod environments that have all the schema and re-runnable database objects at that point in time. These versioned stage directories correlate to a label in VSS. By automating the application of the schema and the dropping and recreating of the re-runnable objects your database will reflect your source control.