0

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

Improve this question
3
  • Relevant question: How to use transactions with SQL Server DDL?
    – ypercubeᵀᴹ
    Nov 2, 2015 at 9:11
  • Thank you, however technical HowTo is not what i am asking for. I am looking for best practices.
    – Vladislav Zalesak
    Nov 2, 2015 at 11:40
  • Are you using Enterprise edition of sql server ? If so, you can leverage - database snapshot and can easily revert it when needed. That being said, you should test your upgrade on a test / UAT system and fix all the issues/failures on that environment. Rollback on PROD should be a last resort. This was you can enhance your build deployment process.
    – Kin Shah
    Nov 2, 2015 at 20:16

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.