I'm working on creating a SQL migration tool to help automate some of the drudgery in moving and upgrading databases.  Initially I'm only supporting SQL Server 2000 since this is what I primarily use.

The SQL scripts are being generated from the SQL entities (sort of like a generic SMO) via NVelocity templates.  I think the NVelocity approach is working very well because the C# code only needs to populate the top most object, like Table or Column, and the template can walk the hierarchy db objects as needed.

Right now I have Create/Drop table, Add/Drop/Alter column, Create/Drop index, Create/Drop foreign key complete.  This should be enough to modify a table for the most common scenarios.  Currently the committed code only has SQL script generation via the scripting API, so there's no intelligence currently backing it in regards to migration.  It only will write SQL scripts at this point.

The one thing I'm making sure to do, is to bulletproof the generated scripts so they can safely be run multiple times without error.  Here's an example template which safely adds a column to a table.

/****** Object:  Column [$column.Table.Schema].[$column.Table.Name].[$column.Name] $time ******/

IF NOT EXISTS
(
  SELECT * FROM [information_schema].[columns]
  WHERE table_name = '$column.Table.Name'
  AND table_schema = '$column.Table.Schema'
  AND column_name = '$column.Name'
)
BEGIN
  ALTER TABLE [$column.Table.Schema].[$column.Table.Name]
  ADD $SqlScriptHelper.WriteColumn($column)
END
GO

I chose to use NVelocity templates because of their flexibility and the ease of use for generating text documents.  NVelocity is also faster for script generation then SMO (and it actually works unlike SMO), and it gives me the flexibility to do things the non-SQL Server way which sometimes is inappropriate for production systems (change a column's nullability and you'll see what I mean).  One more benefit, is it allows me to customize the output just by modifying the template, no recompile necessary.

Keeping my SQL generation separate from SQL Server will also allow me to re-use this migration tool for other databases like MySql.  It could even potentially allow me to export scripts from SQL Server to MySql syntax since I have an abstraction layer over the database like an ORM would.