FluentMigration Backup Table

Question

I have a Table which needs removal of a few columns.

TableA 
{
  ID int, 
  Name NVarChar(MAX) NOT NULL, 
  ToDelete1 int NOT NULL,
  ToDelete2 int NOT NULL,
}

I'm using FluentMigrator .NET (ActiveRecord Ruby On Rails like Migration). For the UP() migration it's a simple case of dropping the columns.

How do I create the down part of the migration?

My idea is to create a clone of the table during the UP() process and then copy the schema and data back to the Original during the Down() process.

What is the easiest way to achieve this?

EDIT: TableA.ID is referenced by other tables. So if we are deleting all rows from TableA and reinserting from TableA_Backup it has to be done while all foreign key constraints are turned off and with identity_insert turned on as far as I understand.

A code example would be great.

Answer 1

For anyone who is interested here is how I managed to get it to work.

1. Renamed the Original table with a _Backup prefix.
2. Copied items from that table to a new table schema with the Original name. With Identity Insert.
3. Removed foreign keys referring to old table.
4. Added foreign keys back to refer to new table.

Each of these steps are reversible and you can create the down migration.