Get my database under Version Control using a DVCS [Mercurial]

Question

What would be the best approach for versioning my whole database ?

Creating a file for each database object (table,view,procedsure..) or rather having one file for all DDL scripts and any new change will be put in a separate file ?

What about handling changes made in a Database manager tool ?

I'd like to have a generic solutions for any kind of RDBMS.

Are there any other options ?

Answer 1

I'm a huge VCS fan in general and a big Mercurial booster, but I really think you're going down the wrong path.

VCSs aren't just about iterative changes, the "what", they're also about answering the "who", "when", and "why". For a database those answers are a lot less interesting or hard to provide to the VCS. If you're doing nightly exports and commits the "who" will always be "cron" and the "why" will always be "midnight".

The other thing modern VCSs do really well is helping you merge changes from multiple branches. That's less applicable in the database world. Very seldom do you say "I want this table structure, but this data", and if you do the text/diff merge isn't going to help you much.

The thing that does do "what" and "when" very well is an incremental backup system, and that's probably the better fit.

At work we use Tivoli and at home I use rdiff-backup and duplicity, but there are plenty of great options.

I guess my general rule of thumb is "if it was typed by hand by a human then it does into source control, and if it was generated/exported then it goes in the incremental backups"

Certainly you can make this work, but I don't think it will buy you much over the more traditional backup solutions.

Answer 2

Have a look at this post

Answer 3

If you need generic solution - put everything in the scripts (simple text files) and put under Version Control system (can be used any of VCS).

Grouping similar database objects into scripts will be depend on your requirement.

So you may for example:

Store table/indexes/ in one or several script Each procedure store in individual script or combine small procedures into one script.

However need to remember one important thing with this approach: don't forget change scripts if you changed table/view/procedure directly in databases and don't create/recreate/compile you db objects in database after changing scripts.