vb.net, visual studio 2010, sqlce 3.5 database
I have a project that uses clickonce to deploy automatic updates. Clickonce overwrites the user's current database every time I publish an update, which I can get around by excluding the database from my project when I publish the update. My issue is that I now need to adjust the schema of the database, for example, add a new column to an existing table.
If I publish the update with the database, the user loses all of their existing data. I'm not sure how to get around this. So far, I have set the program up to create a backup of the database everytime the program opens. The backup is located in an area that will not be unistalled or overwritten by the update, but I don't now how to pull the data from the backup into the new blank database that has the updated schema.
Does anyone have any ideas or a different way to handle this issue? The end result needs to be that if I publish an update with an updated database schema, the user does not lose their data either by altering their existing database columns or re-populating the data from their database backup into the new database.