Database change managing on software patch/update/upgrade

Question

It's been few weeks I'm trying to find a more detailed explanation with examples how to manage my database change on software patch.

Situation:

• I deploy wpf application release to user. App uses MSSQL data file for storing app's data (Deploying with Client Profile and SqlServer Compact 3.5 prerequisites)
• In further development database schema changes I add some extra data too
• I'm trying to run a patch/minor/major upgrade with Visual Studio 2008 publishing and msi

I want to update users app and database file, but not touch the data which was stored in that database. Seems easy, but i fail to find on web how to get this done with Visual Studio publishing

What I've collected so far are 2 choices:

• Create sql update script for each release and try update database on 1st program launch after patching.
• Create database setup project and try to script from there (no idea how to do this).

I'd be glad to hear what some of you are using or would use such case. Some link to examples/detailed explanation how todo would be awesome. I'm not very good at this deployment/database stuff...yet...:)

Thanks in advance.

Answer 1

This article on "Rails Style Database Migrations in .Net" might be useful. Since the rise of Rails there has been a lot of work on best practices for database migrations, which can be leveraged in other languages and platforms.