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.
Best Answer
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).