I guess this is exactly this misconception. The problem happens when you encounter a scenario where your rollback doesn't work. I.e.:

> when there is an issue, it happened because something not planned happened

In 12+ years it worked every time.

I understand it didn't work for you, but it does work for me.

I'm curious how you implemented this system.

In my SQL Server database I have a VERSION table with one row and one column that contains the current "version" of the database, and a DowngradeSteps table that keeps track of what needs to be done to undo the changes.

In my project source control I have a SQL script SyncSchema.sql. It contains a series of step-pairs (roll-forward + roll-back):

  if (select V from VERSION)<XXX
  begin
      begin tran 
          update V=V+1 from VERSION
          ***Roll-forward: ALTER TABLE FOO ADD BAR INT***
          insert DowngradeSteps values (
              (select V from VERSION), 
              '***Roll-back: ALTER TABLE FOO DROP COLUMN BAR***'
          )
      commit tran 
   end

where XXX started from zero and is going up every time I need to add a change. The last batch in the file is the highest XXX it knows about, and it is therefore the TARGET db version. This script run upon each deployment, and if you ignore the DowngradeSteps/roll-back part is the usual roll-forward scheme.

Now the roll-back part: once SyncSchema.sql runs through all steps up to the TARGET version, it opens up the DowngradeSteps table and executes all roll-back steps contained therein, that exceed the TARGET. This way I can sync my source control to any point in the past and immediately deploy - the C# code is updated to the desired version and the SQL database is updated to the TARGET version, so I enjoy a consistent picture. It takes literally seconds to do.

The roll-forward and roll-back changes are always coded in pairs and are tested and code-reviewed before before being committed.

I have put this scheme in place 12 years ago and it's been serving me well *

* One caveat is that SQL Server sometimes throws a fit due to lame name binding rules (it binds names inside the IF block even when condition is false), so you have to enclose some of the steps into exec(), and you have to do this proactively(!). This sucks, but maybe your database doesn't do that.

Schema(or data in a database) in your release N should be compatible with release N-1, so you can revert your code to previous working version with new schema. That's the only way to ship new releases without stopping service for maintenance.