This is an over simplified summary of my experiences, handling database changes on different agile projects.

Most of the projects I worked on, we were using ANT for our builds. So we automated our database scripts using ANT.

List of things that need to be in place for this to work:

1. Ever developer has a database instance, either on local machine or on a central server. Local machine is preferred. Properties file can be used to point to the correct database.

2. The development team is following an iterative development model. At the beginning of each iteration/sprint, someone on the team [DBA or build master or someone] creates a baseline of the database from the current database snapshot and stores it in a central place. This baseline can then be used by every developer to bring their DB instance to the baseline version.

3. Under the project directory, we create a folder called DB. This folder contains one folder per iteration/sprint. All the database scripts are added to the respective iteration/sprint folders in which they are created. The whole DB folder is checked into version control along with the code.

4. In the build.properties file, declare a property called current.iteration.number. This property is defines the current iteration or sprint number and is used by the targets in ant to get the baseline DB.

I would like to describe the different scenario that can occur on a project irrespective of whether you have a dedicated DBA or not.

Scenario 1: Developer needs a database change
Developer Activities:
1. Create a sql script [with or without DBA‘s help] as per standards in the appropriate DB folder
2. Apply script to local database using appropriate database client [Eg. Query Analyzer for MS SQL Server, Toad for Oracle, etc]
3. Test application against local database
4. Check in the script into version control

Scenario 2: Team member needs to get database changes made by others during the current iteration/sprint
Developer Activities
1. Update the source tree from version control system. This should get all the database, code and configuration changes from the version control.
2. Run the default ant target [build verification] at the project level (this will run the ‘updatedb‘ target that executes all new database scripts on the local database)

Scenario 3: Beginning of Iteration/Sprint
Activities
1. Backup the database from the build/QA server at the same time as labeling the codebase in your version control (If the team has a Build Master then it‘s that person responsibility to label the codebase and take the backup). The baseline can be checked into version control. Its optional.
2. Update the current.iteration.number in build.properties to the latest iteration/sprint number
3. Run ‘restoredb‘ ant target to get the baseline version of the database for the new sprint.

Scenario 4: Local Database is messed up
Developer Activities:
1. Update the project tree from source control to make sure you have all the latest changes.
2. Run ‘forceupdatedb‘ ant target to restore the baseline database for the current iteration/sprint and to catch up with the latest changes. [Executes the ‘restoredb‘ and ‘updatedb‘ targets]

Properties of SQL Scripts:

1. Naming convention: yyyy-mm-dd-hhmi.sql
The scripts are named based on the time of creation. The name is used to execute the scripts in the order of creation.

2. The scripts should be incremental .i.e. you should never modify an existing script.
For example, you create a new table and check in the create table script. Then you need to add a new column to this table, you should create a new alter table script. The same goes for objects that you cannot modify like stored procedures. You would create new scripts that drop the existing procedure and create the procedure again with modified code.

3. The scripts need not be re-entrant .i.e. if the same script is execute multiple times then they can fail. We use the modified selector on the FileSet element. The modified selector selects files if the return value of the configured algorithm is different from that stored in a cache. So if a file is not updated, then it does not execute the file. This means, only new scripts are executed and the old ones are executed only once.

http://ant.apache.org/manual/CoreTypes/selectors.html#modified

If you want to be sure that the scripts are executed only once, then you could create a version table in database and have the sql scripts check the version to decide if it needs to be executed. The script would insert a row into the version table if it has to be executed. This also gives us the ability to restore databases to a specific version.

All these tasks can be achieved by three simple ant targets:

1. updatedb: Executes all the new (or modified) sql scripts for current iteration/sprint against local server. It executes the scripts in the order of their creation time, based on the script‘s file name.

2. restoredb: Restores the database from the baseline version.

3. forceupdatedb: Executes all sql scripts for current sprint against local server after restoring the local database from the baseline.

To see the actual build file and sample DB folder with sample scripts click here

Note: the scripts and ant targets assume that the database is SQL Server. They can be easily modified for other Databases.
Download the DBChangeManagement.jar and rename it to DBChangeManagement.zip.