Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year.
Currently, a backup is done, and if anything fails, backup is restored and upgrade scripts are checked for reason.
We are looking into the possibility of upgrading the database inside a transaction, rollbacking the changes if anything happens.
Are there any best-practices, reasons, knowledge and/or experience that would say if this is a good idea?
Thank you
Best Answer
Reverting to a valid backup is the most common and safest approach. You can reduce risk and likelihood of needing to restore from backup by versioning the changes.
For instance, say your upgrade process involves 50 steps that have some sequence/order requirements. Any step or small group of steps that can survive a reboot after they are applied should safely rev a version number in your DB so you know exactly what the last successful upgrade step was. You can then resume the process instead of having to restart.
Add this to putting each step or collection of steps in transactions will help ensure your upgrade steps comply with ACID requirements and make your upgrade process more robust. We do this with some of our major upgrades and rarely ever have to restore when something breaks in the middle unless there's a bug in the script of one of the upgrade steps.