Core users can only take full backups. To use the other backup features, you need an enterprise license.

CockroachDB's BACKUP statement allows you to create full or incremental backups of your cluster's schema and data that are consistent as of a given timestamp.

You can backup a full cluster, which includes:

You can also back up:

Because CockroachDB is designed with high fault tolerance, these backups are designed primarily for disaster recovery (i.e., if your cluster loses a majority of its nodes) through RESTORE. Isolated issues (such as small-scale node outages) do not require any intervention.


BACKUP only offers table-level granularity; it does not support backing up subsets of a table.


To view the contents of an enterprise backup created with the BACKUP statement, use SHOW BACKUP.

Required privileges

Only members of the admin role can run BACKUP. By default, the root user belongs to the admin role.


BACKUP TABLE table_pattern , DATABASE database_name , TO destination AS OF SYSTEM TIME timestamp INCREMENTAL FROM full_backup_location , incremental_backup_location WITH backup_options_list OPTIONS ( backup_options_list )


Parameter Description
table_pattern The table(s) or view(s) you want to back up.
database_name The name of the database(s) you want to back up (i.e., create backups of all tables and views in the database).
destination The URL where you want to store the backup.

For information about this URL structure, see Backup File URLs.
timestamp Back up data as it existed as of timestamp. The timestamp must be more recent than your cluster's last garbage collection (which defaults to occur every 25 hours, but is configurable per table).
full_backup_location Create an incremental backup using the backup stored at the URL full_backup_location as its base. For information about this URL structure, see Backup File URLs.

Note: After a full backup for an explicit list of tables and/or databases, it is not possible to create an incremental backup if one or more tables were created, dropped, or truncated. In these cases, you must create a new full backup before more incremental backups can be created. To avoid this, backup the cluster instead of the explicit list of tables/databases.
incremental_backup_location Create an incremental backup that includes all backups listed at the provided URLs.

Lists of incremental backups must be sorted from oldest to newest. The newest incremental backup's timestamp must be within the table's garbage collection period.

For information about this URL structure, see Backup File URLs.

For more information about garbage collection, see Configure Replication Zones.
kv_option_list Control the backup behavior with a comma-separated list of these options.

The BACKUP statement cannot be used within a transaction.


Option Value Description
revision_history N/A Create a backup with full revision history, which records every change made to the cluster within the garbage collection period leading up to and including the given timestamp.
encryption_passphrase STRING The passphrase used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates. This same passphrase is needed to decrypt the file when it is used to restore and to list the contents of the backup when using SHOW BACKUP.
detached N/A New in v20.2: When a backup runs in detached mode, the backup job will execute asynchronously and the job ID will be returned immediately without waiting for the job to finish. Note that the job completion status will not be returned. To check on the job status, use the SHOW JOBS statement.
kms STRING New in v20.2: The key management service (KMS) URI (or a comma-separated list of URIs) used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates. This same KMS URI is needed to decrypt the file when it is used to restore and to list the contents of the backup when using SHOW BACKUP.

Currently, only AWS KMS is supported.

Backup file URLs

CockroachDB uses the URL provided to construct a secure API call to the service you specify. The URL structure depends on the type of file storage you are using. For more information, see the following:

Functional details

Object dependencies

Dependent objects must be backed up at the same time as the objects they depend on.

Object Depends On
Table with foreign key constraints The table it REFERENCES; however, this dependency can be removed during the restore.
Table with a sequence The sequence it uses; however, this dependency can be removed during the restore.
Views The tables used in the view's SELECT statement.
Interleaved tables The parent table in the interleaved hierarchy.

Users and privileges

The system.users table stores your users and their passwords. To restore your users and privilege grants, do a cluster backup and restore the cluster to a fresh cluster with no user data. You can also backup the system.users table, and then use this procedure.

Backup types

CockroachDB offers two types of backups: full and incremental.

Full backups

Full backups contain an un-replicated copy of your data and can always be used to restore your cluster. These files are roughly the size of your data and require greater resources to produce than incremental backups. You can take full backups as of a given timestamp and (optionally) include the available revision history.

Incremental backups

Incremental backups are smaller and faster to produce than full backups because they contain only the data that has changed since a base set of backups you specify (which must include one full backup, and can include many incremental backups). You can take incremental backups either as of a given timestamp or with full revision history.


Incremental backups can only be created within the garbage collection period of the base backup's most recent timestamp. This is because incremental backups are created by finding which data has been created or modified since the most recent timestamp in the base backup––that timestamp data, though, is deleted by the garbage collection process.

You can configure garbage collection periods using the ttlseconds replication zone setting.

For an example of an incremental backup, see the Create incremental backups example below.


The BACKUP process minimizes its impact to the cluster's performance by distributing work to all nodes. Each node backs up only a specific subset of the data it stores (those for which it serves writes; more details about this architectural concept forthcoming), with no two nodes backing up the same data.

For best performance, we also recommend always starting backups with a specific timestamp at least 10 seconds in the past. For example:


This improves performance by decreasing the likelihood that the BACKUP will be retried because it contends with other statements/transactions. However, because AS OF SYSTEM TIME returns historical data, your reads might be stale.

Viewing and controlling backups jobs

After CockroachDB successfully initiates a backup, it registers the backup as a job, and you can do the following:

Action SQL Statement
View the backup status SHOW JOBS
Pause the backup PAUSE JOB
Resume the backup RESUME JOB
Cancel the backup CANCEL JOB

You can also visit the Jobs page of the Admin UI to view job details. The BACKUP statement will return when the backup is finished or if it encounters an error.


The presence of a BACKUP-CHECKPOINT file in the backup destination usually means the backup is not complete. This file is created when a backup is initiated, and is replaced with a BACKUP file once the backup is finished.


Per our guidance in the Performance section, we recommend starting backups from a time at least 10 seconds in the past using AS OF SYSTEM TIME. Each example below follows this guidance.

Backup a cluster

To backup a full cluster:

'gs://acme-co-backup/test-cluster' \

Backup a database

To backup a single database:

TO 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \

To backup multiple databases:

> BACKUP DATABASE bank, employees \
TO 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \

Backup a table or view

To backup a single table or view:

> BACKUP bank.customers \
TO 'gs://acme-co-backup/bank-customers-2017-03-27-weekly' \

To backup multiple tables:

> BACKUP bank.customers, bank.accounts \
TO 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \

Create incremental backups

If you backup to a destination already containing a full backup, an incremental backup will be produced in a subdirectory with a date-based name (e.g., destination/day/time_1, destination/day/time_2):

'gs://acme-co-backup/test-cluster' \

This incremental backup syntax does not work for backups using HTTP storage; you must explicitly control where your incremental backups go by using the INCREMENTAL FROM syntax.

Run a backup asynchronously

New in v20.2: Use the detached option to execute the backup job asynchronously:

'gs://acme-co-backup/test-cluster' \
WITH detached;

The job ID is returned immediately without waiting for the job to finish:

(1 row)

Advanced examples

For examples of advanced BACKUP and RESTORE use cases, see:

See also

Yes No