Versioning DB copies for installation on test envs
I'm building a config mgmt structure where cruisecontrol pulls a copy of the trunk (and all automated tests contained within) every 30 mins, deploys on a test machine and runs the test suite. We're an agile house so need this regular suite execution.
The problem is with maintaining an up-to-date db schema to match the copy of the trunk taken. Developers append any db changes during an iteration to a sql script held in SVN, and this is run on production for every release. So if the testers are wanting to update their local env they need to remember which sql commands they've already run, and execute the rest. And also need to remember to check the sql script for any updates!
It's a horrible scenario with too many reliances on people remembering to inform everyone, remembering to check the script etc. And as for needing to rollback the test envs, whoah!
I've read about rubyonrails and rake, and it looks the ideal solution - but means that devs need to make a big change in how they provide schema changes i.e. writing into the rails script. Are there any other ways to close this messy config issue?
Thanks very much for your help,