I'm looking for a way to automated the generation of change scripts for our databases.

Currently we use Visual Studio 2008 database edition, which has the Schema Compare option, but I don't see a way to automated those actions.

What I'd like to do is create an old and a new database during my daily builds and have schema compare generate a change script for the differences between those two. (Differences in schema and procedures, lookup tables can be dropped and recreated without problems)

Does anyone know of a solution for, or am I going to write miles of scripts to get this going?

Using any paid products besides the Microsoft tooling is not an option here...

Update based on feedback:

  • I have various databases of various sizes, but all changes will happen in a controlled manner while being aware of data already present.
  • Only "easy" changes should be taken care of automagically, i'm thinking addition of columns and tables (very common). Columns may never be removed (so the tool/script may protest if this happens)
sql-server visual-studio-2008 sql-server-2005 t-sql tfsbuild
Edit
Report