How do you version your database schema? [closed]

Question

See

Is there a version control system for database structure changes?

How do I version my MS SQL database in SVN?

and Jeff's article

Get Your Database Under Version Control

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

Mechanisms for tracking DB schema changes

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

You might take a look at another, similar thread: How do I version my MS SQL database in SVN?.

If you are still looking for options : have a look at neXtep designer. It is a free GPL database development environment based on the concepts of version control. In the environment you always work with versioned entities and can focus on the data model development. Once a release is done, the SQL generation engine plugged on the version control system can generate any delta you need between 2 versions, and will offer you some delivery mechanism if you need.

Among other things, you can synchronize and reverse synchronize your database during developments, create data model diagrams, query your database using integrated SQL clients, etc.

Have a look at the wiki for more information : http://www.nextep-softwares.com/wiki

It currently supports Oracle, MySql and PostgreSql and is in java so the product runs on windows, linux and mac.

I don't manage deltas. I make changes to a master database and have a tool that creates an XML based build script based on the master database.

When it comes time to upgrade an existing database I have a program that uses the XML based build script to create a new database and the bare tables. I then copy the data over from the old database using INSERT INTO x SELECT FROM y and then apply all indexes, constraints and triggers.

New tables, new columns, deleted columns all get handled automatically and with a few little tricks to adjust the copy routine I can handle column renames, column type changes and other basic refactorings.

I wouldn't recommend this solution on a database with a huge amount of data but I regularly update a database that is over 1GB with 400 tables.

I make sure that schema changes are always additive. So I don't drop columns and tables, because that would zap the data and cannot be rolled back later. This way the code that uses the database can be rolled back without losing data or functionality.

I have a migration script that contains statements that creates tables and columns if they don't exist yet and fills them with data.

The migration script runs whenever the production code is updated and after new installs.

When I would like to drop something, I do it by removing them from the database install script and the migration script so these obsolete schema elements will be gradually phased out in new installs. With the disadvantage that new installs cannot downgrade to an older version before the install.

And of course I execute DDLs via these scripts and never directly on the database to keep things in sync.