Backup and Restore Using Copydb
The copydb command creates two scripts:
copy.out
Contains query language statements to copy your tables to operating system files. The script contains a COPY statement for each table being copied.
You run the copy.out script (using the sql command) to copy tables out of the database.
copy.in
Contains query language statements to recreate your tables, views, and associated indexes, permissions, and integrities, and copy the table’s data from the operating system files into a database.
You run the copy.in script (using the sql command) to copy the tables into the same or another database.
You can run copydb without shutting down the database; however, copydb needs to acquire locks while reading system catalogs, so it will wait for any users that are running DDL concurrently to finish.
To back up tables with copydb
1. Use operating system commands to create a temporary working directory for the copy.in and copy.out scripts that will be created, and then move to this directory. For example:
Linux:
mkdir /tmp/mydir.backup
cd /tmp/mydir.backup
Windows:
mkdir D:\tmp\mydir.backup
D:
cd \tmp\mydir.backup
2. Back up all the tables and views that you own in the database by issuing the following command at the operating system prompt:
copydb dbname
To back up specified tables, issue this command:
copydb dbname tablename {tablename}
Copy.out and copy.in scripts are created.
3. Copy the data out of the database by running the copy.out script. Issue the following command at the operating system prompt:
sql dbname <copy.out
A copy of the objects copied from the database is created. Store these files on disk or tape.
To restore the data from a backup created by copydb
Run the copy.in script as follows:
sql dbname <copy.in
The tables are copied into the specified database.