Rolling back database when using migration

Has anyone figured out a good process/practice for rolling back when using migrations? When deploy fails I saw some where here that there will be a deployfail script that you can use so that takes care of that scenario. I’m especially interested in the scenario where the migration goes fine but you for some reason find out that you need to roll things back anyway, maybe a day later. The problem I see is that you are at version A with your database using some type of migrations. When you do an upgrade, to B, that will be a different assembly containing the logic for upgrading from A to B as well as the logic to roll back to A from B. The problem is that when you roll back later that most likely means that you install version A again, but that won’t roll back to A since it hasn’t the logic it needs to go from B to A.

1 Like

Have you looked at roundhouse? That wraps everything in a transaction

That doesn’t help in the scenario I try to describe as the problematic one. Also, it won’t be the solution where you have multiple deployment steps in a release since what do you do if one of those steps fails?

The scenario I see is the most problematic is the one where you have done your deployment and everything looks fine but after a couple of hours or up to a day or two you notice something really weird an need to do a roolback. For a web that is no problem, but for a an SQL database that is problematic even if you use migrations. The package you had for the previous release (package A) will not contain the steps that describe how you go from the latest release (package B) back since that is described in the latest release package.

Hi Thomas,

It’s an interesting question, and I’m curious to hear other people’s answers.

The approach I’d probably take is to write rollback scripts, but instead of depending on the tool to run it, I’d ask the DBA to run them manually. The reason is that the reasons for the failure might be different, and so might impact your rollback strategy.

For example, if your upgrade script introduced a new table, and users had been saving information to that table, you probably don’t want to delete the table when you roll back because it contains useful user-submitted information. So the author of a rollback script would always need to consider the scenarios under which it might be run, and try to predict what it should do.

So for something this tricky (and hopefully infrequent) I’d actually probably prefer a manual approach.

That said, there’s probably something Octopus can do to make this easier. For example, if you have a Rollback.ps1 script, Octopus could run it on the current package before installing the previous package.

Alternatively, you could ‘roll forward’ by creating a new package with the old content, plus the rollback scripts.

Paul

Unwilling to back down, are you?

Hi LotCenoicoche,

I wrote some more details about what rolling back means in Octopus here:

Octopus will also now invoke a PowerShell script where you can put custom code to handle deployment failure:

Hope that helps,

Paul