How do you approach database versioning when performing regular builds?

Question

I have a web application project that works with a fairly large database (over 5 GB). The data in the database is shared by project. Each project takes approximately 1 GB, and this is the minimum set for the application to work (we do some mathematical calculations that apply to this data set, and deleting part of the data set is not an option).

As part of our daily build, we also deploy our application in a test environment. To do this, the builder launches his own database update utility, which updates the current database to the appropriate version. But we also have a requirement to save all daily assemblies, so that the QA team can "come back in time" and compare the calculation results with different assemblies. As much as we try to have a backward compatible data schema, sometimes it is very complicated and time consuming. So, therefore, the question is:

What approach would you use to version control the database if you had the requirement to keep previous daily builds and run and need to manage a large database with daily builds?

We use SQL Server 2005 and ColdFusion with some Java in the interface, if that matters.

Answer 1

I would suggest reading Leon Bambrick's list of 11 Database Versioning Tools

And I would add Visual Studio for Database Specialists to the list that can be found here.

Jeff Atwood also has a nice blog post on this subject with links to some background readings.