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:
- Recovery to a snapshot is extremely fast, while restoring a database from backups can take a long time.
- Full text indexes are not included in snapshots
- If you recover from a snapshot you've broken the log chain, so you need to do a fresh backup afterwards
- Recovering from a snapshot also breaks any replication or mirroring you may have in place
- If you elect to restore from a backup you'll need to delete any snapshots.
If you can live with the constraints, snapshots look like a terrific way to add some insurance to application/database upgrades.