We are in the process of building a pipeline which is used for executing DDL & DML statements when migrated from one environment to another environment (eg: Dev to Test or Test to PROD). Biggest challenge that we face in the whole process is "How to rollback DDLs objects automatically incase of a failure?". Incase of DML, we have BT/ET, but the DDLs doesnt work that way !
Is there an automated way to perform the rollback of DDLs or please suggest better way to do this.
No there isn't an automated way of handling this.
You have to build the relevant commands (DROP, ALTER etc.) into your pipeline and execute them if a previous command failed.
To do that you'll have to think about the different type of DDL changes that may happen and for each type answer the question "How do I 'rollback' this change?"
Some obvious examples:
- add a secondary index. 'Rollback' is to drop the index.
- add a new column to a table. 'Rollback' is to drop the column.
An alternative for smaller tables is:
- backup the database containing the objects(s) to be changed
- run the DDL commands to make changes
- if any errors, restore the entire database
(this has an obvious downside if dealing with large tables).
Does that help?
Thanks Dave for the detailed explanation. We were also thinking of doing something very similar to what you suggested except for the backing up & replacing the whole database. I think adds too much of complexity to the whole rollback step.
Thanks again for your response. Appreciate that !! :)