What methods exist for handling DB Change Management?

Question

I recently began working with version control in my web-development using subversion. This works great for managing the files I develop but does nothing for the changes I sometimes need to make to the databases. To my knowledge the server I'm working on doesn't have any DB management systems in place and I likely wouldn't be able to get them to install anything. What options are there for managing DB's in this kind of environment?

Answer 1

K. Scott Allen has written some excellent blog posts about this a few years ago. They might be "old" in internet terms, but his solution is still very nice.

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

Answer 2

Migration frameworks are code libraries that provide a simple API to automate DB changes, with some mechanism for versioning the DB.

I use Migrator.Net for some projects. I create a class for each changeset, and the framework provides a simple way to execute the changes against a DB. I can also run changes forwards and backwards, provided I code the logic for downgrading as well as upgrading.

We then manage the migration code in our VCS like any other code.

Answer 3

Nightly backups of course (at least) .. Because its not just the DB structure you care about.

You have several options:

1) Keep your alter scripts in the Source control.

2) After each db change generate the Db scripts.. and Store those in Source control along with a DB backup at the same time. This way you can do a compare on the Generated Script file.