Database Modification Scenarios - Best Deployment / Rollback Practices?

Question

I am looking for information on best practices for database scripts for modifications that come with other code changes for a software system.

I used to work for a company that insisted that every roll should roll back in case of problems. This sounds reasonable, but in my opinion, the rollback code for database modifications deployed through scripts has the same chance of failure as when deploying the script.

For managed code, version control makes this pretty easy, but for a database schema, rolling back changes is not so easy - especially if the data changes as part of the deployment.

My current practice is to test the deployment code by running a test database at a late stage of development and then launching the application with this test database. After that, I back up the live DB and continue the deployment.

I still have a problem, but I wonder how other stores manage database changes, and what is the strategy for recovering from any errors.

Answer 1

All of our database scripts go through several stages of testing with databases that are similar to our database. Thus, we can be fairly confident that the modification scripts will work as expected.

For rollback, stored procedures, views, functions, triggers, all software, rollback is easy, just apply the previous version of the object.

As you mentioned, the hard part comes up when updating / deleting records from tables or even when adding new columns to tables. And you are right that in this case, a rollback can be as likely as a failure.

We do this if we have changes that cannot be easily discarded, but this is a sensitive / critical section ... is that we have dialback rollback scripts that also go through the same test environments. We run the script update, verify that it is working properly, and then run the script rollbacks and verify that it works as it was before the change.

Another thing we do as a precaution is to take a database snapshot (SQL Server 2005) before upgrading. Thus, if there are any unexpected problems, we can use the snapshot to recover any data that was potentially lost during the update.

Thus, the safest way to do this is to test the databases as close as possible to your live system, and also check your rollback scripts ... and just in case both of them fail, if necessary.

Answer 2

SQL Diff (or something like this is always useful if you use a test database. It has many checks and balances, guarantees and recovery methods, or rollback if there is a problem. Very useful.

http://www.apexsql.com/sql_tools_diff.aspx