WiX: how do you go about updating the db?

Question

In my WiX project I have a file data and a SQL scripts which create db, create/alter tables insert/update rows, etc. All scripts are separated into three parts and are executed via SqlScript element. I use ContinueOnError="no" but if the previous script was executed successfully it don't be rollbacked. Can I wrap all scripts in transaction and use try/catch blocks? Is there a chance to handle catch event from WiZ? What can you advise to make such kind of the installer?

Answer 1

We don't use Wix SQL extension, we run custom actions to do the job we need.

On install, we use custom actions to first backup the database, then run the right upgrade scripts (based on version of current database), and if needed restore the database to the backup as a rollback action of the upgrade.

On uninstall, we backup the database, delete it (conditionally based on user input), and restore if anything goes wrong during the uninstall.

Answer 2

Wix does not handle SQL scripts that way.

I believe your choices are fairly limited.

1. Create a database backup before installation and restore it on install failure. Unless you know for certain that the data size will always be small this probably should not be an automated part of the installer.

2. Provide rollback sql scripts to be sequenced and run in case of install failure. This can be a real pain in the ass to get correct depending on the types of DB changes you need.

Offhand I am unaware of any installer tool kit that even attempts to automate database rollbacks as part of a larger install. There are just too many variables to account for. (e.g. how long the rest of the non-DB installation takes and the affect that could have on database connection timeout)