As a data architect I always ensure that for any database schema
change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for every change. For example, if a change adds a new column or index to a table, a revert script would remove the respective column or index.
The goal is to always have a defensive position for any changes. The concept is that simple, it is not complex.
In its simplest form I use the following directory and file structure.
/schema schema.sql /patch YYYYMMDDXX.sql where XX,ZZ are sequential 2 digit numbers, e.g. 01,02 YYYYMMDDZZ.sql /revert YYYYMMDDXX.sql This is the same file name in the revert sub-directory. YYYYMMDDZZ.sql
At any commit or tag in configuration management it is possible
to create a current copy of the schema, i.e. use
It is also possible to take the first …