Backing up and restoring a database, schema, or table

You can use the web console or the db_backup and db_restore commands to perform different types of backups in your database.

Backup and restore functionality is available from both the web console and the command line. While the web console allows you to backup an entire database only, with the command line you can alternatively backup a single schema and restore a single schema or even a single table.

Important notes:

SAN is the recommended storage to take backups. Backups to local filesystem are not recommended, as there is only limited space available inside the container.
Do not attempt to delete a database-level backup. By default, the retention period is set to 91 days, and only the latest 16 backups are retained. The remaining backups are pruned automatically every 91 days.
Do not attempt to abort a restore when in progress.
Do not attempt to run apstop when a backup or a restore is in progress.
Perform offline backup or any restore only during a scheduled maintenance window because the system will be down during these operations.

Types of database backups

A database backup can be of one of the following types:

Full offline backup: The database is offline and inaccessible to applications during backup.
Full online backup: The database is online and accessible to applications during backup. Changes saved in the database during the backup are included in this backup.
Incremental online backup: This type of backup captures new changes that were made since the last available online backup. The database is online during backup and accessible to applications.; An incremental backup image is a copy of all database data that has changed since the most recent, successful, full online backup operation. This is also known as a cumulative backup image, because a series of incremental backups taken over time will each have the contents of the previous incremental backup image. The predecessor of an incremental backup image is always the most recent successful full backup of the same object.

Note: Online and offline backups are independent of each other. An incremental online backup updates the last online backup and ignores any offline backups.

Types of schema backups

A schema backup can be of one of the following types:

Full online backup: The schema is online and accessible to applications during backup. Changes saved in the schema during the backup are not included in this backup.
Incremental online backup: This type of backup captures new changes that were made since the last available online backup. The database is online during backup and accessible to applications.; An incremental backup image is a copy of all schema data that has changed since the most recent, successful, full online backup operation. This is also known as a cumulative backup image, because a series of incremental backups taken over time will each have the contents of the previous incremental backup image. The predecessor of an incremental backup image is always the most recent successful full backup of the same object.; Only supported for schemas enabled for row modification tracking. For more information, refer to Incremental schema backup and restore.
Delta incremental online backup: A delta incremental online backup, also known as a differential backup. A delta backup image is a copy of all schema data that has changed since the last successful backup. This could be a full, cumulative or a differential backup.; Only supported for schemas enabled for row modification tracking. For more information, refer to Incremental schema backup and restore.

Database backups versus schema backups

You can back up an entire database or a single database schema to a local file system, SAN, or NAS. There is no limit to the number of backups that can be created. From a database backup, you can restore the entire database only. From a schema backup, you can restore one table or all tables from the schema. Backups include the source table information required to create the table on the target.

Note: Database backups and schema backups are incompatible. For example, you cannot restore a single schema or table from a database backup.

The following restrictions apply to schema backup and restore:

Note: Some of these restrictions are for schemas not enabled for row modification tracking (RMT). For restrictions to schemas enabled for RMT, refer to Limitations for schema backup of schemas enabled for row modification.

Schema backup and restore are not available on the web console.
Schema backup is available only as a full online backup (-type onl).
Schema and table restore is available only as a restore from an online schema backup image (-type frh).
Schema backup does not provide the option to backup a subset of tables.
Schema backup does not backup tables with names starting with SYS.
Schema backup is not possible for a schema that contains a table with more than one spatial column.
Schema backup is not possible for a schema that contains a table that contains both spatial and large-object (LOB) data.
Schema backup is not possible for a schema that contain a delimiter such as a period (.) in its name.

Before a schema restore, all existing schema objects are first dropped, then re-created from the backup.

Before schema/table restore:

If the schema/table(s) already exists and you specify that existing schema/table(s) are to be dropped, the schema/table(s) are first dropped, then recreated from the backup.
If the schema/table(s) already exists and you did not specify that existing schema/table(s) are to be dropped, error message indicating that the table already exists is displayed.
If the schema/table(s) does not exist, the restore operation proceeds regardless of whether you specify that existing tables are to be dropped.