How to rollback a database deployment without losing new data?

Question

My company uses virtual machines for our web/app servers. This allows for very easy rollbacks of a deployment if something goes wrong. However, if an app server deployment also requires a database deployment and we have to rollback I'm kind of at a loss. How can you rollback database schema changes without losing data? The only thing that I can think of is to write a script that will drop/revert tables/columns back to their original state. Is this really the best way?

Answer 1

Generally speaking you can not do this.

However assuming that such a rollback makes sense it implies that the data you are trying to retain is independent from the schema changes you'd like to revert.

One way to deal with it would be to:

• backup only data (script),
• revert the schema to the old one and
• restore the data

The above would work well if schema changes would not invalidate the created script (for example changing number of columns would be tricky).

This question has details on tools available in MS SQL for generating scripts.

Answer 2

But if you do drop columns then you will lose data since those columns/tables (supposedly) will contain some data. And since I'd assume that any rollbacks often are temporary in that a bug is found, a rollback is made to get it going while that's fixed and then more or less the same changes are re-installed, the users could get quite upset if you lost that data and they had to re-enter it when the system was fixed.

I'd suggest that you should only allow additions of tables and columns, no alterations or deletions, then you can rollback just the code and leave the data as is, if you have a lot of rollbacks you might end up with some unused columns, but that shouldn't happen that often that someone added a table/column by mistake and in that case the DBA can remove them manually.