Tool or Best practice for installing a database with a client application

I have come up with 2 methods for installing a local database with my WPF application:

1. Create a backup of the DB, then restore in the installer via SMO
2. Script the database install and execute 开发者_JAVA技巧it via ADO or osql.exe

I'm required to provide an upgrade path, rather than just dropping the currently installed databases.

Are there other methods that are more flexible? Are there better/easier to manage tools?

---

Have you looked at Sql Server Compact 3.5 SP1? The install is just a question of copying the database files and referencing the assemblies in your project.

http://www.microsoft.com/sqlserver/2008/en/us/compact.aspx

What do you mean exactly with an upgrade path? Future newer versions of the same application and database?

---

The methods I've seen that support incremental upgrades are all something along the lines of maintaining a set of "change scripts". For an initial install, you can restore a known "clean" backup to a new DB, or you can use a SQL script or an ORM tool to project a schema into the DMBS. NHibernate has the ability to generate a schema given a mapping configuration, and you can set this behavior up from inside an installer.

Once the DB is installed, you have to update incrementally. That generally means creating a script for each change to the DB, and including them in the install package, with a "runner" (a simple batch process) that will spin through the scripts and execute them against the DB. If they're order-dependent, make sure your file structure takes that into account (but it's usually best to combine two scripts when one must be executed first). Also make sure that the change is not "destructive"; for instance, adding a column to a table cannot involve dropping and recreating the table, even if it would be less code. The change must also be either repeatable or non-repeating, as you're likely going to have to include the same script twice. This can mean keeping track of names of scripts you've executed, and/or by having each script detect that a change it makes has not already been made before executing it.

Keep in mind that if you use views, stored procs or SQL functions, these can be dropped and recreated at will without affecting data.

Lastly, make sure that all connections to a DB being updated are closed or dropped before executing updates to avoid unpredictable behavior.

---

For the initial install do a restore of the db. Always ship the current version of the db with the app.

For the update requirement you need to a list of change scripts, one for each new version. I like the RoR approach where the db includes the version.

This makes it easier to just check which version the db is in, and applied all the change scripts after it.