SQL migration tool

Question

I want to write a SQL migration tool. This tool must:

• merge new scripts in a folder,
• validate the merging of those scripts,
• run those scripts against a database.
• if a validation error occurred, then highlight the invalid rows.
• if the script was successful deployed, I want to save this information in a database with the version and other related information.

Can I do this with some validation framework, or maybe can I use nHibernate?

Answer 1

Not exactly what your asking for but I think I know what your trying to achieve.

http://code.google.com/p/migratordotnet/

http://ayende.com/Blog/archive/2009/05/01/nhibernate-validator.aspx

And also Schema Updater (part of NHibernate)

This blog has a code sample. http://morten.lyhr.dk/2008/03/nhibernates-schemaupdate-feature.html

Answer 2

Hey mate,
Try DBSourceTools. http://dbsourcetools.codeplex.com
By using the patching engine, it will allow you to reliably test patch scripts against a database at a known state.
It is an open source tool that combines features of Visual Studio Database Edition and MSSQL Management Studio.
Ideally, you would want to test all scripts that you are running in any sort of migration.
Why would you need to store whether a validation error occurred in a script ?
Surely this means that the script was not tested correctly ?
Have fun.