What is the point of down-migrating data?

Question

Since all databases should be, the source code for our user is versioned using a control source. The database is updated using a series of SQL scripts generated by the Red Gate comparison tool, which essentially matches up-migration in the many database migration infrastructures that seem to have appeared recently.

But what's the point of “down” migration within this framework? Often the code for up-migration is extremely complex (usually a complex data migration as functions evolve), and I struggle to understand that I need to write everything in the reverse order for down. This, of course, is something that I have never felt. Am I missing something here ...?

Answer 1

It seems that the question is relevant here:

• Why is scanned rollback ever preferable to fully restoring a database from a backup made immediately before the upgrade?

I can think of several reasons:

• The database is very large - say, several hundred GB - and your company cannot afford the downtime and / or administrative overhead that will be involved in a full recovery.

• An error was introduced that was not detected until a week or two into production. If you've never experienced this before, you're in luck. After you have a week of transactions in a new database, you can forget about simply restoring from a backup.

• The error was not detected until several months in the release. In other words, you no longer have a backup, and you are officially in damage control / disaster recovery mode. I have never experienced this, but I have heard stories. This is a terrible thought - how do you cancel all the damage that has been done? In this case, your downgrade may not be ideal, but it may be better than an alternative.

• In contrast, perhaps the database changes were trivial - adding a few lines here, a few triggers. In this case, the rollback according to the scenario will take much less time than the recovery. It is possible that some things that took several hours to update — for example, creating new indexes or adding new columns — may take just a few seconds to drop.

• You are deployed to customer sites. Some of them may not have backups at all (yes, it’s a pity, but you can’t do anything about it). If one of them needs a rollback, this is your only option.

There may be other reasons to have downgrade scenarios - it's just from the top of your head.

Answer 2

Client: "We do not like the new version and we want to return to the old version."

Answer 3

• Rollbacks. You push everything towards production, and it explodes up - down - a good protective net for folding back.
• Or you develop several branches of code - you can move between versions to your heartfelt content.

Answer 4

If you update and then the data is added to your database that you want to keep, a rollback script (as long as it is designed as such) should achieve this, whereas if you simply restore the backup, you will lose it.

But you can get around the above by restoring the backup and using SQL Data Compare to copy additional data.