2

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.

oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206

3 Answers3

2

After over a year and a half, I've finally found a slick solution to this issue:

create table mydb.mytablebackup as 
(select * from (select * from mydb.mytable) x) 
with data;

Be sure to qualify the innermost subquery or it won't work.

oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206
  • Caution. This will change the PI of the table to the first column, which might result in a skewed table and slow procesing. You should copy the PI & partitioning definition of the original table, too. Then it will be a fast AMP-local merge. – dnoeth Dec 06 '13 at 23:23
  • @dnoeth Good word. However, the new table will never actually be used, a single exception given to simply holding backup data. In the event that something ever goes wrong with the original (eg, you do a mass update on the wrong column, delete the wrong records, etc), you can just clear the original table and insert back into it all of the data from the backup table. Your point, however, is good to know in case someone were to ever delete the original table and rename the backup table to the original table name so that it actually becomes the new home for live data. – oscilatingcretin Dec 07 '13 at 00:09
  • Even if you don't use that copy it might still be much slower creating it due to spooling and redistribution. And you should never use this copy as a replacement of the original table as not only the indexes & identity is lost, also all NOT NULL and CHECK constraints, just compare the results of both SHOW TABLEs. – dnoeth Dec 07 '13 at 09:14
  • @dnoeth This is all about convenience and finding the quickest, most effortless method of creating a one-time table backup, so performance isn't really a concern. It also doesn't matter if constraints are not copied to the backup because the data will not be affected. If I need to copy the data from the backup back to the original table, the data will fit right back in place in its old home. Doing all of that SHOW TABLE stuff and adding more SQL to the backup script defeats the purpose of the convenience I am trying to achieve. – oscilatingcretin Dec 07 '13 at 16:02
  • However, as I said in a previous comment, if you're actually concerned about making an exact duplicate of the DDL, my method will indeed not work and you'd be more wise to get the source table's DDL, change the identity column to a basic integer, create the backup table with modified DDL, and insert * from source table into backup table. – oscilatingcretin Dec 07 '13 at 17:13
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;
lins314159
  • 2,510
  • 1
  • 16
  • 19
0

This involves 3 steps:

 1. SHOW TABLE orig_Table; (*Get the DDL*)

 2.  Replace orig_Table with bkp_Table name

 3.  INSERT INTO bkp_Table SELECT * FROM orig_Table;
Raj
  • 22,346
  • 14
  • 99
  • 142
  • What do I do in the event I have to roll back the table? Won't inserting into the backup table result in a new sequence of numbers for the identity column? If so, I can't do an update join because the primary keys will not match. – oscilatingcretin May 02 '12 at 12:04
  • In that case, for the backup table, do not use an identity column. Use Decimal. So the value from the identity column goes into a normal column with the same values – Raj May 05 '12 at 06:55