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

# BACKUP

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>;
};

CockroachDB's `BACKUP` <InternalLink path="sql-statements">statement</InternalLink> allows you to create <InternalLink path="take-full-and-incremental-backups">full or incremental backups</InternalLink> of your cluster's schema and data that are consistent as of a given timestamp.

You can [back up a full cluster](#back-up-a-cluster), which includes:

* 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>
* All <InternalLink path="manage-a-backup-schedule">scheduled jobs</InternalLink>

You can also backup:

* [An individual database](#back-up-a-database), which includes all of its tables and views.
* [An individual table](#back-up-a-table-or-view), which includes its indexes and views.

  `BACKUP` only backs up entire tables; it **does not** support backing up subsets of a table.

Because CockroachDB is designed with high fault tolerance, these backups are designed primarily for disaster recovery (i.e., if your cluster loses a majority of its nodes) through <InternalLink path="restore">`RESTORE`</InternalLink>. Isolated issues (such as small-scale node outages) do not require any intervention. You can check that backups in external storage are valid by using a <InternalLink path="backup-validation">backup validation</InternalLink> command.

To view the contents of an backup created with the `BACKUP` statement, use <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>.

<Tip>
  We recommend using scheduled backups to automate daily backups of your cluster.
</Tip>

<Danger>
  The `BACKUP ... TO` and `RESTORE ... FROM {storage_uri}` syntax has been removed from CockroachDB v24.3 and later.

  For details on the syntax to run `BACKUP` and `RESTORE`, refer to the  [backup](#examples)  <InternalLink path="backup#examples">backup</InternalLink>  and  [restore](#examples)  <InternalLink path="restore#examples">restore</InternalLink>  examples.
</Danger>

## Considerations

* [Full cluster backups](#back-up-a-cluster) include <InternalLink path="licensing-faqs#set-a-license">license keys</InternalLink>. When you <InternalLink path="restore">restore</InternalLink> a full cluster backup that includes a license, the license is also restored.
* You cannot restore a backup of a multi-region database into a single-region database.
* Exclude a table's row data from a backup using the <InternalLink path="take-full-and-incremental-backups">`exclude_data_from_backup`</InternalLink> parameter.
* `BACKUP` is a blocking statement. To run a backup job asynchronously, use the `DETACHED` option. See the [options](#options) below.
* 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>.

### Storage considerations

* Cockroach Labs tests functionality with AWS S3, Google Cloud Storage (GCS), and Azure Blob Storage. Other S3-compatible storage solutions are untested, but common compatibility issues in v24.3 and later may be fixed by adding the `AWS_SKIP_CHECKSUM` option to the S3 URLs.
* <InternalLink path="use-a-local-file-server">HTTP storage</InternalLink> is not supported for `BACKUP` and `RESTORE`.
* Modifying backup files in the storage location could invalidate a backup, and therefore, prevent a restore. In v22.1 and later, **we recommend enabling <InternalLink path="use-cloud-storage#immutable-storage">object locking</InternalLink> in your cloud storage bucket.**

<Danger>
  Cockroach Labs does not officially support untested storage systems. If you encounter issues when using unsupported S3-compatible storage, drivers, or frameworks, contact the maintainer.
</Danger>

You can test the connection from each node in the cluster to your external storage with the <InternalLink path="check-external-connection">`CHECK EXTERNAL CONNECTION`</InternalLink> statement.

## Required privileges

<Note>
  Starting in v22.2, CockroachDB introduces a new <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege model</InternalLink> that provides finer control over a user's privilege to work with the database, including taking backups.

  There is continued support for the [legacy privilege model](#required-privileges-using-the-legacy-privilege-model) for backups 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 new and existing backups.
</Note>

You can <InternalLink path="grant#grant-privileges-on-specific-tables-in-a-database">grant</InternalLink> the `BACKUP` privilege to a user or role depending on the type of backup:

| Backup   | Privilege                                                                                                                                                                                                                                   |
| -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Cluster  | Grant a user the `BACKUP` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink>. For example, `GRANT SYSTEM BACKUP TO user;`.                                                    |
| Database | Grant a user the `BACKUP` privilege on the target database. For example, `GRANT BACKUP ON DATABASE test_db TO user;`.                                                                                                                       |
| Table    | Grant a user the `BACKUP` privilege at the table level. This gives the user the privilege to back up the schema and all user-defined types that are associated with the table. For example, `GRANT BACKUP ON TABLE test_db.table TO user;`. |

The listed privileges do not cascade to objects lower in the schema tree. For example, if you are granted database-level `BACKUP` privileges, this does not give you the privilege to back up a table. If you need the `BACKUP` 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 `BACKUP` 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>.

<Note>
  You can grant the `BACKUP` privilege to a user or role **without** the `SELECT` privilege on a table. As a result, these users will be able to take backups, but they will not be able to run a `SELECT` query on that data directly. However, these users could still read this data indirectly, by restoring it from any backups they produce.
</Note>

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

### Privileges for managing a backup job

To manage a backup 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 backup 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 legacy privilege model that CockroachDB supports in v22.2 and earlier. Support for this privilege model will be removed in a future release of CockroachDB:

* <InternalLink path="take-full-and-incremental-backups#full-backups">Full cluster backups</InternalLink> 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 backups, the user must have <InternalLink path="security-reference/authorization#managing-privileges">read access</InternalLink> on all objects being backed up. Database backups require `CONNECT` privileges, and table backups require `SELECT` privileges. Backups of user-defined schemas, or backups containing user-defined types, require `USAGE` privileges.

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

## Destination privileges

You can grant a user the `EXTERNALIOIMPLICITACCESS` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink>.

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

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

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 S3-compatible services  <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/Nqtj0HvOrM_ugxgN/images/sql-diagrams/v26.2/backup.svg?fit=max&auto=format&n=Nqtj0HvOrM_ugxgN&q=85&s=7416474b56d73df07a250557bbbf8343" alt="backup syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="567" height="635" data-path="images/sql-diagrams/v26.2/backup.svg" />

## Parameters

CockroachDB stores full backups in a backup collection. Each full backup in a collection may also have incremental backups. For more detail on this, see <InternalLink path="take-full-and-incremental-backups#backup-collections">Backup collections</InternalLink>.

| Parameter                                      | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| ---------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `targets`                                      | Back up the listed [targets](#targets).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| <a id="subdirectory" /> `subdirectory`         | The name of the specific backup (e.g., `2021/03/23-213101.37`) in the collection to which you want to add an <InternalLink path="take-full-and-incremental-backups#incremental-backups">incremental backup</InternalLink>. To view available backup subdirectories, use <InternalLink path="show-backup">`SHOW BACKUPS IN collectionURI`</InternalLink>. If the backup `subdirectory` is not provided, incremental backups will be stored in the default `/incrementals` directory at the root of the collection URI. See the [Create incremental backups](#create-incremental-backups) example.<br /><br />**Warning:** If you use an arbitrary `STRING` as the subdirectory, a new full backup will be created, but it will never be shown in `SHOW BACKUPS IN`. We do not recommend using arbitrary strings as subdirectory names. |
| `LATEST`                                       | Append an incremental backup to the latest completed full backup's subdirectory.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| <a id="collectionURI-param" /> `collectionURI` | The URI where you want to store the backup. (Or, the default locality for a locality-aware backup.) The storage URI for each <InternalLink path="take-full-and-incremental-backups#backup-collections">backup collection</InternalLink> must be unique. You will encounter an error if you run multiple backup collections to the same storage URI.<br /><br />For information about this URL structure, see [Backup File URLs](#backup-file-urls).                                                                                                                                                                                                                                                                                                                                                                                   |
| `localityURI`                                  | The URI containing the `COCKROACH_LOCALITY` parameter for a non-default locality that is part of a single locality-aware backup.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `timestamp`                                    | Back up data as it existed as of <InternalLink path="as-of-system-time">`timestamp`</InternalLink>. The `timestamp` must be more recent than your data's garbage collection TTL (which is controlled by the <InternalLink path="configure-replication-zones">`gc.ttlseconds` replication zone variable</InternalLink>).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `backup_options`                               | Control the backup behavior with a comma-separated list of [these options](#options).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |

### Targets

| Target                             | Description                                                                                                                                                 |
| ---------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| N/A                                | Back up the cluster. For an example of a full cluster backup, refer to [Back up a cluster](#back-up-a-cluster).                                             |
| `DATABASE {database_name} [, ...]` | The names of the databases to back up. A database backup includes all tables and views in the database. Refer to [Back Up a Database](#back-up-a-database). |
| `TABLE {table_name} [, ...]`       | The names of the tables and <InternalLink path="views">views</InternalLink> to back up. Refer to [Back Up a Table or View](#back-up-a-table-or-view).       |

### Query parameters

| Query parameter      | Value                                               | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| -------------------- | --------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `ASSUME_ROLE`        | <InternalLink path="string">`STRING`</InternalLink> | Pass the [ARN](https://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html) of the role to assume. Use in combination with `AUTH=implicit` or `specified`. <br />`external_id`: Use as a value to `ASSUME_ROLE` to specify the [external ID](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user_externalid.html) for third-party access to your S3 bucket. Refer to <InternalLink path="cloud-storage-authentication">Cloud Storage Authentication</InternalLink> for setup details. |
| `AUTH`               | <InternalLink path="string">`STRING`</InternalLink> | The authentication parameter can define either `specified` (default) or `implicit` authentication. To use `specified` authentication, pass your [Service Account](https://cloud.google.com/iam/docs/understanding-service-accounts) credentials with the URI. To use `implicit` authentication, configure these credentials via an environment variable. Refer to the <InternalLink path="cloud-storage-authentication">Cloud Storage Authentication page</InternalLink> page for examples of each of these.               |
| `AWS_ENDPOINT`       | <InternalLink path="string">`STRING`</InternalLink> | Specify a custom endpoint for Amazon S3 or S3-compatible services. Use to define a particular region or a Virtual Private Cloud (VPC) endpoint.                                                                                                                                                                                                                                                                                                                                                                            |
| `AWS_SESSION_TOKEN`  | <InternalLink path="string">`STRING`</InternalLink> | (Optional) Use as part of temporary security credentials when accessing AWS S3. For more information, refer to Amazon's guide on [temporary credentials](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp_use-resources.html).                                                                                                                                                                                                                                                                         |
| `AWS_USE_PATH_STYLE` | <InternalLink path="string">`STRING`</InternalLink> | Change the URL format to path style from the default AWS S3 virtual-hosted–style URLs when connecting to Amazon S3 or S3-compatible services.                                                                                                                                                                                                                                                                                                                                                                              |
| `COCKROACH_LOCALITY` | Key-value pairs                                     | Define a locality-aware backup with a list of URIs using `COCKROACH_LOCALITY`. The value is either `default` or a single locality key-value pair, such as `region=us-east`. At least one `COCKROACH_LOCALITY` must the `default` per locality-aware backup. Refer to <InternalLink path="take-and-restore-locality-aware-backups">Take and Restore Locality-aware Backups</InternalLink> for more detail and examples.                                                                                                     |
| `S3_STORAGE_CLASS`   | <InternalLink path="string">`STRING`</InternalLink> | Specify the Amazon S3 storage class for files created by the backup job. Refer to [Back up with an S3 storage class](#back-up-with-an-s3-storage-class) for the available classes and an example.                                                                                                                                                                                                                                                                                                                          |

If you are creating an external connection<InternalLink path="create-external-connection">external connection</InternalLink> with <InternalLink path="backup#query-parameters">`BACKUP` query parameters</InternalLink> or <InternalLink path="cloud-storage-authentication">authentication</InternalLink> parameters, you must pass them in uppercase otherwise you will receive an `unknown query parameters` error.

### Options

| Option                                             | Value                                                  | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| -------------------------------------------------- | ------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `revision_history`                                 | <InternalLink path="bool">`BOOL`</InternalLink> / None | Create a backup with full <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">revision history</InternalLink>, which records every change made to the cluster within the garbage collection period leading up to and including the given timestamp.<br /><br />You can specify a backup with revision history without any value e.g., `WITH revision_history`. Or, you can explicitly define `WITH revision_history = 'true' / 'false'`. `revision_history` defaults to `true` when used with `BACKUP` or `CREATE SCHEDULE FOR BACKUP`. A value is **required** when using <InternalLink path="alter-backup-schedule">`ALTER BACKUP SCHEDULE`</InternalLink>.                                                                                                                                                                           |
| `encryption_passphrase`                            | <InternalLink path="string">`STRING`</InternalLink>    | The passphrase used to <InternalLink path="take-and-restore-encrypted-backups">encrypt the files</InternalLink> (`BACKUP` manifest and data files) that the `BACKUP` statement generates. This same passphrase is needed to decrypt the file when it is used to <InternalLink path="take-and-restore-encrypted-backups">restore</InternalLink> and to list the contents of the backup when using <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>. There is no practical limit on the length of the passphrase.                                                                                                                                                                                                                                                                                                                                                 |
| `detached`                                         | <InternalLink path="bool">`BOOL`</InternalLink> / None | When a backup runs in `detached` mode, it will execute asynchronously. The job ID will be returned after the backup <InternalLink path="backup-architecture#job-creation-phase">job creation</InternalLink> 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="backup#run-a-backup-asynchronously">example</InternalLink>. To check on the job status, use the <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink> statement. Backups running on a <InternalLink path="create-schedule-for-backup">schedule</InternalLink> have the `detached` option applied implicitly.<br /><br />To run a backup within a <InternalLink path="transactions">transaction</InternalLink>, use the `detached` option. |
| <a id="execution_locality" /> `EXECUTION LOCALITY` | Key-value pairs                                        | Restricts the execution of the backup to nodes that match the defined locality filter requirements. For example, `WITH EXECUTION LOCALITY = 'region=us-west-1a,cloud=aws'`. <br /><br />Refer to <InternalLink path="take-locality-restricted-backups">Take Locality-restricted backups</InternalLink> for usage and reference detail.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `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>. The key or keys are  used to encrypt the manifest and data files that the `BACKUP` statement generates and to decrypt them during a <InternalLink path="take-and-restore-encrypted-backups#examples">restore</InternalLink> operation, and to list the contents of the backup when using <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>. <br /><br />AWS KMS, Google Cloud KMS, and Azure Key Vault are supported.                                                                                     |
| `STRICT`                                           | <InternalLink path="bool">`BOOL`</InternalLink> / None | A locality-aware backup running in `STRICT` mode fails if it cannot back up data from a node with a locality tag to a bucket with a matching locality tag.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |

### 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#url-format">URL format</InternalLink>
* <InternalLink path="use-cloud-storage#example-file-urls">Example file URLs</InternalLink>
* <InternalLink path="cloud-storage-authentication">Authentication parameters</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.

<Tip>
  Backups support cloud object locking and [Amazon S3 storage classes](#back-up-with-an-s3-storage-class). For more detail, see <InternalLink path="use-cloud-storage#additional-cloud-storage-feature-support">Additional cloud storage feature support</InternalLink>.
</Tip>

## Functional details

### Object dependencies

Dependent objects must be  restored  backed up  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`  <InternalLink path="restore">`RESTORE`</InternalLink>  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>
  To exclude a table's row data from a backup, use the `exclude_data_from_backup` parameter with <InternalLink path="create-table#create-a-table-with-data-excluded-from-backup">`CREATE TABLE`</InternalLink> or <InternalLink path="alter-table">`ALTER TABLE`</InternalLink>.

  For more detail, see the <InternalLink path="take-full-and-incremental-backups">Exclude a table's data from backups</InternalLink> example.
</Note>

### Users and privileges

The `system.users` table stores your users and their passwords. To restore your users and privilege <InternalLink path="grant">grants</InternalLink>, do a cluster backup and restore the cluster to a fresh cluster with no user data. You can also backup the `system.users` table, and then use <InternalLink path="restore#restoring-users-from-system-users-backup">this procedure</InternalLink>.

## Performance

The backup job process minimizes its impact to the cluster's performance with:

* Even distribution of work to a node that has a replica of the range to back up. If a locality filter is specified, work is distributed to a node from those that match the locality filter and has the most locality tiers in common with a node that has a replica. Refer to the <InternalLink path="backup-architecture">Backup Architecture</InternalLink> page for a detailed explanation of how a backup job works.
* Integration with elastic CPU limiter by default, which helps to minimize the impact backups have on foreground traffic. This integration will limit the amount of CPU time used by a backup thereby allowing foreground SQL traffic to continue largely unaffected.

A backup job, like any read, cannot export a range if the range contains an <InternalLink path="architecture/transaction-layer#resolving-write-intents">unresolved intent</InternalLink>. While it is important to minimize the impact of bulk, background jobs like `BACKUP` on your foreground traffic, it is still crucial for backups to finish (in order to maintain your [recovery point objective (RPO)](https://en.wikipedia.org/wiki/Disaster_recovery#Recovery_Point_Objective)).

Unlike a normal <InternalLink path="architecture/reads-and-writes-overview#read-scenario">read transaction</InternalLink> that will block until any uncommitted writes it encounters are resolved, a backup job's read request will be allotted a fixed amount of CPU time to read the required keys and values. Once the backup's read request has exhausted this time, the backup will resume once it has been allocated more CPU time. This process allows for other requests, such as foreground SQL traffic to continue, almost unaffected, because there is a cap on how much CPU a backup job will take.

You can monitor your cluster's <InternalLink path="admission-control">admission control system</InternalLink> on the <InternalLink path="ui-overload-dashboard">Overload dashboard</InternalLink>. To monitor your backup jobs, refer to the <InternalLink path="backup-and-restore-monitoring">Backup and Restore Monitoring</InternalLink> page.

For a more technical explanation of elastic CPU, refer to the [Rubbing control theory on the Go scheduler](https://www.cockroachlabs.com/blog/rubbing-control-theory/) blog post.

We recommend always starting backups with a specific <InternalLink path="timestamp">timestamp</InternalLink> at least 10 seconds in the past. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP...AS OF SYSTEM TIME '-10s';
```

This improves performance by decreasing the likelihood that the `BACKUP` will be <InternalLink path="transactions#transaction-retries">retried because it contends with other statements/transactions</InternalLink>. However, because <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> returns historical data, your reads might be stale. Taking backups with `AS OF SYSTEM TIME '-10s'` is a good best practice to reduce the number of still-running transactions you may encounter, because a backup will eventually push the contending transactions to a higher timestamp, which causes the transactions to retry.

A backup job will initially ask individual ranges to back up but to skip if they encounter an intent. Any range that is skipped is placed at the end of the queue. When a backup job has completed its initial pass and is revisiting ranges, it will ask any range that did not resolve within the given time limit (default 1 minute) to attempt to resolve any intents that it encounters and to **not** skip. Additionally, the backup's read transaction priority is eventually set to `high`. This will result in contending transactions being pushed and retried at a higher timestamp.

If a backup job encounters too many retryable errors, it will enter a <InternalLink path="show-jobs#job-status">`failed` state</InternalLink> with the most recent error, which allows subsequent backups the chance to succeed. Refer to the <InternalLink path="backup-and-restore-monitoring">Backup and Restore Monitoring</InternalLink> page for metrics to track backup failures.

### Backup performance configuration

Cluster settings provide a means to tune a CockroachDB cluster. The following cluster settings are helpful for configuring backup files and performance:

#### `bulkio.backup.file_size`

Set a target for the amount of backup data written to each backup file. This is the maximum target size the backup will reach, but it is possible files of a smaller size are created during the backup job.

Note that if you lower `bulkio.backup.file_size` below the default, it will cause the backup job to create many small SST files, which could impact a restore job’s performance because it will need to keep track of so many small files.

**Default:** `128 MiB`

#### `cloudstorage.azure.concurrent_upload_buffers`

Improve the speed of backups to Azure Storage by increasing `cloudstorage.azure.concurrent_upload_buffers` to `3`. This setting configures the number of concurrent buffers that are used during file uploads to Azure Storage. Note that the higher this setting the more data that is held in memory, which can increase the risk of OOMs if there is not sufficient memory on each node.

**Default:** `1`

#### Cluster settings for cloud storage

The following cluster settings limit the read and write rates to <InternalLink path="use-cloud-storage">cloud storage</InternalLink>. A user may choose to use these settings if their backups overwhelm the network. These settings limit throughput and as a result backups and <InternalLink path="change-data-capture-overview">changefeeds</InternalLink> will take longer. The designated `<provider`s include `s3`, `gs`, and `azure`.

##### `cloudstorage.<provider.write.node_rate_limit`

Limit the number of bytes per second per node across operations writing to the designated cloud storage provider if non-zero.

**Default:** unlimited, `0 B`

##### `cloudstorage.<provider.write.node_burst_limit`

Limit the number of bytes per second per node handled concurrently across operations writing to the designated cloud storage provider if non-zero.

**Default:** unlimited, `0 B`

##### `cloudstorage.<provider.read.node_rate_limit`

Limit the number of bytes per second per node across operations reading to the designated cloud storage provider if non-zero.

**Default:** unlimited, `0 B`

##### `cloudstorage.<provider.read.node_burst_limit`

Limit the number of bytes per second per node handled concurrently across operations reading to the designated cloud storage provider if non-zero.

**Default:** unlimited, `0 B`

For a complete list, including all cluster settings related to backups, see the <InternalLink path="cluster-settings">Cluster Settings</InternalLink> page.

## Viewing and controlling backups jobs

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

| Action                 | SQL Statement                                               |
| ---------------------- | ----------------------------------------------------------- |
| View the backup status | <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink>   |
| Pause the backup       | <InternalLink path="pause-job">`PAUSE JOB`</InternalLink>   |
| Resume the backup      | <InternalLink path="resume-job">`RESUME JOB`</InternalLink> |
| Cancel the backup      | <InternalLink path="cancel-job">`CANCEL JOB`</InternalLink> |

You can also visit the <InternalLink path="ui-jobs-page">**Jobs** page</InternalLink> of the DB Console to view job details. The `BACKUP` statement will return when the backup is finished or if it encounters an error.

<Note>
  The presence of the `BACKUP MANIFEST` file in the backup subdirectory is an indicator that the backup job completed successfully.
</Note>

## Backup compactions

<Note>
  New in v26.2: This capability is available in <InternalLink path="cockroachdb-feature-availability">Preview</InternalLink> for self-hosted clusters in v26.2.
</Note>

Backup compactions automatically merge incremental backups, allowing you to take up to 400 incrementals between full backups instead of the usual 48-backup limit. This maintains the same RPO while reducing storage costs.

Enabling backup compactions also improves restore performance and is required for <InternalLink path="restore#run-faster-restores">faster restores</InternalLink> using `WITH EXPERIMENTAL COPY`.

### How it works

When enabled for scheduled backups, compaction jobs automatically trigger when the number of backups in the backup chain—the full backup and its incrementals—reaches a configured quantity. These jobs merge multiple consecutive incremental backups into a single compacted backup, reducing chain length while preserving data. Compactions run through <InternalLink path="admission-control">Admission Control</InternalLink> to minimize impact on foreground operations.

With backup compaction enabled, each backup chain can grow up to a maximum recommended size of 400 incremental backups, far more than the maximum of 48 recommended when using incremental backups without compaction.

<Note>
  Backup compactions improve restore performance by creating consolidated SST files from incremental backups. The original backups remain available for point-in-time restores. For fine-grained point-in-time restore, use <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">revision history backups</InternalLink>.
</Note>

### Enable backup compactions

Set the `backup.compaction.threshold` cluster setting to `4`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING backup.compaction.threshold = 4;
```

Higher values are not recommended.

* `0`: Disabled (default)
* `4`: Recommended, if enabling backup compactions. Compaction occurs when chain reaches length 4 (1 full + 3 incrementals)

<Note>
  Compactions only apply to **scheduled backups** with full and incremental backups. Manual `BACKUP` statements do not trigger compactions. In addition to full and incremental backups, compactions also support locality-aware, revision history, and encrypted backups.
</Note>

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING backup.compaction.threshold = 4;

CREATE SCHEDULE hourly_backups
FOR BACKUP DATABASE movr INTO 'external://backup_s3'
RECURRING '@hourly'
FULL BACKUP '@weekly';
```

This configuration runs hourly incrementals with weekly full backups. Each time the chain reaches 4, compaction automatically merges incrementals.

## Examples

Per our guidance in the [Performance](#performance) section, we recommend starting backups from a time at least 10 seconds in the past using <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink>.

The examples in this section use one of the following storage URIs:

* External connections, which allow you to represent an external storage or sink URI. You can then 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.
* Amazon S3 connection strings with the **default** `AUTH=specified` parameter. For guidance on using `AUTH=implicit` authentication with Amazon S3 buckets instead, read <InternalLink path="cloud-storage-authentication">Cloud Storage Authentication</InternalLink>.

For guidance on connecting to other storage options or using other authentication parameters instead, 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, see <InternalLink path="cloud-storage-authentication">Assume role authentication</InternalLink> for setup instructions.

The `BACKUP ... TO` and `RESTORE ... FROM {storage_uri}` syntax has been removed from CockroachDB v24.3 and later.

For details on the syntax to run `BACKUP` and `RESTORE`, refer to the  [backup](#examples)  <InternalLink path="backup#examples">backup</InternalLink>  and  [restore](#examples)  <InternalLink path="restore#examples">restore</InternalLink>  examples.

### Back up a cluster

To take a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> of a cluster:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

You will encounter an error if you run multiple <InternalLink path="take-full-and-incremental-backups#backup-collections">backup collections</InternalLink> to the same storage URI. Backup collections can contain multiple full and incremental backups, but each collection's URI must be unique. If you are using backup schedules, each schedule must have a unique URI.

### Back up a database

To take a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> of a single database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP DATABASE bank INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

To take a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> of multiple databases:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP DATABASE bank, employees INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

### Back up a table or view

To take a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> of a single table or view:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP bank.customers INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

To take a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink> of multiple tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP bank.customers, bank.accounts INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

### Back up all tables in a schema

To back up all tables in a <InternalLink path="create-schema">schema</InternalLink>, use a wildcard (`*`) with the schema name:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP test_schema.* INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

Alternatively, use a <InternalLink path="sql-name-resolution#lookup-with-fully-qualified-names">fully qualified name</InternalLink>: `database.schema.*`.

With this syntax, schemas will be resolved before databases. `test_object.*` will resolve to a *schema* of `test_object` within the set current database before matching to a database of `test_object`.

If a database and schema have the same name, such as `bank.bank`, running `BACKUP bank.*` will result in the schema resolving first. All the tables within that schema will be backed up. However, if this were to be run from a different database that does not have a `bank` schema, all tables in the `bank` database will be backed up.

See <InternalLink path="sql-name-resolution">Name Resolution</InternalLink> for more details on how naming hierarchy and name resolution work in CockroachDB.

### Create incremental backups

When a `BACKUP` statement specifies an existing subdirectory in the collection, explicitly or via the `LATEST` keyword, an incremental backup will be added to the default `/incrementals` directory at the root of the <InternalLink path="take-full-and-incremental-backups#backup-collections">collection</InternalLink> storage location.

To take an incremental backup using the `LATEST` keyword:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO LATEST IN 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

To store the backup in an existing subdirectory in the collection:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO {'subdirectory'} IN 'external://backup_s3' AS OF SYSTEM TIME '-10s';
```

<Note>
  If you intend to take a **full** backup, we recommend running `BACKUP INTO {collectionURI}` without specifying a subdirectory.
</Note>

### Run a backup asynchronously

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s' WITH DETACHED;
```

The job ID is returned after the backup <InternalLink path="backup-architecture#job-creation-phase">job creation</InternalLink> completes:

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

**Without** the `DETACHED` option, `BACKUP` 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)
```

### Back up with an S3 storage class

To associate your backup objects with a <InternalLink path="use-cloud-storage#amazon-s3-storage-classes">specific storage class</InternalLink> in your Amazon S3 bucket, use the `S3_STORAGE_CLASS` parameter with the class. For example, the following S3 connection URI specifies the `INTELLIGENT_TIERING` storage class:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP DATABASE movr INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}&S3_STORAGE_CLASS=INTELLIGENT_TIERING' AS OF SYSTEM TIME '-10s';
```

To use an external connection URI to back up to cloud storage with an associated S3 storage class, you need to include the `S3_STORAGE_CLASS` parameter when you <InternalLink path="create-external-connection">create the external connection</InternalLink>.

Use the parameter to set one of these [storage classes](https://docs.aws.amazon.com/AmazonS3/latest/API/API_PutObject.html#AmazonS3-PutObject-request-header-StorageClass) listed in Amazon's documentation. For more general usage information, see Amazon's [Using Amazon S3 storage classes](https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-class-intro.html) documentation.

<Danger>
  <InternalLink path="take-full-and-incremental-backups#incremental-backups">Incremental backups</InternalLink> are **not** compatible with the [S3 Glacier Flexible Retrieval or Glacier Deep Archive storage classes](https://docs.aws.amazon.com/AmazonS3/latest/userguide//storage-class-intro.html#sc-glacier). Incremental backups require the reading of previous backups on an ad-hoc basis, which is not possible with backup files already in Glacier Flexible Retrieval or Glacier Deep Archive. This is because these storage classes do not allow immediate access to an S3 object without first [restoring the archived objects](https://docs.aws.amazon.com/AmazonS3/latest/userguide/restoring-objects.html) to its S3 bucket.

  Refer to <InternalLink path="use-cloud-storage#incremental-backups-and-archive-storage-classes">Incremental backups and storage classes</InternalLink> for more detail.
</Danger>

### Advanced examples

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

* <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">Backup with revision history and point-in-time restore</InternalLink>
* <InternalLink path="take-and-restore-locality-aware-backups">Locality-aware backup and restore</InternalLink>
* <InternalLink path="take-and-restore-encrypted-backups">Encrypted backup and restore</InternalLink>
* <InternalLink path="restore#restore-tables-into-a-different-database">Restore into a different database</InternalLink>
* <InternalLink path="restore#remove-the-foreign-key-before-restore">Remove the foreign key before restore</InternalLink>
* <InternalLink path="restore#restoring-users-from-system-users-backup">Restoring users from `system.users` backup</InternalLink>
* <InternalLink path="take-full-and-incremental-backups">Exclude a table's data from backups</InternalLink>

## See also

* <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="show-backup">`SHOW BACKUP`</InternalLink>
* <InternalLink path="create-schedule-for-backup">`CREATE SCHEDULE FOR BACKUP`</InternalLink>
* <InternalLink path="restore">`RESTORE`</InternalLink>
* <InternalLink path="configure-replication-zones">Replication Controls</InternalLink>
* <InternalLink path="troubleshoot-replication-zones">Troubleshoot Replication Zones</InternalLink>