How to Revert a Failed 2.x to 3.x Migrated Database

I started migrating Retain 2.x to Retain 3.x but it failed.  I would like to get the database back to it's original Retain 2.x state.  How do I do that?

Run one of the following queries (depending on the database management system employed) to drop the 3.x tables and update the database version information; then, the migration program can be used again to migrate the data.

MYSQL

Use the following query, which can be copied and pasted into the MySQL command prompt:

DROP TABLE IF EXISTS s_AddressBookCache;
DROP TABLE IF EXISTS s_Errors;
DROP TABLE IF EXISTS s_JobRunErrors;
DROP TABLE IF EXISTS s_JobRunIncrements;
DROP TABLE IF EXISTS s_JobRuns;
DROP TABLE IF EXISTS s_JobsCache;
DROP TABLE IF EXISTS s_MailboxRunErrors;
DROP TABLE IF EXISTS s_MailboxRunIncrements;
DROP TABLE IF EXISTS s_MailboxRuns;
DROP TABLE IF EXISTS s_ScheduledParameters;
DROP TABLE IF EXISTS s_ScheduledRecipients;
DROP TABLE IF EXISTS s_ScheduledReports;
DROP TABLE IF EXISTS s_ServerUsageMetrics;
DROP TABLE IF EXISTS s_ServerUsage;
DROP TABLE IF EXISTS s_Servers;
DROP TABLE IF EXISTS s_Severities;

DROP TABLE IF EXISTS t_device_properties;
DROP TABLE IF EXISTS t_devices;

DROP TABLE IF EXISTS t_message_properties;
DROP TABLE IF EXISTS t_message_recipients;
DROP TABLE IF EXISTS t_message_attachments;
DROP TABLE IF EXISTS t_message_tags;
DROP TABLE IF EXISTS t_tagdefs;

DROP TABLE IF EXISTS t_document;
DROP TABLE IF EXISTS legacy_ids;

DROP TABLE IF EXISTS t_recipient;
DROP TABLE IF EXISTS t_name;
DROP TABLE IF EXISTS t_value;
DROP TABLE IF EXISTS t_dsref;
DROP TABLE IF EXISTS t_dscnt;


UPDATE t_message set parent_id=null;
UPDATE t_message set folder_id=null;
DROP TABLE IF EXISTS t_message;

UPDATE t_folder set parent_id=null;
DROP TABLE IF EXISTS t_folder;

DROP TABLE IF EXISTS t_uuid_mapping;

DELETE FROM t_dbinfo where name='DBMigrateVer' and value='3';
UPDATE t_dbinfo set value='3' where name='DBSchemaVer' and value='4';

MS SQL

Use the following query:

DROP TABLE s_AddressBookCache;
DROP TABLE s_Errors;
DROP TABLE s_JobRunErrors;
DROP TABLE s_JobRunIncrements;
DROP TABLE s_JobRuns;
DROP TABLE s_JobsCache;
DROP TABLE s_MailboxRunErrors;
DROP TABLE s_MailboxRunIncrements;
DROP TABLE s_MailboxRuns;
DROP TABLE s_ScheduledParameters;
DROP TABLE s_ScheduledRecipients;
DROP TABLE s_ScheduledReports;
DROP TABLE s_ServerUsage;
DROP TABLE s_Servers;
DROP TABLE s_Severities;

DROP TABLE t_device_properties;
DROP TABLE t_devices;

DROP TABLE t_message_properties;
DROP TABLE t_message_recipients;
DROP TABLE t_message_attachments;
DROP TABLE t_message_tags;
DROP TABLE t_uuid_mapping;
DROP TABLE t_tagdefs;
DROP TABLE t_document;
DROP TABLE legacy_ids;

DROP TABLE t_recipient;
DROP TABLE t_name;
DROP TABLE t_value;
DROP TABLE t_dscnt;
DROP TABLE t_dsref;

UPDATE t_message set parent_id=null;
UPDATE t_message set folder_id=null;
DROP TABLE t_message;

UPDATE t_folder set parent_id=null;
DROP TABLE t_folder;

DELETE FROM t_dbinfo where name='DBMigrateVer' and value='3';
UPDATE t_dbinfo set value='3' where name='DBSchemaVer' and value='4';

Oracle

Use the following query:

DROP TABLE s_AddressBookCache;
DROP TABLE s_Errors;
DROP TABLE s_JobRunErrors;
DROP TABLE s_JobRunIncrements;
DROP TABLE s_JobRuns;
DROP TABLE s_JobsCache;
DROP TABLE s_MailboxRunErrors;
DROP TABLE s_MailboxRunIncrements;
DROP TABLE s_MailboxRuns;
DROP TABLE s_ScheduledParameters;
DROP TABLE s_ScheduledRecipients;
DROP TABLE s_ScheduledReports;
DROP TABLE s_ServerUsage;
DROP TABLE s_Servers;
DROP TABLE s_Severities;

DROP TABLE t_device_properties;
DROP TABLE t_devices;

DROP TABLE t_message_properties;
DROP TABLE t_message_recipients;
DROP TABLE t_message_attachments;
DROP TABLE t_message_tags;
DROP TABLE t_tagdefs;

DROP TABLE t_document;
DROP TABLE legacy_ids;

DROP TABLE t_recipient;
DROP TABLE t_name;
DROP TABLE t_value;
DROP TABLE t_dsref;
DROP TABLE t_dscnt;


UPDATE t_message set parent_id=null;
UPDATE t_message set folder_id=null;
DROP TABLE t_message;

UPDATE t_folder set parent_id=null;
DROP TABLE t_folder;

DROP TABLE t_uuid_mapping;

DELETE FROM t_dbinfo where name='DBMigrateVer' and value='3';
UPDATE t_dbinfo set value='3' where name='DBSchemaVer' and value='5';

This article was originally published in the GWAVA knowledgebase as article ID 2201.