I am searching for understanding of guidelines regarding database scripting for modifications which go out with other code changes for any software system.
I did previously work for an organization that was adamant that each unveil includes a roll back ready just in case of issues. This sounds sensible, but for me, the roll back code for database modifications used via scripts has got the same probability of failing because the unveil script.
For handled code, version control makes this fairly simple, however for a database schema, moving back changes isn't very easy - particularly if information is transformed included in the unveil.
My current practice would be to test the unveil code by running against an evaluation database throughout late stage development, after which run the applying against that test database. After this, I support the live DB, and proceed using the unveil.
I am yet to operate right into a problem, but am wondering how other shops manage database changes, and exactly what the technique is for recuperating from the bugs.
SQL Diff (or something like that like it is usually useful if you work with an evaluation database. It provides extensive constraints, safeguards, and methods for rebuilding or moving back if there's an problem. Very helpful.
Our database scripts undergo several test phases against databases which are like our live database. By doing this we are able to be fairly sure that the alteration scripts will act as expected.
For moving back, saved methods, sights, functions, triggers, everything programmatic is simple to roll back, just apply the prior version from the object.
As if you pointed out, the cruel part comes when upgrading / removing records from tables, as well as adding new posts to tables. And you are right that within this situation the rollback could be just like prone to fail.
What we should do is that if there exists a change that can not be easily folded back, but is really a sensitive / critical section... is the fact that there exists a set rollback scripts which feel the same testing conditions. We run the update script, validate it works not surprisingly, after which run the rollback script, and validate it works because it did just before the alteration.
Another factor that people do as only a precaution is to produce a database snapshot (SQL Server 2005) just before an update. This way if you will find any unpredicted issues, we are able to make use of the snapshot to recuperate data which was potentially lost throughout the update.
Therefore the most secure strategy would be to test against databases which are as near for your live system as you possibly can, and also to test out your rollback scripts too... and merely just in case each of individuals fail, possess a snapshot ready just just in case you really need it.