Lets say there was release that went to production on Sunday and then some hours or some days later (e.g. Tuesday) we realized that we need to rollback the changes ...
Sorry to say it, but two days is a very long time to discover a problem serious enough to even consider rolling back.
we can not restore database from backup ... there is data from Sunday to lets say Tuesday.
Correct. If you roll back, you lose that data.
Can you ask your customers to "redo" everything they've done in the intervening two days?
Of course not. The only existing record of the work they did is the one in your database. You cannot blithely destroy that data by rolling back.
You might have to
- "Freeze" the Business,
- Create a duplicate database in which to hold the current data,
- Roll back the "real" database,
- Reverse engineer the data added between Sunday and Tuesday from the "new" structures into the "old" ones,
- Get the business to check that everything looks "right", then
- "Unfreeze" the business.
If you're very lucky, you might be able to skip steps #1 and #6, but you'll need to do a fair amount of analysis up front to work out whether that's the case or not. If not, the pressure on doing it quickly will ramp up astronomically.
#4 is the really worrying one.
That bit's never been done before (not against these two sets of data structures, anyway) and anything that's a "first" is a huge Risk and and a big "unknown" in terms of how long it might take.
Make sure you have antacids on hand. You're probably going to need them.