Best way of upgrading SQL Server Compact Edition 3.5 database?

Question

I have a desktop application that uses an SQL Server CE 3.5 database. I can not use any other SQL Server edition. Now I'm wondering: In case of future updates to the functionality, how would I best upgrade the database (= keep current data but add new fields/tables/etc.)?

If I used one of the server editions, I'd just write an upgrade script and run it. My current idea is to do the following:

1. Rename the old database file (backup)
2. Copy the new database file
3. Connect to both database files
4. Copy all data from old to new database programmatically

This works, but the approach does not seem optimal. Is there an easier and more reliable way?

Answer 1

Try Wizardby: it allows you to specify database changes incrementally and it will apply these changes in a very controlled manner. You'll have to write an MDL file and distribute it with your application along with Wizardby binaries, and at application startup it will check whether database version is up to date and if not it will apply all necessary changes in a transaction (luckily, SQL Server CE supports DDL transactions).