How to undo SQL changes using installer

I have installer to install procedures, scripts, views, etc in SQL server 2005/2008.

Now I want to add a condition in the insta开发者_开发百科ller like if there is any error while installing, I want to undo all the changes done in SQL server.

I tried to store the procedures, views, etc which I am changing while installing and reverting them back if I get any error. But am not able to do it the way I want.

Can someone guide me if he had done the same thing?

To specify I am using WIX installer.

Also if someone has tried SMO, it will be of great help.

The simplest and most robust way to handle this is not to use the installer at all. Rather wrap all your SQL into a transaction block. Using this means that if anything fails for any reasons (as part of the SQL) then the transaction will gracefully roll back and all your DB changes will be reverted without you having to implement any more than defining the transaction block on your SQL statement.

Assuming MS SQL more information regarding transactions can be found here:

http://msdn.microsoft.com/en-us/library/ms188929.aspx

Most other mainstream SQL implementation follow a very similar model, but obviously refer to their docs instead.

If you need to trigger "rollback" of the SQL component of your install if some other component of your install fails. Then unfortunately you can't use transactions in this manner. However in this case you could simply call a rollback script that deletes any SP's / tables etc you have added. That said in .NET you can being the SQL transaction handling into the code (i.e. C#) if that is available to you you could use this to wrap up everything.

It can be difficult to rollback an SQL upgrade script, particularly if that script could fail at any point. The problem is that the built-in rollback machinery cannot handle, for example, most DDL statements. Therefore, you would have to implement such rollbacks manually with compensating scripts that undo the changes.

It might be simpler to back-up the database at the outset of the installation and restore it should the installation fail.