Teradata: How to back up a table that uses an identity column?

Question

In Teradata, the way I've been doing backups for tables is like this:

create table xxx_bak as xxx with data

Works great, but I have just discovered that this doesn't work for tables with identity columns.

I need a backup method that can duplicate a table with its data intact so that I can roll it back in case I mess up some data.

Answer 1

If you just want a copy of the table, you can create one with the same structure but without making the key column an identity column. You can then insert into it from the original table. However, you wouuldn't be able to insert back into the old table from the backup while retaining the same keys.

The way to make a backup that you can later restore with the same keys is to use the archive/restore tool ARCMAIN.

Backup like this:

logon my_server/my_user, my_password;
archive data tables (my_database.my_table), release lock, file=backup_file;

Restore like this:

logon my_server/my_user, my_password;
restore data tables (my_database.my_table), release lock, file=backup_file;