How do you maintain multiple versions of Databases?

Question

We have many environments

trunk (dev integration) -> devel (team testing) -> qa (regression testing) -> live (customer use)

Each has its own database that works with the code in that environment.

Part of the contents of the database is metadata, and part is data. For example if you are building a report, then the columns you can choose from to build the report are metadata, but the reports that a user has already built are data. Metadata flows up the promotion chain (get introduced in trunk, and goes devel->qa->live), along with code as it gets tested. Data however does not get promoted. Moreover data in a certain environment must not get erased or corrupted and continue to work after that environment has been promoted to.

What good strategies exist out there to manage and equally important, automate such a setup?

We are using .NET/C#/SQL Server but I think this problem is generic and has to be dealt with across the board for any mature application that has a large number of developers working on it, and cares about the data users generate on it.

Answer 1

If you are asking what I think you are, the Database versioning tools in the Team Suite editions of Visual Studio are totally up to this task. They include tools for schema comparisions (DB to DB, and project to DB, etc.), data generation, population, and even unit testing of stored procedures.