I have to ask… Do you know what a roll back script is?
All too often I see database changes made to Production systems in the scripts all nice and pretty, but when I ask for roll back scripts people look at me like a deer in the headlights. Then all the sudden it’s like the light comes on and they turn the tables back on me. The roll back script is the backup you fool of a DBA…. Well lets thing again.
Many databases are required to be online 24 hours a day. So when a change is rolled to production it requires that the change be done after hours or whatever the slowest time of the day is for the database. What worries me is when this situation occurs:
A database that takes updates around the clock, these changes are being delivered into the system by a web service or even a simple web page. After the release the testing team is going crazy to approve the release. A couple hours later a bug is discovered. So you have your backup that does not have the most recent 2 hours of transactions, your only choice is going to be to restore the backup and try to merge the database between the two databases. With larger databases this is going to take some time.
The other reason I am not a fan of backups as roll back plans is 100% motivated by sleep. It sounds selfish but the older I get the more I realize that when I wake up in the middle of the night is a miracle I can still find the restroom in my house. The middle of the night is not always the best time for you or me at least to make decisions. If I have a script that will allow me to load and roll, the decision making is gone. Decisions were made when the team was all there and awake. Now we do have plans that if I am required to be at the top of my game in the middle of the night, that includes a lot of candy and soda.
So the next time you are scripting the changes you are going to apply to the database, take some time and decide if you need roll back scripts. Go the extra steps and develop the plans that you need to remove changes from the database.