Get my database under Version Control Using DVCS [Mercurial]

Question

What would be the best approach for version control of my entire database?

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

How to handle changes made to the database manager tool?

I would like to have common solutions for any DBMS.

Are there any other options?

Answer 1

I'm a big fan of VCS in general and a big Mercurial amplifier, but I really think you are going the wrong way.

VCS are not just iterative changes, “what”, they also answer “who”, “when” and “why”. For a database, these answers are much less interesting or inaccessible for VCS. If you do night export and fix “who” will always be “cron”, and “why” will always be “midnight”.

Another thing is that modern VCS really help you merge changes from multiple branches. This is less applicable in the database world. Very rarely, you say, “I want this table structure, but this data,” and if you are merging text / diff, it will not help you.

What the “what” and “when” does very well is an incremental backup system and is probably better suited.

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

I assume that my general rule of thumb is “if it was introduced by man by man, it brings it into the original control, and if it was generated / exported, then it goes in incremental backups”

Of course, you can do this work, but I don’t think it will buy you for more traditional backup solutions.

Answer 2

Look at the post

Answer 3

If you need a general solution, put everything in scripts (plain text files) and put it in the version control system (you can use any of the VCS).

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

So you can, for example:

Save the table / indexes / in one or more scripts. Each procedure is stored in a separate script or combines small procedures into one script.

However, there is one important thing to remember with this approach: do not forget the change scripts if you change the table / view / procedure directly in the databases and do not create / create / compile db objects in the database after changing the scripts.

Answer 4

SQL Source Control currently supports SVN and TFS, but Mercurial queries are growing rapidly, and we hope the story comes very soon.

We use UserVoice to measure demand, so please vote if you are interested in this: http://redgate.uservoice.com/forums/39019-sql-source-control