Data needs to be safe at all times. — 📸 Photo by Fernando Lavin on Unsplash

Database Rollbacks in CI/CD: Strategies and Pitfalls

Database (schema) rollbacks after failed database deployments would be incredibly valuable and essential if we want quick fixes for failed deployments. But implementing them is notoriously challenging, sometimes impossible, and rarely automated in real-world systems. This blog post aims to highlight some options for establishing safety nets in our database deployments, while also explaining why this process is complex and what can go wrong.

I’m writing this article because you should expect database deployments to go wrong. Because when you expect them to go wrong, you can plan for it in advance. And one day, when things go wrong, you know how to solve it. If you didn’t plan for it, your options are minimal.

Why Database Rollbacks Are Tricky

Rolling back database changes is fundamentally different from rolling back application code, where, e.g. an old (stateless) JAR file can be replaced by a previous one. Database schema migrations often involve data transformations or schema changes that can be difficult to reverse deterministically, especially in production environments, where data state continually evolves. It’s somewhat easier when some downtime is not an issue, but we don’t want to work at night. Among the biggest challenges in rolling back database migrations are:

• Data loss: Rolling back can require restoring data from a backup, snapshot or restore point taken before the deployment. Any data that was added or modified since then will be lost. And, deleted data appears again.
• Stateful nature and data integrity: Databases maintain a persistent state. If we were to roll back only a particular part of a schema, e.g. the objects we changed during deployment, we risk harming the data integrity.

Possible Strategies for Database Rollbacks

To increase the likelihood of being able to roll back database changes, we can apply the following strategies. They all come with their own pros and cons listed. None of them can guarantee a rollback of your database after a failed deployment on their own without data loss.

1. Versioned Migrations with Reversible Scripts

The first possibility is to use database migration tools like Flyway or Liquibase (using one of these is recommended either way with or without CI/CD). With those, it is possible to design migrations so that every change has a corresponding rollback script. In the event of an error during the forward script, the rollback is applied. That’s what the marketing says…

For example, with the following forward script:

ALTER TABLE CUSTOMERS ADD (EMAIL VARCHAR2(100));

you can provide a rollback:

ALTER TABLE CUSTOMERS DROP COLUMN EMAIL;

However, this only works for non-destructive changes and doesn’t help if dropping the column means permanent data loss. This approach is also only recommended when your change sets are small and can be reduced to straightforward rollback scripts. If your change sets are larger, the undo script would roll back not only the failed part of your migration, but also the successful part. Another issue is that the rollback script itself may also fail, depending on the previous error that occurred. So you’re still in trouble. All in all, the effort that goes into reversible scripts usually isn’t worth it.

2. Backups before Changes

Before applying potential breaking changes, you can execute a full data export using Oracle Data Pump and create a snapshot of your data. If you use this command, replace the timestamp string with your desired export time. Without the flashback time, your export might not be consistent. *Thanks for pointing this out!*

expdp system/password@db schemas=my_schema \
  directory=backup_dir \
  dumpfile=myschema_backup.dmp \
  logfile=myschema_backup.log \
  flashback_time="TO_TIMESTAMP('2025-10-24 18:05:00', 'YYYY-MM-DD HH24:MI:SS')"

It’s also possible to use flashback_scn to reference the System Change Number of your database. You can get it with:

SELECT CURRENT_SCN FROM V$DATABASE;

Another way is to create a full backup with RMAN (regularly doing this is recommended, regardless of your approach to data protection during database schema migrations (if you care about your data)).

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

However, with both approaches, you will experience downtime during the restore, and all changes made (and data added) between the backup and the restore will be lost. Depending on the size of your database, the restore will take a varying amount of time.

3. Immutable Migrations and Forward Fixes

Instead of trying to automate rollbacks, schema migrations can be treated as immutable (forward-only). If a release fails, deploy a new migration that “undoes” or compensates for the failed migration.

When working with immutable migrations, the Expand-Contract Pattern can be a great way of adding larger changes in small steps. Also, it’s essential that you follow evolutionary database design principles.

This approach is the preferred one if you’re using a CI/CD pipeline and is safer in most cases. It requires the developers to keep their change sets very small as well (as they always should be), to limit the impact of a failed deployment. However, you should always use Strategy 3 in conjunction with Strategy 4, “Restore Points and Flashbacks”, to be extra safe. (And of course, Strategy 2. We need Backups.)

4. Restore Points and Flashbacks

Oracle offers robust flashback technology: database restore points. They allow you to freeze specific moments in time, e.g. before a deployment, to which you can later revert the entire database. This is extremely useful before risky deployments: if something goes wrong, you can flash back the database to exactly how it was at the restore point.

However, maintaining restore points for a long time comes with a trade-off: they prevent the database from discarding old undo information, which can fill up the underlying transaction log (redo log area). That’s why they are only suitable for small time windows. You should remove them as soon as you no longer need them. Also, to flash back to restore points requires exclusive access (downtime).

5. Pre-Production Test

*(This was added later, thanks to the input by Ludo!)*

Before you hit production with your schema migration, you must test it in a pre-production environment that is a clone of production. This validation environment gives you certainty that the deployment to production will go smoothly. Without this, testing migrations in containers or data-less databases in your pipeline before, you can’t have any confidence about your deployment success.

This environment can be a snapshot standby database or a read clone. Maybe you want to test your restore strategy regularly? This is your chance to do so. Making the pre-production environment a recent copy of the production by restoring from it. As you can see, there are many possibilities.

However, to do so, you need a pre-production environment with the same specifications as production. This might not always be the case.

Conclusion

Relying solely on reversible scripts for database rollbacks is rarely worth the tedious effort required to write them. While reversible migrations can offer an appealing safety net, in reality, that net is often fragile — prone to failure, incomplete coverage, and easily broken by complex, real-world data transformations.

No single rollback strategy is enough to protect your database (and business). Robust planning is your best defence: expect deployments to go wrong and prepare for disasters. Immutable migrations are a first step to being extra careful.

Always create pre-deployment restore points or exports, regardless of which migration strategy you use, especially for large databases — skipping these safeguards can lead to catastrophic, unrecoverable data loss and/or prolonged downtime. Rollbacks and restores may take hours or even days for large databases.

Also, make sure your (RMAN) backup is running regularly. If you can test your restore by setting up a pre-production environment for validation, you can gain a lot of confidence in your CI/CD pipeline.

References

[embed]Redgate Flyway - Automate database deployments across teams and technologies - Redgate Software From version control to continuous delivery, Redgate Flyway helps individuals, teams, and enterprises build on…www.red-gate.com

[embed]Liquibase: Database Change Management & CI/CD Automation | Database DevOps Automate database change management to code at full speed & continuously deliver with full confidence. Liquibase helps…www.liquibase.com

[embed]Utilities Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.docs.oracle.com

[embed]Expand and Contract - A Pattern to Apply Breaking Changes to Persistent Data with Zero Downtime The Expand and Contract pattern provides a way to implement breaking changes to a system in a safe manner.www.tim-wellhausen.de

[embed]Evolutionary Database Design Techniques to allow you to evolve the schema and contents of a production databasemartinfowler.com

[embed]Backup and Recovery User's Guide Use the BACKUP command to back up all or part of your database.docs.oracle.com