Summary of environment.

• Asp.net web application (source stored in svn)
• sqlserver database. (Database schema (tables/sprocs) stored in svn)
• db version is synced with web application assembly version. (stored in table 'CurrentVersion')
• CI hudson server that checks out web app from repo and runs custom msbuild file to publish/package app.

My msbuild script updates the assembly version of the web app (Major.Minor.Revision.Build) on each build. The 'Revision' is set to the currently checked out svn revision and the 'Build' to the hudson build number (incremented on each automated build).

This way i can match the app to a specific trunk revision also get other build stats from the hudson build number.

I'd like to automate the collecting of migration scripts (updated sprocs etc) to add to the zip package. I guess by comparing the svn revision of the db that has yet to be deployed to, to the revision being deployed, i can find what db files have changed in the trunk since the last deployment to that database/environment.

This could easily be achieved by manually calling the svn diff -r REVNO:REVNO command to list changed .sql files. These files could then manually have to be added to the package. It would be great if this could be automated.

Firstly i'd imagine I'll have to write a custom task to check the version of the db that has yet to be deployed to. After that I'm quite unsure. Does anyone have any suggestion on how this would be achieved through an msbuild task either existing or custom?

Finally I'll have to autogen a script to add to the package that updates the database version table so as to be in sync with the application.