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.
- 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.