How can I safely roll back a database schema change made through Eclipse DTP after a failed deployment?

Question

When using Eclipse Data Tools Platform (DTP) to apply a schema change such as adding a column or modifying a table definition, the operation is typically executed as a plain SQL script. Because many relational databases do not support transactional DDL, a failure part‑way through the script can leave the database in a partially altered state, making a simple re‑run of the original script unsafe.
To achieve a reliable rollback, teams often need to keep a copy of the pre‑change schema, generate inverse DDL statements, and verify that applying those inverses restores the original objects without side effects. The challenge is to automate this workflow inside Eclipse while ensuring that the rollback can be tested repeatedly without affecting production data.
What is the recommended way to generate and store reversible DDL scripts within Eclipse DTP? How can I validate that a rollback script will return the database to a consistent, pre‑change state without manual inspection? Is there a method to integrate rollback verification into an Eclipse‑based build or continuous integration pipeline?

Zoha · Answer

Answer the Question First
When a schema change applied from Eclipse Data Tools Platform (DTP) fails, the only reliable rollback path is to restore the database to a known‑good state using a pre‑generated reverse‑migration script. DTP does not offer an “undo” button; all DDL statements are sent to the database immediately and, in most engines, cannot be rolled back with a standard SQL ROLLBACK.

Recommended Rollback Workflow

Have a baseline snapshot. Before you run any DDL, export the current schema (or take a full database backup). In DTP, right‑click the connection → Export Schema → save the DDL file.

Generate a reverse script. In the failed state, open the Schema Compare tool, compare the live database to the baseline snapshot. Check the Generate Synchronization Script option; DTP will produce a script that, when executed, will drop added objects, revert altered columns, and recreate dropped tables.

Validate the reverse script. Run the generated script in a separate test database that mirrors production (ideally a read‑only copy). Verify that:
No data were lost – tables that existed before the change still contain their rows.The schema matches the baseline DDL exactly (use DTP’s compare again).

Apply in production. After the test passes, run the reverse script against the production database. If your DBMS supports transactional DDL (e.g., PostgreSQL), wrap the script in a transaction for atomicity; otherwise, rely on the backup for recovery.

Automate in CI. Store the baseline DDL and the reverse script in source control. In your CI pipeline, add a step that:
Pulls the baseline snapshot.Runs the reverse script against a staging database.Runs a schema‑compare assertion to ensure the staging state equals the baseline.

Key Assumptions & Caveats

Baseline snapshot exists and is current.
The database engine’s DDL behavior is known (e.g., MySQL auto‑commits, PostgreSQL allows DDL in transactions).
Data that existed in the added column or modified table are not required; otherwise, a full data export/import may be needed before dropping.

Missing Diagnostic Detail
Could you confirm whether you have a recent full backup or a baseline DDL file available? This determines whether the reverse script can be executed safely or if a full restore is required.
Safe Commands (example for PostgreSQL)
BEGIN;
-- reverse script generated by DTP
COMMIT;

How can I safely roll back a database schema change made through Eclipse DTP after a failed deployment?

1 answer

Answer the Question First

Recommended Rollback Workflow

Key Assumptions & Caveats

Missing Diagnostic Detail

Safe Commands (example for PostgreSQL)

Your perspective belongs here.

1 question comment