Copy database and Restore and Archive with No Data option in Teradata

===========================================================

 ARC/RESTORE: - Copy database with No data

 ==========================================================

You can use following SQL directly to generate create table as commands for all the tables listed a database:

select 'create table '||'targetdb.'||tvmname||' as sourcedb.'||tvmname||' with no data;' as "ct command" from dbc.tvm where databaseid in (sel databaseid from dbc.dbase where databasename = 'sourcedb');

Simply redirect output to a file and use that file to run new script to create tables in target database.

Here is sample output where I want to copy all the tables from database sourcedb to database targetdb:

help database sourcedb;

Table/View/Macro name          Kind Comment

------------------------------ ---- ------------

     t1                             T    ?

     t2                             T    ?

     t3                             T    ?

select 'create table '||'targetdb.'||tvmname||' as sourcedb.'||tvmname||' with no data;' as "ct command" from dbc.tvm where databaseid in (sel databaseid from dbc.dbase where databasename = 'sourcedb');

     ct command

     ---------------------------------------------------------------

     create table targetdb.t2 as sourcedb.t2 with no data;

     create table targetdb.t1 as sourcedb.t1 with no data;

     create table targetdb.t3 as sourcedb.t3 with no data;

Second way :

You can use Teradata System Emulation Tool(TSET), if you have necessary access to use it.

OR the below as example:-

SELECT  'SHOW TABLE ' ||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||' ; FROM DBC.TABLES WHERE DATABASENAME IN ('INF_DWH_DB','DWH_DB','DWH_STAGING') AND TABLEKIND IN( 'T' ,'O') ORDER BY DATABASENAME;

Third Way :

COPY DATA TABLES (iastest2),

RELEASE LOCK,

FILE=textias;

This will create iastest2 on the target system - if you want to change the name of the target table, then use the FROM clause, e.g.

COPY DATA TABLES (iastest2-new),

(FROM (iastest2)),

RELEASE LOCK,

FILE=textias;

logon tcrm/user1,pal;

COPY DATA TABLES (iastest2),

RELEASE LOCK,

FILE=textias;

LOGOFF;

  

COPY DATA TABLE (tbase) (FROM (tbase) ,NO FALLBACK),

RELEASE LOCK,

FILE = tbase;

COPY DATA TABLE (tbase) -->> Destination Table

FROM (tbase) -->>Name of the table archived (source).

FILE = tbase -->>Name of the file used when archived.