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.

A backup can be of one of the following types:
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 adds new changes to the last online backup available. 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.
Full offline backup
The database is offline and inaccessible to applications during backup.

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:

  • IBM recommends that you run daily, full online backups to SAN storage.
  • 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 backup or 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.

Database-level backups versus schema-level 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-level backup, you can restore the entire database only. From a schema-level 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 level backups and schema level backups are incompatible. For example, you cannot restore a single schema or table from a database-level backup.
The following restrictions apply to schema-level backup and restore:
  • Schema-level backup and restore are not available on the web console.
  • Schema-level backup is available only as a full online backup (-type onl).
  • Schema-level and table-level restore is available only as a restore from an online backup image (-type frh).
  • You can back up an entire database, or one schema, but not a table.
  • Schema-level backup is not possible for a schema that contains a table with more than one spatial column.
  • Schema-level backup is not possible for a schema that contains a table that contains both spatial and large-object (LOB) data.
  • Schema-level backup is not possible for a schema that contain a delimiter such as a period (.) in its name.

Before a schema-level 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.