How do you share SQL changes within your team?

Question

Whenever you make database changes, how do you apply these changes to others databases on the team (and also your servers)?

Currently we are using a file called changes.sql where we put all our changes, separated with ISO date comments.

Is there a better way?

Answer 1

We use an expanded version of your approach.

We have an database upgrade folder for each release, which contains all the scripts which are part of the release. There is one index file in the folder, which contains pseudo links to all the scripts which should be run.

We have a cruise control job which runs each night to restore a copy of the current production database, then runs the current release's upgrade scripts against it (by executing the scripts defined in the index file). There's also a CI job which runs whenever anyone checks anything into the upgrade folder for the current release.

The scripts need to be re-runnable obviously, eg they should check for the existence of something before dropping or creating it.