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.
