RESTORE

On this page

Warning:

As of November 18, 2022, CockroachDB v21.1 is no longer supported. For more details, refer to the Release Support Policy.

The RESTORE statement restores your cluster's schemas and data from a BACKUP stored on services such as AWS S3, Google Cloud Storage, or NFS.

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

You can restore:

A full cluster
Databases
Tables

Note:

The BACKUP ... TO and RESTORE ... FROM syntax is deprecated as of v22.1 and will be removed in a future release.

We recommend using the BACKUP ... INTO {collection} syntax, which creates or adds to a backup collection in your storage location. For restoring backups, we recommend using RESTORE FROM {backup} IN {collection} with {backup} being LATEST or a specific subdirectory.

For guidance on the syntax for backups and restores, see the BACKUP and RESTORE examples.

Considerations

RESTORE cannot restore backups made by newer versions of CockroachDB.
RESTORE is a blocking statement. To run a restore job asynchronously, use the DETACHED option. See Options for more usage detail.
RESTORE no longer requires an Enterprise license, regardless of the options passed to it or to the backup it is restoring.
You cannot restore a backup of a multi-region database into a single-region database.

Required privileges

Full cluster restores can only be run by members of the ADMIN role. By default, the root user belongs to the admin role.
For all other restores, the user must have write access (CREATE or INSERT) on all objects affected.
Zone configurations present on the destination cluster prior to a restore will be overwritten during a cluster restore with the zone configurations from the backed up cluster. If there were no customized zone configurations on the cluster when the backup was taken, then after the restore the destination cluster will use the zone configuration from the RANGE DEFAULT configuration.

Source privileges

The source file URL does not require the admin role in the following scenarios:

S3 and GS using SPECIFIED (and not IMPLICIT) credentials. Azure is always SPECIFIED by default.
Userfile

The source file URL does require the admin role in the following scenarios:

S3 or GS using IMPLICIT credentials
Use of a custom endpoint on S3
Nodelocal

We recommend using cloud storage for bulk operations.

Synopsis

Parameters

Parameter	Description
`table_pattern`	The table or view you want to restore.
`database_name`	The name of the database you want to restore (i.e., restore all tables and views in the database). You can restore an entire database only if you had backed up the entire database.
`destination`	The URL where the full backup (and appended incremental backups, if applicable) is stored. For information about this URL structure, see Backup File URLs.
`LATEST`	Restore the most recent backup in the given location. See the Restore from the most recent backup example.
`subdirectory`	Restore from a specific subdirectory in the given collection URI. See the Restore a specific backup example.
`partitioned_backup_location`	The URL where a locality-aware backup is stored. When restoring from an incremental locality-aware backup, you need to include every locality ever used, even if it was only used once. For information about this URL structure, see Backup File URLs.
`AS OF SYSTEM TIME timestamp`	Restore data as it existed as of `timestamp`. You can restore point-in-time data only if you had taken full or incremental backup with revision history.
`restore_options_list`	Control your backup's behavior with these options.

Options

You can control RESTORE behavior using any of the following in the restore_options_list. To set multiple RESTORE options, use a comma-separated list:

Option	Value	Description
`into_db`	Database name	Use to change the target database for table restores. The target database must exist before a restore with `into_db`. (Does not apply to database or cluster restores.) Example: `WITH into_db = 'newdb'`
`skip_missing_foreign_keys`	N/A	Use to remove the missing foreign key constraints before restoring. Example: `WITH skip_missing_foreign_keys`
`skip_missing_sequences`	N/A	Use to ignore sequence dependencies (i.e., the `DEFAULT` expression that uses the sequence). Example: `WITH skip_missing_sequences`
`skip_missing_sequence_owners`	N/A	Must be used when restoring either a table that was previously a sequence owner or a sequence that was previously owned by a table. Example: `WITH skip_missing_sequence_owners`
`skip_missing_views`	N/A	Use to skip restoring views that cannot be restored because their dependencies are not being restored at the same time. Example: `WITH skip_missing_views`
`encryption_passphrase`	Passphrase used to create the encrypted backup	The passphrase used to decrypt the file(s) that were encrypted by the `BACKUP` statement.
`DETACHED`	N/A	When `RESTORE` runs with `DETACHED`, the job will execute asynchronously. The job ID is returned after the restore job creation completes. Note that with `DETACHED` specified, further job information and the job completion status will not be returned. For more on the differences between the returned job data, see the example below. To check on the job status, use the `SHOW JOBS` statement. To run a restore within a transaction, use the `DETACHED` option.

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:

Use Cloud Storage for Bulk Operations

Note:

HTTP storage is not supported for BACKUP and RESTORE.
Use a Local File Server for Bulk Operations

Functional details

You can restore:

A full cluster
Databases
Tables

Full cluster

A full cluster restore can only be run on a target cluster with no user-created databases or tables. Restoring a full cluster includes:

All user tables
Relevant system tables
All databases
All tables (which automatically includes their indexes)
All views

RESTORE will only restore the latest data in an object (table, database, cluster), or the latest data as per an AS OF SYSTEM TIME restore. That is, a restore will not include historical data even if you ran your backup with revision_history. This means that if you issue an AS OF SYSTEM TIME query on a restored object, the query will fail or the response will be incorrect because there is no historical data to query. For example, if you restore a table at 2022-07-13 10:38:00, it is not then possible to read or back up that table at 2022-07-13 10:37:00 or earlier. This is also the case for backups with revision_history that might try to initiate a revision start time earlier than 2022-07-13 10:38:00.

Note:

When you restore a full cluster with an Enterprise license, it will restore the Enterprise license of the cluster you are restoring from. If you want to use a different license in the new cluster, make sure to update the license after the restore is complete.

Databases

The database cannot already exist in the target cluster. Restoring a database will create a new database and restore all of its tables and views. The created database will have the name of the database in the backup.

RESTORE DATABASE backup_database_name FROM 'subdirectory' IN 'your_backup_location';

Tip:

If dropping or renaming an existing database is not an option, you can use table restore to restore all tables into the existing database by using the WITH into_db option.

Tables

You can also restore individual tables (which automatically includes their indexes) or views from a backup. This process uses the data stored in the backup to create entirely new tables or views in the target database.

By default, tables and views are restored into a target database matching the name of the database from which they were backed up. If the target database does not exist, you must create it. You can choose to change the target database with the into_db option.

The target database must not have tables or views with the same name as the tables or views you're restoring. If any of the restore target's names are being used, you can:

DROP TABLE, DROP VIEW, or DROP SEQUENCE and then restore them. Note that a sequence cannot be dropped while it is being used in a column's DEFAULT expression, so those expressions must be dropped before the sequence is dropped, and recreated after the sequence is recreated. The setval function can be used to set the value of the sequence to what it was previously.
Restore the table or view into a different database.

Note:

RESTORE only offers table-level granularity; it does not support restoring subsets of a table.

When restoring an individual table that references a user-defined type (e.g., ENUM), CockroachDB will first check to see if the type already exists. The restore will attempt the following for each user-defined type within a table backup:

If there is not an existing type in the cluster with the same name, CockroachDB will create the user-defined type as it exists in the backup.
If there is an existing type in the cluster with the same name that is compatible with the type in the backup, CockroachDB will map the type in the backup to the type in the cluster.
If there is an existing type in the cluster with the same name but it is not compatible with the type in the backup, the restore will not succeed and you will be asked to resolve the naming conflict. You can do this by either dropping or renaming the existing user-defined type.

In general, two types are compatible if they are the same kind (e.g., an enum is only compatible with other enums). Additionally, enums are only compatible if they have the same ordered set of elements that have also been created in the same way. For example:

CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('yes', 'no') are compatible.
CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('no', 'yes') are not compatible.
CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('yes'); ALTER TYPE t2 ADD VALUE ('no') are not compatible because they were not created in the same way.

Object dependencies

Dependent objects must be restored 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.
Views	The tables used in the view's `SELECT` statement.
Interleaved tables	The parent table in the interleaved hierarchy.

Users and privileges

To restore your users and privilege grants, you can do a cluster backup and restore the cluster to a fresh cluster with no user data.

If you are not doing a full cluster restore, the table-level privileges need to be granted to the users after the restore is complete. (By default, the user restoring will become the owner of the restored objects.) To grant table-level privileges after a restore, backup the system.users table, restore users and their passwords, and then grant the table-level privileges.

Restore types

You can either restore from a full backup or from a full backup with incremental backups, based on the backup files you include:

Restore Type	Parameters
Full backup	Include the path to the full backup destination and the subdirectory of the backup. See the Examples section for syntax of cluster, database, and table restores.
Full backup + incremental backups	Include the path that contains the backup collection and the subdirectory containing the incremental backup. See Restore from incremental backups for an example.

Performance

The RESTORE process minimizes its impact to the cluster's performance by distributing work to all nodes. Subsets of the restored data (known as ranges) are evenly distributed among randomly selected nodes, with each range initially restored to only one node. Once the range is restored, the node begins replicating it others.

Note:

When a RESTORE fails or is canceled, partially restored data is properly cleaned up. This can have a minor, temporary impact on cluster performance.

Viewing and controlling restore jobs

After CockroachDB successfully initiates a restore, it registers the restore as a job, which you can view with SHOW JOBS.

After the restore has been initiated, you can control it with PAUSE JOB, RESUME JOB, and CANCEL JOB.

Note:

If initiated correctly, the statement returns when the restore is finished or if it encounters an error. In some cases, the restore can continue after an error has been returned (the error message will tell you that the restore has resumed in background).

Examples

The following examples make use of:

Amazon S3 connection strings. For guidance on connecting to other storage options or using other authentication parameters instead, read Use Cloud Storage for Bulk Operations.
The default AUTH=specified parameter. For guidance on using AUTH=implicit authentication with Amazon S3 buckets instead, read Use Cloud Storage for Bulk Operations — Authentication.

Note:

The examples in this section use the default AUTH=specified parameter. For more detail on how to use implicit authentication with Amazon S3 buckets, read Use Cloud Storage for Bulk Operations — Authentication.

View the backup subdirectories

BACKUP ... INTO adds a backup to a collection within the backup destination. The path to the backup is created using a date-based naming scheme. To view the backup paths in a given destination, use SHOW BACKUPS:

> SHOW BACKUPS IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

       path
-------------------------
/2021/12/14-190909.83
/2021/12/20-155249.37
/2021/12/21-142943.73
(3 rows)

When you want to restore a specific backup, add the backup's subdirectory path (e.g., /2021/12/21-142943.73) to the RESTORE statement. For details on viewing the most recent backup, see SHOW BACKUP LATEST.

Incremental backups will be appended to the full backup with BACKUP ... INTO LATEST IN {destination}. Your storage location will contain the incremental as a date-based subdirectory within the full backup.

In the following example /2021/12/21-142943.73 contains the full backup. The incremental backups (144748.08/ and 144639.97/) are appended as subdirectories to the full backup:

2021
|—— 12
   |—— 21-142943.73/
       |—— 20211221/
           |—— 144748.08/
           |—— 144639.97/

To output more detail about the backups contained within a directory, see View a list of the full and incremental backups in a specific full backup subdirectory.

See Incremental backups with explicitly specified destinations to control where your backups go.

Restore the most recent backup

New in v21.1.14: To restore from the most recent backup in the collection's location, use the LATEST syntax:

RESTORE FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

Restore a specific backup

To restore a specific backup, use the backup's subdirectory in the collection's location:

RESTORE FROM '2021/03/23-213101.37' IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories, use SHOW BACKUPS.

Restore a cluster

To restore a full cluster:

> RESTORE FROM LATEST IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories, use SHOW BACKUPS.

Restore a database

To restore a database:

> RESTORE DATABASE bank FROM LATEST IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories, use SHOW BACKUPS.

Note:

RESTORE DATABASE can only be used if the entire database was backed up.

Restore a table

To restore a single table:

> RESTORE TABLE bank.customers FROM LATEST IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To restore multiple tables:

> RESTORE TABLE bank.customers, bank.accounts FROM LATEST IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories, use SHOW BACKUPS.

Restore from incremental backups

To restore the most recent incremental backup from a location containing the full and incremental backup:

RESTORE DATABASE bank FROM LATEST IN 's3://{bucket_name}/{path/to/backup-collection}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

Note:

New in v21.1: RESTORE will re-validate indexes when incremental backups are created from an older version (v20.2.2 and earlier or v20.1.4 and earlier), but restored by a newer version (v21.1.0+). These earlier releases may have included incomplete data for indexes that were in the process of being created.

Restore a backup asynchronously

Use the DETACHED option to execute the restore job asynchronously:

RESTORE TABLE bank.customers FROM LATEST IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}' WITH DETACHED;

The job ID is returned after the restore job creation completes:

        job_id
----------------------
  592786066399264769
(1 row)

Without the DETACHED option, RESTORE will block the SQL connection until the job completes. Once finished, the job status and more detailed job data is returned:

job_id             |  status   | fraction_completed | rows | index_entries | bytes
-------------------+-----------+--------------------+------+---------------+--------
652471804772712449 | succeeded |                  1 |   50 |             0 |  4911
(1 row)

Other restore usages

Restore tables into a different database

By default, tables and views are restored to the database they originally belonged to. However, using the into_db option, you can control the target database. Note that the target database must exist prior to the restore.

First, create the new database that you'll restore the table or view into:

> CREATE DATABASE newdb;

Next, restore the table into the newly created database with into_db:

> RESTORE bank.customers \
FROM '2021/09/29-153014.47' IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}' \
WITH into_db = 'newdb';

Remove the foreign key before restore

By default, tables with foreign key constraints must be restored at the same time as the tables they reference. However, using the skip_missing_foreign_keys option you can remove the foreign key constraint from the table and then restore it.

> RESTORE bank.accounts \
FROM '2021/09/29-153014.47' IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}' \
WITH skip_missing_foreign_keys;

Restoring users from `system.users` backup

The system.users table stores your cluster's usernames and their hashed passwords. To restore them, you must restore the system.users table into a new database because you cannot drop the existing system.users table.

After it's restored into a new database, you can write the restored users table data to the cluster's existing system.users table.

First, create the new database that you'll restore the system.users table into:

> CREATE DATABASE newdb;

> RESTORE system.users \
FROM '2021/09/29-153014.47' IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}' \
WITH into_db = 'newdb';

After the restore completes, add the users to the existing system.users table:

> INSERT INTO system.users SELECT * FROM newdb.users;

> DROP TABLE newdb.users;

Cockroach
University

Docs Hub

RESTORE

Considerations

Required privileges

Source privileges

Synopsis

Parameters

Options

Backup file URLs

Functional details

Full cluster

Databases

Tables

Object dependencies

Users and privileges

Restore types

Performance

Viewing and controlling restore jobs

Examples

View the backup subdirectories

Restore the most recent backup

Restore a specific backup

Restore a cluster

Restore a database

Restore a table

Restore from incremental backups

Restore a backup asynchronously

Other restore usages

Restore tables into a different database

Remove the foreign key before restore

Restoring users from `system.users` backup

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

RESTORE

Considerations

Required privileges

Source privileges

Synopsis

Parameters

Options

Backup file URLs

Functional details

Full cluster

Databases

Tables

Object dependencies

Users and privileges

Restore types

Performance

Viewing and controlling restore jobs

Examples

View the backup subdirectories

Restore the most recent backup

Restore a specific backup

Restore a cluster

Restore a database

Restore a table

Restore from incremental backups

Restore a backup asynchronously

Other restore usages

Restore tables into a different database

Remove the foreign key before restore

Restoring users from system.users backup

See also

Cockroach
University

Cockroach
University

Restoring users from `system.users` backup