Wednesday, February 8, 2017

ARCMAIN Scripts for BACKUP , COPY and RESTORE - BAR


Note  : Backup file name should be less than 7 characters ( Eg : FILE = 1234567) 

=======================================================
Complete FULL Backup for DBC ( Including all DB's under DBC hierarchy 
========================================================

.LOGON 127.0.0.1/dbc,dbc;
ARCHIVE data tables(DBC) ALL,
RELEASE LOCK,
FILE=DBC;
LOGOFF;

On Command Prompt  :          arcmain < dbc.txt  > dbc.log

=======================================================
Single Database  / User Backup 
=======================================================

.logon 10.65.40.139/dbc,dbc;
archive data tables (lkp85src) ALL,
release lock,
file=LKP85SR;  
.logoff;


=======================================================
By using COPY command in ARCMAIN script
======================================================= 

.logon 10.65.40.139/dbc,dbc;
copy data tables (lkp95src)(FROM(lkp85src)), 
release lock,
file= LKP85SR;  
.logoff;


=========================================================
By using RESTORE command in ARCMAIN script
=========================================================

.logon 10.65.40.139/dbc,dbc;
Restore data tables (zs91src),
release lock,
file= ZS91SR;  
.logoff;


=========================================================
Single TABLE backup
=========================================================

This is the archival code which works for a single table only.

.LOGON 10.65.40.139/dbc,dbc;
ARCHIVE DATA TABLEs (DBname.TableName) ,
RELEASE LOCK,
FILE=SIN_TAB;
LOGOFF;

=========================================================
Multiple Table backup 
========================================================= 

When I want to archive more than 1 tables from the database I typed the following code:

.LOGON 172.16.134.3/dbc ,dbc;
ARCHIVE DATA TABLES 
(DBname.TableName1), 
(DBname.TableName2) ,
RELEASE LOCK,
FILE=TABLES;
LOGOFF;

============================================================
By using EXCLUDE command for  Tables 
============================================================

ARCHIVE DATA TABLEs (DBname) EXCLUDE TABLES (DBname.TableName3,DBname.TableName4) ,
RELEASE LOCK,
FILE=check;

===========================================================
Table wise Backup 
===========================================================

LOGON 172.16.134.3/dbc ,dbc;
ARCHIVE DATA TABLES
(Database1.Account_Table),(Database1.Customer_Table),(Database1.Transaction_Table)
,RELEASE LOCK
,INDEXES
,ABORT
,FILE=TABLES;
LOGOFF;


================================================================
While using restore (copy),we need  to  provide the databasename.tablename all of them
================================================================

.LOGON 127.0.0.1/dbc,dbc;
COPY data tables (databasename.tablename) ( from (databasename.tablename),
RELEASE LOCK,
FILE=tables;
LOGOFF;


===============================================================
Partition Backup and copy
===============================================================

for Archive:

     .logon TDPID/username,passwd;
     ARCHIVE DATA TABLES
     (Students.orders_PPI_M)
     (PARTITIONS WHERE (! o_orderdate = DATE '2000-01-25' !)),
     RELEASE LOCK,
     FILE=ARCHIVE;
     .logoff;

For Copy:

     .logon TDPID/username,passwd;
     COPY DATA TABLES
     (Students.orders_rst )   (from(Students.orders_PPI_M )),
     RELEASE LOCK,
     FILE=ARCHIVE;
     .LOGOFF;

14 comments:

  1. Backup file name can be upto 8 characters. If I want to archive only dbc tables, not anyother databases. how can I do that?

    ReplyDelete

  2. .logon 10.65.40.139/dbc,dbc;
    archive data tables (DBC),
    release lock,
    file=DBC;
    .logoff;

    ReplyDelete
  3. Backup file name can be upto 8 characters. Yes Correct . Just now i checked from manual.Earlier it was 7 .

    ReplyDelete
  4. can you help me with COPY DATA TABLES with EXCLUDE TABLES option

    ReplyDelete
  5. .LOGON hostname/dbc,password;
    COPY data tables (mmqa_link) ( from (mmqa_link)),
    EXCLUDE (TD_SYSFNLIB),
    RELEASE LOCK,
    FILE=DBC;
    LOGOFF;

    ReplyDelete
  6. .LOGON lnx2080/dbc,dbc;
    COPY data tables (ldm_source) (from (ldm_source),
    EXCLUDE TABLES (TABLE_UDT,TABLE_Unique,employee_mngr,parent_1,Table_With_TableLevelConstraint,distance),
    WITH JOURNAL TABLE = (ldm_source.tabl_jrnl)),
    RELEASE LOCK,
    FILE=LDM_SOU;
    LOGOFF;

    ReplyDelete
    Replies
    1. Thanks for the reply , Am trying to rename the database at the same time will my below script work
      COPY DATA TABLES
      (DB2) (FROM (DB1)), EXCLUDE (DB1.TB1,DB1.TB2,DB1.TB3),
      RELEASE LOCK,
      FILE=ARCHIVE;

      Delete
    2. Thanks Vasu , I was able to figure it out the below script worked
      COPY DATA TABLES
      (DB2) (FROM (DB1), EXCLUDE TABLES(DB1.TB1,DB1.TB2,DB1.TB3)),
      RELEASE LOCK,
      FILE=ARCHIVE;

      Delete
    3. This comment has been removed by the author.

      Delete
    4. archive data table (user) (exclude tables (opt_cost_table, temp_reconfigspace)),
      release lock,
      file=_DUMMY;

      Please use this script and let me know if you need any help

      Delete
  7. How do I archive and restore(copy) a view?

    Is it as simple and same syntax for a table archive and restore?

    ReplyDelete
  8. What is the difference between copy and restore here ?

    ReplyDelete
    Replies
    1. Copy vs. Restore

      The difference between copy and restore depends on the kind of operation being performed:

      • A restore operation moves data from archived files back to the same Teradata Database
      from which it was archived or moves data to a different Teradata Database so long as
      database DBC is already restored.

      • A copy operation moves data from an archived file to any existing Teradata Database and
      creates a new object if one does not already exist on that target database. (The target object
      does not have to exist, however, the database must already exist.)
      When selected partitions are copied, the table must exist and be a table that was previously
      copied as a full-table copy.

      Delete
  9. The copy table statement fails if the file is very big.
    What could I do?
    Thanks

    ReplyDelete