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
schema.sql.
It is also possible to take the first …