I work on a large Clarion project which we're migrating from Btrieve to SQL Server. As we get closer to actually being able to run a SQL version (for a while we need to generate and compile dual versions), we're also looking at how we can handle database changes and specifically rolling back the database in the event of an upgrade failure.

I've been reading up on SQL Server snapshots, which were introduced in 2005. Snapshots have a lot of uses, such as creating a static copy of your data (which could be useful for reporting). You can also use them to quickly roll back database changes, whether to data or schema.

Among other things I've found an article from the Microsoft operations team on how to use snapshots as a rollback procedure

Key points include:

If you can live with the constraints, snapshots look like a terrific way to add some insurance to application/database upgrades.