> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# RESTORE

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `RESTORE` <InternalLink path="sql-statements">statement</InternalLink> restores your cluster's schemas and data from <InternalLink path="backup">a `BACKUP`</InternalLink> 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](#full-cluster)
* [Databases](#databases)
* [Tables](#tables)

For details on restoring across versions of CockroachDB, see <InternalLink path="restoring-backups-across-versions">Restoring Backups Across Versions</InternalLink>.

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 {collectionURI}` syntax, which creates or adds to a <InternalLink path="take-full-and-incremental-backups#backup-collections">backup collection</InternalLink> in your storage location. For restoring backups, we recommend using `RESTORE FROM {backup} IN {collectionURI}` with <code>{'{backup}'}</code> being <InternalLink path="restore#restore-the-most-recent-full-or-incremental-backup">`LATEST`</InternalLink> or a specific <InternalLink path="restore">subdirectory</InternalLink>.

For guidance on the syntax for backups and restores, see the <InternalLink path="backup#examples">`BACKUP`</InternalLink> and <InternalLink path="restore#examples">`RESTORE`</InternalLink> examples.

## Considerations

* `RESTORE` cannot restore backups made by newer versions of CockroachDB.
* `RESTORE` only supports backups taken on a cluster on a specific major version into a cluster that is on the same version or the next major version. Refer to the <InternalLink path="restoring-backups-across-versions">Restoring Backups Across Versions</InternalLink> page for more detail.
* `RESTORE` is a blocking statement. To run a restore job asynchronously, use the [`DETACHED`](#detached) option.
* `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.
* When the <InternalLink path="take-full-and-incremental-backups">`exclude_data_from_backup`</InternalLink> parameter is set on a table, the table will not contain row data when restored.
* During a <InternalLink path="restore#full-cluster">cluster restore</InternalLink>, any <InternalLink path="configure-replication-zones">zone configurations</InternalLink> present on the destination cluster are **overwritten** with the zone configurations from the <InternalLink path="backup#back-up-a-cluster">backed-up cluster</InternalLink>. If no customized zone configurations were on the cluster when the backup was taken, then after the restore the destination cluster will use the zone configuration from the <InternalLink path="configure-replication-zones#view-the-default-replication-zone">`RANGE DEFAULT` configuration</InternalLink>.

## Required privileges

<Note>
  Starting in v22.2, CockroachDB introduces a new restore privilege model that provides finer control over a user's privilege to restore backups.

  There is continued support for the [legacy privilege model](#required-privileges-using-the-legacy-privilege-model) in v22.2, however it **will be removed** in a future release of CockroachDB. We recommend implementing the new privilege model that follows in this section for all restores.
</Note>

You can grant the `RESTORE` privilege to a user or role depending on the type of restore required:

| Restore  | Privilege                                                                                                                                                    |
| -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Cluster  | Grant a user the system level `RESTORE` privilege. For example, `GRANT SYSTEM RESTORE TO user;`.                                                             |
| Database | Grant a user the system level `RESTORE` privilege to restore databases onto the cluster. For example, `GRANT SYSTEM RESTORE TO user;`.                       |
| Table    | Grant a user the database level `RESTORE` privilege to restore schema objects into the database. For example, `GRANT RESTORE ON DATABASE nonadmin TO user;`. |

The listed privileges do not cascade to objects lower in the schema tree. For example, if you are granted system-level restore privileges, this does not give you the privilege to restore a table. If you need the `RESTORE` privilege on a database to apply to all newly created tables in that database, use <InternalLink path="security-reference/authorization#default-privileges">`DEFAULT PRIVILEGES`</InternalLink>. You can add `RESTORE` to the user or role's default privileges with <InternalLink path="alter-default-privileges#grant-default-privileges-to-a-specific-role">`ALTER DEFAULT PRIVILEGES`</InternalLink>.

Members of the <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink> can run all three types of restore (cluster, database, and table) without the need to grant a specific `RESTORE` privilege. However, we recommend using the `RESTORE` privilege model to create users or roles and grant them `RESTORE` privileges as necessary for stronger access control.

### Privileges for managing a restore job

To manage a restore job with <InternalLink path="pause-job">`PAUSE JOB`</InternalLink>, <InternalLink path="resume-job">`RESUME JOB`</InternalLink>, or <InternalLink path="cancel-job">`CANCEL JOB`</InternalLink>, users must have at least one of the following:

* Be a member of the <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink>.
* The <InternalLink path="security-reference/authorization">`CONTROLJOB` role option</InternalLink>.

To view a restore job with <InternalLink path="show-jobs">`SHOW JOB`</InternalLink>, users must have at least one of the following:

* The <InternalLink path="security-reference/authorization#supported-privileges">`VIEWJOB` privilege</InternalLink>, which allows you to view all jobs (including `admin` -owned jobs).
* Be a member of the <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink>.
* The <InternalLink path="security-reference/authorization">`CONTROLJOB` role option</InternalLink>.

See <InternalLink path="grant">`GRANT`</InternalLink> for detail on granting privileges to a role or user.

## Required privileges using the legacy privilege model

The following details the existing privilege model that CockroachDB supports in v22.2 and earlier. Support for this privilege model will be removed in a future release of CockroachDB:

* [Full cluster restores](#full-cluster) can only be run by members of the <InternalLink path="security-reference/authorization#admin-role">`ADMIN` role</InternalLink>. By default, the `root` user belongs to the `admin` role.
* For all other restores, the user must have <InternalLink path="security-reference/authorization#managing-privileges">write access</InternalLink> ( `CREATE` or `INSERT` ) on all objects affected.

See the [Required privileges](#required-privileges) section for the updated privilege model.

## Source privileges

You can grant a user the `EXTERNALIOIMPLICITACCESS` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink> to interact with external resources that require implicit access.

Either the `EXTERNALIOIMPLICITACCESS` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink> or the <InternalLink path="security-reference/authorization#admin-role">`admin`</InternalLink> role is required for the following scenarios:

* To interact with a cloud storage resource using <InternalLink path="cloud-storage-authentication">`IMPLICIT` authentication</InternalLink>.
* Use of a [custom endpoint](https://docs.aws.amazon.com/sdk-for-go/api/aws/endpoints) on S3.
* <InternalLink path="cockroach-nodelocal-upload">Nodelocal</InternalLink>

No special privilege is required for:

* Interacting with an Amazon S3 and Google Cloud Storage resource using `SPECIFIED` credentials. Azure Storage is always `SPECIFIED` by default.
* Using <InternalLink path="use-userfile-storage">Userfile</InternalLink> storage.

We recommend using <InternalLink path="use-cloud-storage">cloud storage</InternalLink>. You also need to ensure that the permissions at your storage destination are configured for the operation. See <InternalLink path="use-cloud-storage#storage-permissions">Storage Permissions</InternalLink> for a list of the necessary permissions that each bulk operation requires.

<Danger>
  While Cockroach Labs actively tests Amazon S3, Google Cloud Storage, and Azure Storage, we **do not** test <InternalLink path="cloud-storage-authentication">S3-compatible services</InternalLink> (e.g., [MinIO](https://min.io/), [Red Hat Ceph](https://docs.ceph.com/en/pacific/radosgw/s3)).
</Danger>

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/kYb5CU8_1XGPxWhU/images/sql-diagrams/v24.1/restore.svg?fit=max&auto=format&n=kYb5CU8_1XGPxWhU&q=85&s=1e626e185c7826a8266050065f219e2f" alt="restore syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="559" height="595" data-path="images/sql-diagrams/v24.1/restore.svg" />

## Parameters

| Parameter                    | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `table\_pattern`             | The table, <InternalLink path="views">view</InternalLink>, or <InternalLink path="create-sequence">sequence</InternalLink> you want to restore. For details on how restore works with objects that are dependent on one another, refer to [Object dependencies](#object-dependencies).                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `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.                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `collectionURI`              | The <InternalLink path="take-full-and-incremental-backups#backup-collections">collection</InternalLink> URI where the <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> (and appended <InternalLink path="take-full-and-incremental-backups#incremental-backups">incremental backups</InternalLink>, if applicable) is stored.  For information about this URL structure, see [Backup File URLs](#backup-file-urls). |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `LATEST`                     | Restore the most recent backup in the given collection URI. See the [Restore from the most recent backup](#restore-the-most-recent-full-or-incremental-backup) example.                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| <a id="subdir-param" />      | `subdirectory`                                                                                                                                                                                                                                                                                                                                                                                                                                                    | Restore from a specific subdirectory in the given collection URI. See the [Restore a specific backup](#restore-a-specific-full-or-incremental-backup) example.                                                                                                                                                                                                                                                                                                                                                                                   |
| `localityURI`                | The URI where a <InternalLink path="take-and-restore-locality-aware-backups">locality-aware backup</InternalLink> 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](#backup-file-urls).                                                                                                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| <a id="as-of-system-time" /> | `AS OF SYSTEM TIME timestamp`                                                                                                                                                                                                                                                                                                                                                                                                                                     | Restore data as it existed as of <InternalLink path="as-of-system-time">`timestamp`</InternalLink>. You can restore point-in-time data if you had taken full or incremental backup <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">with revision history</InternalLink>. If the backup was not taken with `revision\_history`, you can use `SHOW BACKUP` to restore to a time that the backup covers (including in the full or incremental backup). See the [example](#restore-with-as-of-system-time). |
| `restore\_options\_list`     | Control your backup's behavior with [these options](#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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| --------------------------------- | --------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| <a id="detached" />               | `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 <InternalLink path="restore#restore-a-backup-asynchronously">example</InternalLink> below. To check on the job status, use the <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink> statement.  To run a restore within a <InternalLink path="transactions">transaction</InternalLink>, use the `DETACHED` option. |
| `encryption\_passphrase`          | Passphrase used to create the <InternalLink path="take-and-restore-encrypted-backups">encrypted backup</InternalLink> | The passphrase used to decrypt the file(s) that were encrypted by the <InternalLink path="take-and-restore-encrypted-backups">`BACKUP`</InternalLink> statement.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| <a id="execution_locality" />     | `EXECUTION LOCALITY`                                                                                                  | Key-value pairs                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | Restricts the execution of the restore to nodes that match the defined <InternalLink path="take-locality-restricted-backups">locality filter</InternalLink> requirements.  Example: `WITH EXECUTION LOCALITY = 'region=us-west-1a,cloud=aws'`                                                                                                                                                                                                                                                                                                                                                                                                          |
| `incremental\_location`           | <InternalLink path="string">`STRING`</InternalLink>                                                                   | Restore an incremental backup from the alternate collection URI the backup was originally taken with.  See [Restore incremental backups](#restore-a-specific-full-or-incremental-backup) for more detail.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `into\_db`                        | Database name                                                                                                         | Use to [change the destination database](#restore-tables-into-a-different-database) for table restores. The destination database must exist before a restore with `into\_db`. (Does not apply to database or cluster restores.)  Example: `WITH into\_db = 'newdb'`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `kms`                             | <InternalLink path="string">`STRING`</InternalLink>                                                                   | The URI of the cryptographic key stored in a key management service (KMS), or a comma-separated list of key URIs, used to <InternalLink path="take-and-restore-encrypted-backups#examples">take and restore encrypted backups</InternalLink>. Refer to <InternalLink path="take-and-restore-encrypted-backups#uri-formats">URI Formats</InternalLink> on the Encrypted Backups page. The key or keys are used to encrypt the manifest and data files that the `BACKUP` statement generates, decrypt them during a <InternalLink path="take-and-restore-encrypted-backups#examples">restore</InternalLink> operation, and list the contents of the backup when using <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>.  AWS KMS, Google Cloud KMS, and Azure Key Vault are supported. |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `new\_db\_name`                   | Database name                                                                                                         | [Rename a database during a restore](#rename-a-database-on-restore). The existing backed-up database can remain active while the same database is restored with a different name.  Example: `RESTORE DATABASE movr ... WITH new\_db\_name = 'new\_movr'`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `schema\_only`                    | N/A                                                                                                                   | Verify that a backup is valid by running `RESTORE ... schema\_only`, which will restore the backed-up schema without any user data. Refer to <InternalLink path="backup-validation#validate-a-backup-is-restorable">Backup Validation</InternalLink> for detail and an example.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `skip\_localities\_check`         | N/A                                                                                                                   | Use to skip checking localities of a cluster before a restore when there are mismatched <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink> between the backup's cluster and the destination cluster. For further details, refer to [Restoring to multi-region databases](#restoring-to-multi-region-databases).  Example: `WITH skip\_localities\_check`                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `skip\_missing\_foreign\_keys`    | N/A                                                                                                                   | Use to remove the missing <InternalLink path="foreign-key">foreign key</InternalLink> constraints before restoring.  Example: `WITH skip\_missing\_foreign\_keys`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `skip\_missing\_sequences`        | N/A                                                                                                                   | Use to ignore <InternalLink path="show-sequences">sequence</InternalLink> 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 <InternalLink path="create-sequence">sequence owner</InternalLink> or a sequence that was previously owned by a table.  Example: `WITH skip\_missing\_sequence\_owners`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `skip\_missing\_udfs`             | N/A                                                                                                                   | Must be used when restoring a table with referenced <InternalLink path="user-defined-functions">UDF</InternalLink> dependencies. Any column's `DEFAULT` expression using UDFs is dropped.  Example: `WITH skip\_missing\_udfs`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `skip\_missing\_views`            | N/A                                                                                                                   | Use to skip restoring <InternalLink path="views">views</InternalLink> that cannot be restored because their dependencies are not being restored at the same time.  Example: `WITH skip\_missing\_views`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `verify\_backup\_table\_data`     | N/A                                                                                                                   | Run a `schema\_only` restore **and** have the restore read all user data from external storage, verify checksums, and discard the user data before writing it to disk. You must also include the `schema\_only` option in the `RESTORE` statement with `verify\_backup\_table\_data`. For more detail, see <InternalLink path="backup-validation#validate-backup-table-data-is-restorable">Backup Validation</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |

### 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:

* <InternalLink path="use-cloud-storage">Use Cloud Storage</InternalLink>

<Note>
  HTTP storage is not supported for `BACKUP` and `RESTORE`.
</Note>

* <InternalLink path="use-a-local-file-server">Use a Local File Server</InternalLink>

You can create an external connection to represent an external storage or sink URI. This allows you to specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.

## Functional details

CockroachDB uses checksums to ensure data integrity during both the <InternalLink path="architecture/reads-and-writes-overview">write and read</InternalLink> processes. When data is <InternalLink path="architecture/storage-layer">written to disk</InternalLink>, CockroachDB calculates and stores checksums with the data. The checksums are then used to verify the integrity of the data when it is read back from disk. A restore job reads the data from the external backup storage and verifies the checksums to ensure that the data has not been corrupted during the storage or transfer of the backup.

You can restore:

* [A full cluster](#full-cluster)
* [Databases](#databases)
* [Tables](#tables)

`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](#as-of-system-time). That is, a restore will not include historical data even if you ran your backup with <InternalLink path="backup">`revision_history`</InternalLink>. 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 <InternalLink path="backup">back up</InternalLink> 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>
  You can exclude a table's row data from a backup using the <InternalLink path="take-full-and-incremental-backups">`exclude_data_from_backup`</InternalLink> parameter. With this parameter set, a table will be empty when restored.
</Note>

### Full cluster

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

* All user tables
* Relevant system tables
* All <InternalLink path="create-database">databases</InternalLink>
* All <InternalLink path="create-table">tables</InternalLink> (which automatically includes their <InternalLink path="indexes">indexes</InternalLink> )
* All <InternalLink path="views">views</InternalLink>

Also, consider that:

* <InternalLink path="temporary-tables">Temporary tables</InternalLink> will be restored to their original database during a full cluster restore.
* The restore will drop the cluster's `defaultdb` and `postgres` <InternalLink path="show-databases#preloaded-databases">pre-loaded databases</InternalLink> before the restore begins. You can only restore `defaultdb` and `postgres` if they are present in the original <InternalLink path="take-full-and-incremental-backups">backup</InternalLink>.
* <InternalLink path="change-data-capture-overview">Changefeed jobs</InternalLink> will not resume automatically on the new cluster. It is necessary to manually <InternalLink path="create-and-configure-changefeeds">create changefeeds</InternalLink> after a full-cluster restore.
* When the cluster is in a mixed-version state during an <InternalLink path="upgrade-cockroach-version">upgrade</InternalLink>, a full cluster restore will fail. To perform a full cluster restore, it is necessary to first <InternalLink path="upgrade-cockroach-version#step-3-decide-how-the-upgrade-will-be-finalized">finalize the upgrade</InternalLink>.

<Note>
  When you restore a full cluster with an Enterprise license, it will restore the <InternalLink path="licensing-faqs">Enterprise license</InternalLink> of the cluster you are restoring from. If you want to use a different license in the new cluster, make sure to <InternalLink path="licensing-faqs#set-a-license">update the license</InternalLink> **after** the restore is complete.
</Note>

### Databases

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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE backup_database_name FROM LATEST in 'your_backup_collection_URI';
```

To restore a database that already exists in a cluster, use the `new_db_name` option with `RESTORE` to provide a new name for the database. See the [Rename a database on restore](#rename-a-database-on-restore) example.

If <InternalLink path="drop-database">dropping</InternalLink> or <InternalLink path="alter-database#rename-to">renaming</InternalLink> an existing database is not an option, you can use [*table* restore](#restore-a-table) to restore all tables into the existing database by using the [`WITH into_db` option](#options).

### Tables

You can also restore individual tables (which automatically includes their indexes), <InternalLink path="views">views</InternalLink>, or <InternalLink path="create-sequence">sequences</InternalLink> from a backup. This process uses the data stored in the backup to create entirely new tables, views, and sequences in the destination database.

By default, tables, views, and sequences are restored into a destination database matching the name of the database from which they were backed up. If the destination database does not exist, you must <InternalLink path="create-database">create it</InternalLink>. You can choose to change the destination database with the [`into_db` option](#into_db).

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

* <InternalLink path="drop-table">`DROP TABLE`</InternalLink>, <InternalLink path="drop-view">`DROP VIEW`</InternalLink>, or <InternalLink path="drop-sequence">`DROP SEQUENCE`</InternalLink> 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` <InternalLink path="functions-and-operators#sequence-functions">function</InternalLink> can be used to set the value of the sequence to what it was previously.
* [Restore the table, view, or sequence into a different database](#into_db).

<Note>
  `RESTORE` only offers table-level granularity; it **does not** support restoring subsets of a table.
</Note>

When restoring an individual table that references a user-defined type (e.g., <InternalLink path="enum">`ENUM`</InternalLink>), 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 <InternalLink path="drop-type">dropping</InternalLink> or <InternalLink path="alter-type">renaming</InternalLink> 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. When you back up a table, it will not include any dependent tables, <InternalLink path="views">views</InternalLink>, or <InternalLink path="create-sequence">sequences</InternalLink>.

For example, if you back up <InternalLink path="views">view</InternalLink> `v` that depends on table `t`, it will only back up `v`, not `t`. When you try to restore `v`, the restore will fail because the referenced table is not present in the backup.

Alternatively, you can pass a `skip` option with `RESTORE` to skip the dependency instead:

| Dependent object                                                                   | Depends on                                        | Skip option                                                                |
| ---------------------------------------------------------------------------------- | ------------------------------------------------- | -------------------------------------------------------------------------- |
| Table with <InternalLink path="foreign-key">foreign key</InternalLink> constraints | The table it `REFERENCES`.                        | <InternalLink path="restore">`skip\_missing\_foreign\_keys`</InternalLink> |
| Table with a <InternalLink path="create-sequence">sequence</InternalLink>          | The sequence.                                     | <InternalLink path="restore">`skip\_missing\_sequences`</InternalLink>     |
| <InternalLink path="views">Views</InternalLink>                                    | The tables used in the view's `SELECT` statement. | <InternalLink path="restore">`skip\_missing\_views`</InternalLink>         |

We recommend treating tables with <InternalLink path="foreign-key">foreign keys</InternalLink>, which contribute to <InternalLink path="views">views</InternalLink>, or that use sequences or user-defined types as a single unit with their dependencies. While you can restore individual tables, you may find that backing up and restoring at the database level is more convenient.

<Note>
  Referenced UDFs are not restored and require the [`skip_missing_udfs`](#skip-missing-udfs) option.
</Note>

### Users and privileges

The owner of restored objects will be the user running the restore job. To restore your users and privilege <InternalLink path="grant">grants</InternalLink>, 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, <InternalLink path="restore#restoring-users-from-system-users-backup">restore users and their passwords</InternalLink>, and then <InternalLink path="grant">grant</InternalLink> 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](#view-the-backup-subdirectories) of the backup. See the [Examples](#examples) section for syntax of [cluster](#restore-a-cluster), [database](#restore-a-database), and [table](#restore-a-table) restores. |
| Full backup + incremental backups | Include the path that contains the backup collection and the [subdirectory](#view-the-backup-subdirectories) containing the incremental backup. See [Restore from incremental backups](#restore-a-specific-full-or-incremental-backup) for an example.                             |

<Note>
  CockroachDB does **not** support incremental-only restores.
</Note>

## 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.
* When a `RESTORE` fails or is canceled, partially restored data is properly cleaned up. This can have a minor, temporary impact on cluster performance.
* A restore job will pause if a node in the cluster runs out of disk space. See [Viewing and controlling restore jobs](#viewing-and-controlling-restore-jobs) for information on resuming and showing the progress of restore jobs. For instructions on how to free up disk space as quickly as possible after dropping a table, see <InternalLink path="operational-faqs">How can I free up disk space that was used by a dropped table?</InternalLink>
* A restore job will <InternalLink path="pause-job">pause</InternalLink> instead of entering a `failed` state if it continues to encounter transient errors once it has retried a maximum number of times. Once the restore has paused, you can either <InternalLink path="resume-job">resume</InternalLink> or <InternalLink path="cancel-job">cancel</InternalLink> it.

## Restoring to multi-region databases

Restoring to a <InternalLink path="multiregion-overview">multi-region database</InternalLink> is supported with some limitations. This section outlines details and settings that should be considered when restoring into multi-region databases:

* A <InternalLink path="multiregion-overview#cluster-regions">cluster's regions</InternalLink> will be checked before a restore. Mismatched regions between backup and restore clusters will be flagged before the restore begins, which allows for a decision between updating the <InternalLink path="cockroach-start#locality">cluster localities</InternalLink> or restoring with the [`skip_localities_check`](#skip-localities-check) option to continue with the restore regardless.

<Note>
  Restoring a multi-region cluster into a single-region CockroachDB Standard or CockroachDB Basic cluster is not supported. Standard and CockroachDB Basic clusters do **not** support the `skip_localities_check` option with `RESTORE`. To restore a multi-region cluster, you must <InternalLink version="cockroachcloud" path="basic-cluster-management#move-cluster-to-a-new-region">create a new multi-region Standard or CockroachDB Basic cluster</InternalLink> with regions that match the backed-up multi-region cluster.
</Note>

* A database that is restored with the `sql.defaults.primary_region` <InternalLink path="cluster-settings">cluster setting</InternalLink> will have the <InternalLink path="alter-database#set-primary-region">`PRIMARY REGION`</InternalLink> from this cluster setting assigned to the destination database.
* `RESTORE` supports restoring **non**-multi-region tables into a multi-region database and sets the table locality as <InternalLink path="table-localities#regional-tables">`REGIONAL BY TABLE`</InternalLink> to the primary region of the destination database.
* Restoring tables from multi-region databases with table localities set to <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink>, `REGIONAL BY TABLE`, <InternalLink path="alter-table">`REGIONAL BY TABLE IN PRIMARY REGION`</InternalLink>, and <InternalLink path="alter-table">`GLOBAL`</InternalLink> to another multi-region database is supported.
* When restoring a `REGIONAL BY TABLE IN PRIMARY REGION` table, if the primary region is different in the source database to the destination database this will be implicitly changed on restore.
* Restoring a <InternalLink path="partitioning">partition</InternalLink> of a `REGIONAL BY ROW` table is not supported.
* <InternalLink path="table-localities#regional-tables">`REGIONAL BY TABLE`</InternalLink> and <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink> tables can be restored **only** if the regions of the backed-up table match those of the destination database. All of the following must be true for `RESTORE` to be successful:
  * The <InternalLink path="multiregion-overview#database-regions">regions</InternalLink> of the source database and the regions of the destination database have the same set of regions.
  * The regions were added to each of the databases in the same order.
  * The databases have the same <InternalLink path="alter-database#set-primary-region">primary region</InternalLink>.

    The following example would be considered as having **mismatched** regions because the database regions were not added in the same order and the primary regions do not match.

    Running on the source database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database ADD region "us-west1";
    ```

    Running on the destination database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database ADD region "us-east1";
    ```

    In addition, the following scenario has mismatched regions between the databases since the regions were not added to the database in the same order.

    Running on the source database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database ADD region "us-west1";
    ```

    Running on the destination database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database ADD region "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-east1";
    ```

The ordering of regions and how region matching is determined is a known limitation.

For more on multi-region databases, see the <InternalLink path="multiregion-overview">Multi-Region Capabilities Overview</InternalLink>.

<Note>
  Use <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL SET {sessionvar} = {val}`</InternalLink> instead of the `sql.defaults.*` <InternalLink path="cluster-settings">cluster settings</InternalLink>. This allows you to set a default value for all users for any <InternalLink path="set-vars">session variable</InternalLink> that applies during login, making the `sql.defaults.*` cluster settings redundant.
</Note>

## Viewing and controlling restore jobs

After CockroachDB successfully initiates a restore, it registers the restore as a job, which you can view with <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink>.

After the restore has been initiated, you can control it with <InternalLink path="pause-job">`PAUSE JOB`</InternalLink>, <InternalLink path="resume-job">`RESUME JOB`</InternalLink>, and <InternalLink path="cancel-job">`CANCEL JOB`</InternalLink>.

<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).
</Note>

## Examples

There are two ways to specify a full or incremental backup to restore:

* [Restoring from the most recent backup](#restore-the-most-recent-full-or-incremental-backup)
* [Restoring from a specific backup](#restore-a-specific-full-or-incremental-backup)

<Tip>
  You can use external connections to represent an external storage or sink URI. This means that you can specify the external connection's name in statements rather than the provider-specific URI:

  For detail on using external connections, refer to the <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.
</Tip>

Some of the examples in this section use an <InternalLink path="create-external-connection">external connection</InternalLink> to represent the external storage URI. For guidance on connecting to cloud storage or using other authentication parameters, read <InternalLink path="use-cloud-storage#example-file-urls">Use Cloud Storage</InternalLink>.

If you need to limit the control specific users have over your storage buckets, refer to <InternalLink path="cloud-storage-authentication">Assume role authentication</InternalLink> for setup instructions.

### View the backup subdirectories

`BACKUP... INTO` adds a backup to a <InternalLink path="take-full-and-incremental-backups#backup-collections">backup collection</InternalLink> location. To view the backup paths in a given collection location, use <InternalLink path="show-backup">`SHOW BACKUPS`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUPS IN 's3://bucket/path?AUTH=implicit';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
       path
-------------------------
/2023/12/14-190909.83
/2023/12/20-155249.37
/2023/12/21-142943.73
(3 rows)
```

When you want to [restore a specific backup](#restore-a-specific-full-or-incremental-backup), add the backup's subdirectory path (e.g., `/2023/12/21-142943.73`) to the `RESTORE` statement. For details on viewing the most recent backup, see <InternalLink path="show-backup#show-the-most-recent-backup">`SHOW BACKUP FROM {subdirectory} in {collectionURI}`</InternalLink>.

### Restore the most recent full or incremental backup

To restore from the most recent backup (<InternalLink path="take-full-and-incremental-backups">full or incremental</InternalLink>) in the collection's location, use the `LATEST` syntax:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM LATEST IN 's3://bucket/path?AUTH=implicit';
```

If you are restoring an incremental backup, the storage location **must** contain a full backup.

When you restore from an incremental backup, you're restoring the **entire** table, database, or cluster. CockroachDB uses both the latest (or a <InternalLink path="restore#restore-a-specific-full-or-incremental-backup">specific</InternalLink>) incremental backup and the full backup during this process. You cannot restore an incremental backup without a full backup. Furthermore, it is not possible to restore over a <InternalLink path="restore#tables">table</InternalLink>, <InternalLink path="restore#databases">database</InternalLink>, or <InternalLink path="restore#full-cluster">cluster</InternalLink> with existing data. Refer to <InternalLink path="restore#restore-types">Restore types</InternalLink> for detail on the types of backups you can restore.

<Note>
  `RESTORE` will re-validate <InternalLink path="indexes">indexes</InternalLink> when <InternalLink path="take-full-and-incremental-backups">incremental backups</InternalLink> 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.
</Note>

### Restore a specific full or incremental backup

To restore a specific full or incremental backup, specify that backup's subdirectory in the `RESTORE` statement. To view the available subdirectories, use [`SHOW BACKUPS`](#view-the-backup-subdirectories). If you are restoring an incremental backup, the URI must point to the storage location that contains the full backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM '2023/03/23-213101.37' IN 's3://bucket/path?AUTH=implicit';
```

### Restore a cluster

To restore a full cluster:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM LATEST IN 'external://backup_s3';
```

To view the available subdirectories, use [`SHOW BACKUPS`](#view-the-backup-subdirectories).

### Restore a database

To restore a database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE bank FROM LATEST IN 'external://backup_s3';
```

To view the available subdirectories, use [`SHOW BACKUPS`](#view-the-backup-subdirectories).

<Note>
  `RESTORE DATABASE` can only be used if the entire database was backed up.
</Note>

### Restore a table

To restore a single table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE TABLE bank.customers FROM LATEST IN 'external://backup_s3';
```

To restore multiple tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE TABLE bank.customers, bank.accounts FROM LATEST IN 'external://backup_s3';
```

To view the available subdirectories, use [`SHOW BACKUPS`](#view-the-backup-subdirectories).

### Restore with `AS OF SYSTEM TIME`

<Danger>
  `RESTORE` will only restore the latest data as per an `AS OF SYSTEM TIME` restore. The restore will not include historical data even if you ran your backup with `revision_history`.
</Danger>

Running a backup with <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">revision history</InternalLink> captures every change made within the garbage collection period leading up to and including the given timestamp, which allows you to restore to an arbitrary point-in-time within the revision history.

If you ran a backup **without** `revision_history`, it is still possible to use `AS OF SYSTEM TIME` with `RESTORE` to target a particular time for the restore. However, your restore will be limited to the times of the full backup and each incremental backup in the chain. In this case, use the following example to restore to a particular time.

First, find the times that are available for a point-in-time-restore by listing the available backup directories in your storage location:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUPS IN 'external://backup_s3';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
          path
------------------------
  2023/01/18-141753.98
  2023/01/23-184816.10
  2023/01/23-185448.11
(3 rows)
```

From the output use the required date directory and run the following to get the details of the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP '2023/01/23-185448.11' IN 'external://backup_s3';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | parent_schema_name |        object_name         | object_type | backup_type |         start_time         |          end_time          | size_bytes | rows | is_full_cluster
----------------+--------------------+----------------------------+-------------+-------------+----------------------------+----------------------------+------------+------+------------------
  movr          | public             | vehicle_location_histories | table       | full        | NULL                       | 2023-01-23 18:54:48.116975 |      85430 | 1092 |        t
  movr          | public             | promo_codes                | table       | full        | NULL                       | 2023-01-23 18:54:48.116975 |     225775 | 1003 |        t
  movr          | public             | user_promo_codes           | table       | full        | NULL                       | 2023-01-23 18:54:48.116975 |       1009 |   11 |        t
  NULL          | NULL               | system                     | database    | incremental | 2023-01-23 18:54:48.116975 | 2023-01-24 00:00:00        |       NULL | NULL |        t
  system        | public             | users                      | table       | incremental | 2023-01-23 18:54:48.116975 | 2023-01-24 00:00:00        |          0 |    0 |        t
  system        | public             | zones                      | table       | incremental | 2023-01-23 18:54:48.116975 | 2023-01-24 00:00:00        |          0 |    0 |
```

Finally, use the `start_time` and `end_time` detail to define the required time as part of the `AS OF SYSTEM TIME` clause. Run the restore, passing the directory and the timestamp:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE movr FROM '2023/01/23-185448.11' IN 'external://backup_s3' AS OF SYSTEM TIME '2023-01-23 18:56:48';
```

### Restore a backup asynchronously

Use the [`DETACHED`](#detached) option to execute the restore <InternalLink path="show-jobs">job</InternalLink> asynchronously:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESTORE FROM LATEST IN 'external://backup_s3'
WITH DETACHED;
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        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:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#into_db), you can control the destination database. Note that the destination database must exist prior to the restore.

1. Create the new database that you'll restore the table or view into:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > CREATE DATABASE newdb;
   ```
2. Restore the table into the newly created database with `into_db`:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > RESTORE bank.customers FROM LATEST IN 'external://backup_s3'
   WITH into_db = 'newdb';
   ```

#### Rename a database on restore

To rename a database on restore, use the [`new_db_name`](#new-db-name) option:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE bank FROM LATEST IN 'external://backup_s3'
WITH new_db_name = 'new_bank';
```

When you run `RESTORE` with `new_db_name`, the existing database that was originally backed up can remain active:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
database_name
--------------+
defaultdb
bank
new_bank
postgres
system
```

#### Remove the foreign key before restore

By default, tables with <InternalLink path="foreign-key">foreign key</InternalLink> constraints must be restored at the same time as the tables they reference. However, using the <InternalLink path="restore">`skip_missing_foreign_keys`</InternalLink> option you can remove the foreign key constraint from the table and then restore it.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESTORE bank.accounts FROM LATEST IN 'external://backup_s3'
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.

1. Create the new database that you'll restore the `system.users` table into:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > CREATE DATABASE newdb;
   ```
2. Restore the `system.users` table into the new database:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > RESTORE system.users  FROM LATEST IN 'external://backup_s3'
   WITH into_db = 'newdb';
   ```
3. After the restore completes, add the `users` to the existing `system.users` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > INSERT INTO system.users SELECT * FROM newdb.users;
   ```
4. Remove the temporary `users` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > DROP TABLE newdb.users;
   ```

#### Restore from incremental backups in a different location

<Danger>
  **Removed in v26.2**: The `incremental_location` option has been removed in v26.2, following its prior <InternalLink version="releases" path="v25.4">deprecation</InternalLink>. Existing backups taken with this option cannot be restored in v26.2 or later. If you have backups that use `incremental_location`, you can only restore them using a cluster running v26.1 or earlier.
</Danger>

To restore an incremental backup that was taken using the <InternalLink path="backup">`incremental_location` option</InternalLink>, you must run the `RESTORE` statement with both:

* the collection URI of the full backup
* the `incremental_location` option referencing the incremental backup's collection URI, as passed in the original `BACKUP` statement

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE TABLE movr.users FROM LATEST IN 'external://backup_s3' WITH incremental_location = '{incremental_backup_URI}';
```

For more detail on using this option with `BACKUP`, see <InternalLink path="take-full-and-incremental-backups#incremental-backups-with-explicitly-specified-destinations">Incremental backups with explicitly specified destinations</InternalLink>.

## Known limitations

* `RESTORE` will not restore a table that references a <InternalLink path="user-defined-functions">UDF</InternalLink>, unless you skip restoring the function with the [`skip_missing_udfs`](#skip-missing-udfs) option. Alternatively, take a <InternalLink path="backup#back-up-a-database">database-level backup</InternalLink> to include everything needed to restore the table.
* Restoring <InternalLink path="table-localities#global-tables">`GLOBAL`</InternalLink> and <InternalLink path="table-localities#regional-tables">`REGIONAL BY TABLE`</InternalLink> tables into a **non**-multi-region database is not supported.
* <InternalLink path="table-localities#regional-tables">`REGIONAL BY TABLE`</InternalLink> and <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink> tables can be restored **only** if the regions of the backed-up table match those of the destination database. All of the following must be true for `RESTORE` to be successful:
  * The <InternalLink path="multiregion-overview#database-regions">regions</InternalLink> of the source database and the regions of the destination database have the same set of regions.
  * The regions were added to each of the databases in the same order.
  * The databases have the same <InternalLink path="alter-database#set-primary-region">primary region</InternalLink>.

    The following example would be considered as having **mismatched** regions because the database regions were not added in the same order and the primary regions do not match.

    Running on the source database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database ADD region "us-west1";
    ```

    Running on the destination database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database ADD region "us-east1";
    ```

    In addition, the following scenario has mismatched regions between the databases since the regions were not added to the database in the same order.

    Running on the source database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE source_database ADD region "us-west1";
    ```

    Running on the destination database:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database ADD region "us-east1";
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER DATABASE destination_database SET PRIMARY REGION "us-east1";
    ```

## See also

* <InternalLink path="backup">`BACKUP`</InternalLink>
* <InternalLink path="take-full-and-incremental-backups">Take Full and Incremental Backups</InternalLink>
* <InternalLink path="take-and-restore-encrypted-backups">Take and Restore Encrypted Backups</InternalLink>
* <InternalLink path="take-and-restore-locality-aware-backups">Take and Restore Locality-aware Backups</InternalLink>
* <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">Take Backups with Revision History and Restore from a Point-in-time</InternalLink>
* <InternalLink path="manage-a-backup-schedule">Manage a Backup Schedule</InternalLink>
* <InternalLink path="configure-replication-zones">Replication Controls</InternalLink>
* <InternalLink path="enum">`ENUM`</InternalLink>
* <InternalLink path="create-type">`CREATE TYPE`</InternalLink>
* <InternalLink path="drop-type">`DROP TYPE`</InternalLink>