Managing database updates

Question

I've been thinking of ways to improve managing changes to our database structure. I have a build server that creates nightly builds, so I was thinking we could somehow create database dumps, backups, and scripts from the test environment as part of the build process. Then when deploying an update to the client we could use a tool like DBDiff to create the database update script.

Is anybody doing something similar? Is it even a good idea? Maybe some good tips what to use to create these dumps on build server?

Answer 1

Rather than identifying the differences, I recommend to keep a proper script that creates a database from scratch.

We are quite satisfied with using Liquibase to manage all DB migration in our projects. It knows which "patches" have been applied and ensures that only those that are missing will be applied to the target database.