tracking changes made in database structure

Question

How would I be able to track all changes to my database?

I need a way to find out exactly what changes I made to the database so that I can make the same changes on another server.

For example, if I added a new field in one of the tables, I would like to know what field that was so that I could add that same field to another database.

Answer 1

The steps people usually go through when facing these problems are:

1. Change the development db in whatever way is needed at the time.

2. Ready to deploy a new version of the application - realize the production database needs to be updated as well while retaining the data.

3. Dump the production and development schema, manually diff the schema creation scripts. Bang head against wall. Write change scripts that updates the production database.

4. Realize the change scripts needs to be written from the start when updating the development database instead of willy-nilly add and change stuff.

5. Find a naming scheme for versioning those change scripts so any installation can be updated from a given version to a newer version.

6. place the DB creation and change scripts in source control.

Make sure you're not repeating 1-3 every time, but get around to do 4-6 so you don't have to repeat 1-3 every time.