Eric M Russell (3/31/2014)
The problem with restoring from a snapshot or transaction log backup is that you may end up "throwing out the baby with the bath water", meaning that legitimate changes made by users to the database in the window between create snapshot and restore will be lost. What we typically do for a routine database deployment is require the developer to supply a "rollback script" with the change order ticket along with instructions on exactly when and how to use it. So, a script that deploys modifications to a handful of stored procedures would also include a rollback script that creates the previous version.Non-routine deployment, for example scripts that update or delete large volumes of data or upgrade 3rd party ISV databases, are more problematic and snapshot may be an appropriate solution for that scenario, but Junior would not be manning the server room by himself that day. Senior would be on site and supervising it step by step.
Excellent addition. I wanted to include this side note myself, but I apparently forgot.
If your rollback scenario after a change is "restore from backup" (meaning you are willing to lose changes made between deployment and decision to roll back), then revert to snapshot is just as good, but quicker.
If you cannot afford to lose changes, then neither restore from backup nor revert to snapsho is an option; you'll have to have a (tested!) rollback script,