Before

Working on a project that includes a database implies dealing with the schema changes and versions. The problem is that the tools that can help you are pretty expensive and (from my experience) not 100% reliable. It was so bad that my team and I developed our own database versioning mechanism and even started working on a tool to automate it.

What I want

I want to be able to deal with the database schema objects the same way i deal with the code files - specifically, I want to be able to see a svn history for them.

I want to be able to deploy a new version of the database to test and production servers AUTOMATICALLY, without worrying about data loss.

I want to be able to restore an old backup (like one with specific test data) of the database and update it to the latest version.

I want to keep the developer, test and production databases in sync (that is, the schema is the same for the same version).

I want the configuration data (read-only data like states and cities) to be included in the versioning system. More...

Databator is a tool I’m working on that enables you to manage the versioning of your database based on the method described here.

This first version allows you to:

• Create an project for a new or existing database
• Add versioned changes to the database
• Use command line scripts to update, backup or restore the database (handy for Continuous Integration builds)
• Conciliate your changes with those made by tour team mates
• Work in branches and then merge your changes

The final scope of this tool will include:

• Integration with SVN
• Synchronization with Visual Studio Database projects (.dbp)
• Apply or Rollback your updates directly from the UI
• History for each DB Object (just like code files)
• Helpers for creating update scripts (specific cases)
• Automatic rollback script generation (specific cases)

I recommend that you check the Getting Started Guide to get an idea about how this tool can help you optimize your database versioning.

You can download Databator v0.1 from here: Databator0.1.zip (982.01 kb)

One common problem in projects that involve a database is managing its versions.
Basically, there are two needs to be fulfilled:
- to be able to see a history of changes for a database object in a similar way to seeing the changes for a code file
- to be able to update / revert the database to a specific version without losing the data.

For the first one, a simple solution is to just script all objects in the database to a folder and add it to the source code repository (one file per object - in MS SQL Server can be done with a few clicks from SQL Server Management Studio). When there are changes, all you need to do is to repeat this process and the files will be updated. You can then review changes to database objects in the same way you review them for code files.

The second one is a little trickier. Idealy you want scripts that can be automatically used to:
- update developer database copies
- update the continuous integration database (database used for the continuous integration tests)
- update the test database
- update the production database.

There are tools that give you "diff" scripts that can be used to update a database, but, apart from the price problem, they are difficult to use for several-times-a-day automatic updates like the ones needed to update the developer copies. A second problem is that, because the developers have full freedom in their database copy, some "garbage" or unwanted changes might be picked up and included in the diff script.
The solution below is free, the updates can be run automatically whenever is necessary on any database (developer, test and even production) and ensures that the changes were really meant to be included in the update. All you need is a little discipline from the developers. More...