1

I am currently looking into a fail plan when we do a release to our production server.

The context Currently we have one single production database (also production and testing but they are not important to my question) with MS SQL SERVER 2012. No mirroring and no fail over clusters. We make a full back up every night around 1 am.

The issue When we release a new version of our web application we need to update the production database with SQL update scripts. (changes to the scheme, tables, constraints etc...). We want to keep the down time as short as possible.

When we update our production database with our scripts, and something goes wrong there, we want to revert the failed changes.

My plan

  • DB becomes inaccessible to the web application.
  • I first make a differential backup from the production database.
  • We run our cleanup and update scripts
  • Something goes wrong here
  • Restore the production database by restoring the differential backup

But all documentation states that I should first restore the full backup and then the differential backup.

My Question Is it not sufficient enough to use my differential back up when something went wrong with the cleanup and update scripts ? Or do I really have to restore first the full back up and then the differential backup ? Because in this case, I won't win much time if something goes wrong.

Extra We want change the database setup in the future to avoid this manual approach but for now I am hooked to this context and approach.

Improve this question

2 Answers 2

Reset to default
1

No, it is not - because the differential starts from the last full backup. Not from your current database.

I think you main problem is that you hav an unprofessional environment without a backup system. Normally I would take the last backup and restore it on the reserve system, then run the whole update experience there. This staging environment makes pretty sure nothing will fail during teh update and allows to do last tests by operations. Once qualified there - take a full backup, followed by differential, then take the site offline and run the update - not a lot more you CAN do.

Any db update should / has to be fully automated for making sure it (a) goes fast and (b) goes exactly as it did on staging.

Oh, and depending how national you are - make the backup in an off time where people are disturbed as little as possible, even if that is inconvenient. Depends on what your web app is doing through - some are "regional" (so there is a very low activity night) some have natural maintenane windows (where people are not likely to use it).

Improve this answer
1
  • "you have an unprofessional environment without a backup system". I can't agree more, we are looking forward to hire experienced (db) staff soon to improve our environment. For now we we are not that national, but we want to improve the environment for when we launch in a more international environment. Thank you for the response, I am aware now of our options and limitations.
    – Segers-Ian
    Commented Dec 2, 2013 at 12:09
2

You can't use differentials as stated in the other answer, but you CAN use database snapshots. They require Enterprise Edition, but they function exactly like what you're looking for. You take the snapshot, start your deployment, do user acceptance testing, and then either delete the snapshot or roll back to it.

Be aware that "snapshots" is an overloaded term - there's also snapshot replication (which wouldn't help) and SAN snapshots (which actually would help, but it requires a SAN, which I'm guessing you don't have.)

Improve this answer

Your Answer

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

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