If the execution of a transfer statement fails, the migration script generates statements to bring back the table in a usable state. Note that before the execution of the data transfer statement, the table containing the data will be renamed to tmp_ms_sql_server_<former table name>. In addition, a new, empty table with the original name will be created. Also, the primary key of the table containing the data will be renamed to tmp_ms_sql_server_<former pk name> and the original name will be used for the primary key for the newly created table. The undo statements will drop the new table with the original name and undo the rename actions on the original table and the original primary key.

The undo script is stored in the undo_statements table. The transfer_id field is the foreign key field referring to the id of the data_transfer statement. If running the undo script fails, the exec_error flag in the undo_statements table will be set. The error message and the error number will be logged in the undo_statement_errors table.

The undo_statement_errors table has an insert trigger trg_undo_statement_errors_ins that sets the exec_error flag of the failed statement in the undo_statements table. Also, the undo_error flag will be set in the migrate_tables table.

Isah provides the all_undo_statements view to enhance retrieving all data of the statements belonging to a single table or to an entire migration run. Since undo statements are always run in the single-user mode, you only need to order on the primary key id. Example:

select *

from ANSI_Migration.dbo.all_undo_statements U

where U.run_id = 6

and U.table_name = N'[dbo].[T_Action]'

order by U.id;