Backing up and restoring a database, schema, or table

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

Important notes

IBM recommends that you run daily, full online backups to SAN storage. Backup to a local file system is not recommended, because the amount of space that is available inside the container is limited.
Db2® Warehouse retains only the two most recent backups.
Perform an offline backup operation or any type of restore operation only when the system is not running.
Do not attempt to stop the container (that is, do not run a docker stop command) while a backup or a restore operation is in progress
Do not attempt to take a snapshot of the database. For information, see Snapshot backup and restore on IBM Db2 Warehouse.
Do not attempt to delete a database-level backup.

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 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.

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 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.; 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:

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-level 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 is available only as a full online backup (-type onl).
Schema restore is available only as a full restore from an online 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, an empty spatial column, or both spatial and large-object (LOB) data.
Schema backup is not possible for a schema whose name contains a delimiter such as a period (.).

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

Before table restore:

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