Backups and restores with an external table

You can use external tables to back up a table. Although the nzbackup backup utility creates backups of an entire database, you can use the external table backup method to create a backup of a single table, with the ability to later restore it to the database.

To back up table data by using an external table, create external table definitions for each user table and then use SQL to insert into the external table. When you restore table data, create a table definition if it does not exist, and then use SQL to insert into the user table from the external table.

Examples of backing up and restoring by using an external table

The following examples show how to back up and restore the user table EMP by using an external table in compressed binary format:

The following command creates a definition of a binary compressed format external table that is called emp_backup for the table emp:
```
CREATE EXTERNAL TABLE emp_backup SAMEAS emp USING (
    DATAOBJECT ('/tmp/emp.bck')
    COMPRESS   true
    FORMAT     'internal');
```
The following command backs up the emp table data into the emp_backup table:
```
INSERT INTO emp_backup SELECT * FROM emp;
```
The following commands make sure that the emp table is empty and then restore the emp table from the emp_backup table:
```
TRUNCATE TABLE emp;
INSERT INTO emp SELECT * FROM emp_backup;
```