What is the point of database migrations 'down'?

Question

As all databases should be, the source for ours is versioned using source control. The database is upgraded using a series of SQL scripts generated by Red Gate's comparison tool, which is essentially the same as an 'up' migration in the numerous database migration frameworks that seem to have sprung up recently.

But what's the point in the 'down' migrations in these frameworks? Often the code for the 'up' migration is extremely complex (typically complex data migration as features evolve) and I struggle to see the purpose of having to write it all in reverse for the 'down' one. It's certainly something I've never felt the need for. Am I missing something here...?

Answer 1

If you upgrade, and subsequently data is added to your database that you want to preserve, a rollback script (as long as it is designed as such) should achieve this, whereas if you simply restore a backup you'll lose it.

But you could get round the above by restoring a backup and using SQL Data Compare to copy the additional data across.

Answer 2

It seems that the pertinent question here is:

• Why would a scripted rollback ever be preferable to a full database restore from a backup taken immediately before the upgrade?

I can think of several reasons:

1. The database is very large - say a few hundred GB - and your company cannot afford the downtime and/or administrative overhead that would be involved in a full restore.

2. A bug was introduced that was not discovered until a week or two into production. If you've never experienced this before, you're lucky. Once you've got a week's worth of transactions in the new database, you can forget about just restoring from backup.

3. The bug was not discovered until months into the release. In other words, you don't even have the backup anymore, and you're officially in damage control/disaster recovery mode. I've never experienced this, but I've heard stories. It's a scary thought - how do you undo all the damage that was done? In this case your downgrade might not be perfect, but it might still be better than the alternative.

4. By contrast, perhaps the database changes were trivial - adding a few rows here, a few triggers there. In this case, a scripted rollback is going to take much less time than a restore. It's possible that some things that took hours to upgrade - such as creation of new indexes or addition of new columns - may only take seconds to downgrade (drop).

5. You're deploying to customer sites. Some of them may not have backups at all (yeah, it's pathetic, but there's nothing you can do about it). If one of them needs a rollback, this is your only option.

There may be other reasons to have downgrade scripts - this is just off the top of my head.

Answer 3

Customer: "We don't like the new version and want to go back to the old version."