Automated generation of database change scripts

Question

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)

Answer 1

Similar to what @Anton Gogolev is doing, we are using a tool that allows you to write migrations in XML files. The tool we use is called Liquibase and it supports many different DBMS flavors. Not only do we use it internally on our developer schemas, we also use it externally during the upgrade installation that the customer runs.

Answer 2

Hope I can still help:

You should be able to do this with any good database comparison tool that supports command line interface. In that case you would have to create a simple bat script and add it to task scheduler.

I know ApexSQL Diff supports this functionality and I’m pretty sure SQL Compare from Red Gate has the same option in pro version.