Rolling Back a Database Schema Change
When a database schema change goes wrong, decide quickly if it can be reversed. Learn how PostgreSQL, SQL Server, and MySQL handle DDL rollbacks.
On this page
Which Engines Can Roll Back DDL in a Transaction
For database administrators who have just deployed a schema change or need to know if an in-flight migration can be reversed; DDL transaction mechanics and reverse script generation are explained where they appear.
When an ALTER TABLE execution fails or causes immediate lock contention during a release, the first decision is whether your database engine can abort the migration inside an open transaction. In PostgreSQL 17 and 18, Data Definition Language (DDL) is transactional. BEGIN initiates a transaction block, so all statements after it execute in a single transaction until an explicit COMMIT or ROLLBACK is given, and apart from add or drop operations on a database or a tablespace, catalog operations are reversible, including table creation[1]. ALTER TABLE acquires an ACCESS EXCLUSIVE lock unless a subform is explicitly noted otherwise[2], and table-level locks are held until the end of the transaction[3], so aborting the transaction ends the blocking PostgreSQL schema migration.
Microsoft SQL Server (2019, 2022, and Azure SQL) also supports transactional DDL in most cases. BEGIN TRANSACTION marks the starting point of an explicit, local transaction, and all data modifications made after it can be rolled back to that known state of consistency with a ROLLBACK TRANSACTION statement[4], which is how the ALTER TABLE work of altering, adding, or dropping columns and constraints[5] is reverted under the default read-committed isolation level. One exception is worth knowing before you rely on it: because SQL Server does not version metadata, statements such as ALTER TABLE, CREATE INDEX, ALTER INDEX, and DROP INDEX are not permitted after a BEGIN TRANSACTION statement when the session runs under snapshot isolation[1].
| Engine | DDL Transaction Support | Behavior on ROLLBACK inside explicit transaction | What ends the blocking |
|---|---|---|---|
| PostgreSQL 14-18 | Full | Reverts all catalog changes, tables, indexes, and constraints | Table-level locks are held until the end of the transaction |
| Microsoft SQL Server 2016-2022 | Full (most statements, outside snapshot isolation) | Reverts table modifications, added columns, and constraints | Resources stay locked by the transaction until COMMIT or ROLLBACK completes it |
| MySQL 8.0 / 8.4 LTS | None (Implicit Commit) | Cannot roll back; DDL commits the active transaction prior to execution | Each DDL statement commits on its own |
| Oracle Database 19c / 23ai | None (Implicit Commit) | Cannot roll back; issues implicit COMMIT before and after DDL | Each DDL statement commits on its own |
Why MySQL Atomic DDL Cannot Be Rolled Back
MySQL 8.0 and MySQL 8.4 LTS implement atomic DDL, and currently only the InnoDB storage engine supports it. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation: it is either committed, with the changes persisted to all three, or rolled back, even if the server halts during the operation[6].
Atomic DDL is not transactional DDL. As the MySQL manual puts it, DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement, which means DDL statements cannot be performed within another transaction or within transaction control statements such as START TRANSACTION... COMMIT[6]. Once the statement finishes, it commits its own changes immediately into the data dictionary.
If you run the following block in MySQL 8.4 with InnoDB:
``sql START TRANSACTION; ALTER TABLE customer_orders ADD COLUMN processing_status VARCHAR(32) NOT NULL DEFAULT 'pending'; -- A failure occurs in a subsequent application step ROLLBACK; ``
The ROLLBACK command executes, but the processing_status column remains on the customer_orders table. Because the ALTER TABLE issued an implicit commit, the rollback only applies to statements executed after the DDL. Undoing a MySQL schema change requires generating and executing an explicit compensating reverse script.
Why a Dropped Column Does Not Come Back
A reverse migration script restores only schema definitions, never destroyed data records. When an ALTER TABLE DROP COLUMN or DROP TABLE command commits on any relational database engine, the database engine marks the space as free or immediately truncates the underlying data pages. Executing a reverse script that adds the column back creates a new, empty storage attribute populated with NULLs or a designated default value.
- Column deletions: Dropping a column erases all stored values across all existing rows; running ADD COLUMN afterwards leaves the column empty.
- Table deletions: Dropping a table deletes the catalog registration and data files; running CREATE TABLE creates an empty structure without previous records.
- Type narrowing: Converting a column from VARCHAR(255) to VARCHAR(50) or BIGINT to INT truncates or rejects out-of-range data; reversing the type definition does not restore truncated characters.
Before executing any forward migration script containing destructive DDL, you must evaluate whether the target data exists in backups or staging tables. If a destructive migration has committed and the data is lost, restoring the previous table shape via a reverse DDL script will allow the application code to query the schema again, but backfilling the missing values requires extracting records from an external snapshot.
Generating the Reverse Script from Two Model Versions
When you version your database schema design in Git using an offline model file, the previous structural baseline is stored directly in repository history PostgreSQL schema version control. The model file (.dbs) holds the complete database architecture, including tables, data types, indexes, and virtual relations, as plain XML.
Because the model is stored in a clean file format, generating a reverse migration script does not require manually authoring inverse SQL statements. A schema comparison tool reads two model states and generates the exact forward or backward synchronization DDL required to align them compare two database schemas safe migration script.
- Check out the previous working schema model file from your Git repository: git show HEAD~1:schema/production_model.dbs > schema/previous_model.dbs.
- Open the model file in your schema design tool and load your current live schema or the updated model file.
- From the Schema menu, choose Compare Model with Other Model From File and select previous_model.dbs as the comparison target.
- Review the visual schema diff showing missing columns, modified types, and deleted constraints.
- Click Generate Migration Script to produce the reverse DDL SQL statements.
Swapping the source and target files in the comparison tool inverts the operation direction. Where the forward deployment added columns and dropped an index, the generated reverse script drops those newly added columns and rebuilds the previous index definition.
A Worked Before-and-After of a Reversed Schema Change
Consider a forward migration designed to split customer names into distinct fields while removing an old composite identifier constraint on PostgreSQL 17.
The forward migration script applied to the database:
``sql -- Forward Migration: Deploying schema update v2.4 ALTER TABLE accounts ADD COLUMN first_name VARCHAR(100); ALTER TABLE accounts ADD COLUMN last_name VARCHAR(100); ALTER TABLE accounts DROP CONSTRAINT uq_account_composite_code; ALTER TABLE accounts ALTER COLUMN status TYPE VARCHAR(20); ``
When this deployment causes downstream query errors, the comparison against the previous model state produces the compensating reverse script by calculating the inverse diff:
``sql -- Reverse Migration: Rollback schema update v2.4 ALTER TABLE accounts ALTER COLUMN status TYPE VARCHAR(10); ALTER TABLE accounts ADD CONSTRAINT uq_account_composite_code UNIQUE (account_code, department_id); ALTER TABLE accounts DROP COLUMN first_name; ALTER TABLE accounts DROP COLUMN last_name; ``
The re-addition of the unique constraint (ADD CONSTRAINT uq_account_composite_code) will fail if the application inserted rows with duplicate (account_code, department_id) pairs while the forward migration was active. Similarly, reverting status to VARCHAR(10) fails if any row received a status value longer than 10 characters during the incident window. You must inspect the target table for conflicting rows before executing the constraint restoration statements.
The Common Failure When Reversing Multiple Changes
A frequent failure during emergency rollbacks occurs when a deployment alters multiple interdependent tables, but the remediation script reverses only a subset of the modified entities. For example, a release might rename a column on a parent table and update a foreign key definition on a child table in two separate steps.
If an engineer manually writes a rollback script that drops the new foreign key without reverting the parent column rename, the application code will fail with missing column errors upon reconnecting. Partial rollbacks leave the catalog in an untested hybrid state that matches neither the old code release nor the new one.
- Multi-table dependencies: Changing a referenced primary key and referencing foreign keys across several tables requires rolling back all dependent definitions in reverse topological order.
- Split-file migrations: If deployment steps were split across multiple migration files, compare the live database catalog directly against the target Git tag rather than reversing individual files by hand.
- State verification: Run a complete catalog diff against the target baseline model to confirm zero unresolved structural discrepancies remain across all application tables.
What to Verify Once the Reversal Has Run
Once the reverse migration script completes execution, you must run specific validation checks against the live instance before routing production application traffic back to the database.
- Catalog comparison: Perform a fresh schema comparison between the live database and your baseline model file to verify that tables, columns, data types, and default expressions match the intended commit.
- Constraint and index integrity: Check system views (such as pg_constraint and pg_indexes in PostgreSQL or information_schema.table_constraints in MySQL) to verify that all foreign keys, unique keys, and secondary indexes are active and valid.
- Connection pool statement cache: Restart or flush application connection pools (such as HikariCP or PgBouncer in transaction mode) to clear cached prepared statement plans that reference dropped or altered column positions.
- Migration tracking table: Update your schema migration ledger table to record the executed rollback or remove the failed forward version entry, ensuring future automated pipelines do not skip the corrected version.
To inspect your database schema visually, track design history in Git, and generate bidirectional migration scripts between model states, download DbSchema, connect to your database instance, and compare your live catalog against your versioned schema models.
Frequently asked questions
Can you roll back a schema migration in MySQL?
No. MySQL 8.0 supports atomic DDL, which ensures a single statement succeeds or fails completely, but it triggers an implicit commit. DDL statements cannot be performed within another transaction, so they cannot be rolled back if they alter the schema incorrectly.
Can you roll back an ALTER TABLE in PostgreSQL?
Yes. PostgreSQL supports transactional DDL. If you place an ALTER TABLE statement inside a BEGIN and ROLLBACK block, the database completely reverses the schema change.
Does reversing a schema change restore deleted data?
No. If your schema change dropped a column, dropped a table, or narrowed a column type, the data is physically destroyed. A reverse script restores the structural shape of the schema, but it does not bring back the data that was erased.
How do you generate a reverse script in DbSchema Pro Edition?
Because DbSchema saves the database design model as a plain XML file in Git, you can check out the previous model version. DbSchema Pro Edition compares the two schema versions and generates a migration script that reverses the changes.
Why did the rollback script run cleanly but the application still breaks?
This happens when a deployment contains multiple changes and only some of them were reversed. The application expects a specific schema state, and reversing only one part of the deploy leaves the database out of sync with the application code.
What should a database administrator verify after a rollback?
Check that the object list matches the previous model version, verify that dependent views and foreign keys are fully restored, ensure the connection pool has no stale prepared statements, and confirm the migration ledger records the reversal.
Sources
Generate the reverse script for your next rollback
DbSchema compares two versions of your schema model and generates the migration script to reverse a change, so the rollback is ready before you need it.
