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

# Take Full and Incremental Backups

Because CockroachDB is designed with high fault tolerance, backups are primarily needed for [disaster recovery](https://docs.cockroachlabs.com/docs/stable/disaster-recovery-planning.md) (i.e., if your cluster loses a majority of its nodes). Isolated issues (such as small-scale node outages) do not require any intervention. However, as an operational best practice, **we recommend taking regular backups of your data**.

There are two main types of backups:

* [Full backups](#full-backups)
* [Incremental backups](#incremental-backups)

You can use the [`BACKUP`](https://docs.cockroachlabs.com/docs/stable/backup#collectionURI-param) statement to efficiently back up your cluster's schemas and data to popular cloud services such as AWS S3, Google Cloud Storage, or NFS, and the [`RESTORE`](https://docs.cockroachlabs.com/docs/stable/restore#new-db-name) statement to efficiently restore schema and data as necessary. For more information, see [Use Cloud Storage](https://docs.cockroachlabs.com/docs/stable/use-cloud-storage.md).

> [!CAUTION]
>
> 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)  [backup](https://docs.cockroachlabs.com/docs/stable/backup.md#examples)  and  [restore](#examples)  [restore](https://docs.cockroachlabs.com/docs/stable/restore.md#examples)  examples.

> [!TIP]
>
> We recommend using scheduled backups to automate daily backups of your cluster.

## Supported products

The feature described on this page is available in **CockroachDB Basic**, **CockroachDB Standard**, **CockroachDB Advanced**, and **CockroachDB self-hosted** clusters when you are running [self-managed backups](https://docs.cockroachlabs.com/docs/cockroachcloud/take-and-restore-self-managed-backups.md). For a full list of features, refer to [Backup and restore product support](https://docs.cockroachlabs.com/docs/stable/backup-and-restore-overview.md#backup-and-restore-support).

## Backup collections

A *backup collection* defines a set of backups and their metadata. The collection can contain multiple full backups and their subsequent [incremental backups](#incremental-backups). The path to a backup is created using a date-based naming scheme and stored at the [collection URI](https://docs.cockroachlabs.com/docs/stable/backup.md#collectionURI-param) passed with the `BACKUP` statement.

For a [locality-aware backup](https://docs.cockroachlabs.com/docs/stable/take-and-restore-locality-aware-backups.md). The backup collection will be stored according to the URIs passed with the `BACKUP` statement: `BACKUP INTO LATEST IN {collectionURI}, {localityURI}, {localityURI}`. Here, the `collectionURI` represents the default locality.

By default, full backups are stored at the root of the collection's URI in a date-based path, and incremental backups are stored in the `/incrementals` directory. The following example shows a backup collection created using these default values, where all backups reside in one storage bucket:

```
Collection URI:
|—— 2022
  |—— 02
    |—— 09-155340.13/
      |—— Full backup files
[...]
|—— incrementals
  |—— 2022
  |—— 02
    |—— 25-172907.21/
      |—— 20220325
        |—— 17921.23
          |—— incremental backup files
```

[`SHOW BACKUPS IN {collectionURI}`](https://docs.cockroachlabs.com/docs/stable/show-backup#view-a-list-of-the-available-full-backup-subdirectories) will display a list of the full backup subdirectories at the collection's URI.

In the examples on this page, `{collectionURI}` is a placeholder for the storage location that will contain the example backup.

You will encounter an error if you run multiple [backup collections](https://docs.cockroachlabs.com/docs/stable/take-full-and-incremental-backups.md#backup-collections) 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.

For example, if you have a backup schedule running backups for the database `users` the full backup and incremental backup should have the same storage URI for the full and incremental schedule. (`CREATE SCHEDULE FOR BACKUP` will automatically create two schedules for the full and incremental backup to the same storage URI.) If there is another backup schedule, for the database `accounts`, the full and incremental backups for `accounts` should have the same storage URI. However, the storage URI for the `accounts` backup collection should be different to the storage URI for the `users` backup collection.

## Full backups

Full backups contain an un-replicated copy of your data and can always be used to restore your cluster. These files are roughly the size of your data and require greater resources to produce than incremental backups. You can take full backups as of a given timestamp. Optionally, you can include the available [revision history](https://docs.cockroachlabs.com/docs/stable/take-backups-with-revision-history-and-restore-from-a-point-in-time.md) in the backup.

In most cases, **it's recommended to take nightly full backups of your cluster**. A cluster backup allows you to do the following:

* Restore table(s) from the cluster
* Restore database(s) from the cluster
* Restore a full cluster

[Full cluster backups](https://docs.cockroachlabs.com/docs/stable/backup.md#back-up-a-cluster) include [Enterprise license keys](https://docs.cockroachlabs.com/docs/stable/licensing-faqs.md#set-a-license). When you [restore](https://docs.cockroachlabs.com/docs/stable/restore.md#new-db-name) a full cluster backup that includes Enterprise license, the Enterprise license is also restored.

> [!NOTE]
>
> To set a target for the amount of backup data written to each backup file, use the `bulkio.backup.file_size` [cluster setting](https://docs.cockroachlabs.com/docs/stable/cluster-settings.md).
>
> See the [`SET CLUSTER SETTING`](https://docs.cockroachlabs.com/docs/stable/set-cluster-setting) page for more details on using cluster settings.

### Take a full backup

To perform a full cluster backup, use the [`BACKUP`](https://docs.cockroachlabs.com/docs/stable/backup#collectionURI-param) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO '{collectionURI}';
```

To restore a backup, use the [`RESTORE`](https://docs.cockroachlabs.com/docs/stable/restore#new-db-name) statement, specifying what you want to restore as well as the [collection's](#backup-collections) URI:

* To restore the latest backup of a table:

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

* To restore the latest backup of a database:

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

* To restore the latest backup of your full cluster:

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

> [!NOTE]
>
> A full cluster restore can only be run on a destination cluster that has **never** had user-created databases or tables.

* To restore a backup from a specific subdirectory:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  RESTORE DATABASE bank FROM {subdirectory} IN '{collectionURI}';
  ```

To view the available backup subdirectories, use [`SHOW BACKUPS`](https://docs.cockroachlabs.com/docs/stable/show-backup).

## Incremental backups

If your cluster grows too large for daily [full backups](#full-backups), you can take less frequent full backups (e.g., weekly) with daily incremental backups. Incremental backups are storage efficient and faster than full backups for larger clusters.

If you are taking backups on a regular cadence, we recommend [creating a schedule](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-backup.md) for your backups.

### Recommendations for incremental backup frequency

Incremental backups form chains between full backups. Each incremental backup contains only the data that has changed since a base set of backups. This base set of backups must include one full backup and can include multiple incremental backups, which are typically smaller and faster to run than full backups. You can take incremental backups either as of a given timestamp or with full [revision history](https://docs.cockroachlabs.com/docs/stable/take-backups-with-revision-history-and-restore-from-a-point-in-time.md). Cockroach Labs supports up to 48 incremental backups between full backups. Taking more than 48 incremental backups between full backups increases the risk of performance issues due to the size of the backup chain. The maximum supported frequency for incremental backups is every five minutes.

### Garbage collection and backups

Incremental backups with [revision history](https://docs.cockroachlabs.com/docs/stable/take-backups-with-revision-history-and-restore-from-a-point-in-time.md#create-a-backup-with-revision-history) are created by finding what data has been created, deleted, or modified since the timestamp of the last backup in the chain of backups. For the first incremental backup in a chain, this timestamp corresponds to the timestamp of the base [(full) backup](#full-backups). For subsequent incremental backups, this timestamp is the timestamp of the previous incremental backup in the chain.

[Garbage collection Time to Live (GC TTL)](https://docs.cockroachlabs.com/docs/stable/architecture/storage-layer.md#garbage-collection) determines the period for which CockroachDB retains revisions of a key. If the GC TTL of the [backup's target](https://docs.cockroachlabs.com/docs/stable/backup.md#targets) is shorter than the frequency at which you take incremental backups with revision history, then the revisions become susceptible to garbage collection before you have backed them up. This will cause the incremental backup with revision history to fail.

We recommend configuring the garbage collection period to be at least the frequency of incremental backups and ideally with a buffer to account for slowdowns. You can configure garbage collection periods using the `ttlseconds` [replication zone setting](https://docs.cockroachlabs.com/docs/stable/configure-replication-zones.md#gc-ttlseconds).

If an incremental backup is created outside of the garbage collection period, you will receive a `protected ts verification error…`. To resolve this issue, see the [Common Errors](https://docs.cockroachlabs.com/docs/stable/common-errors.md#protected-ts-verification-error) page.

> [!NOTE]
>
> If you are creating incremental backups as part of a [backup schedule](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-backup.md), [protected timestamps](https://docs.cockroachlabs.com/docs/stable/architecture/storage-layer.md#protected-timestamps) will ensure the backup revision data is not garbage collected, which allows you to lower the GC TTL. See [Protected timestamps and scheduled backups](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-backup.md#protected-timestamps-and-scheduled-backups) for more detail.

### Take an incremental backup

Periodically run the [`BACKUP`][backup] command to take a full backup of your cluster:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BACKUP INTO '{collectionURI}';
```

Then, create nightly incremental backups based off of the full backups you've already created. To append an incremental backup to the most recent full backup created at the [collection's](#backup-collections) URI, use the `LATEST` syntax:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> BACKUP INTO LATEST IN '{collectionURI}';
```

This will add the incremental backup to the default `/incrementals` directory at the root of the backup collection's directory. With incremental backups in the `/incrementals` directory, you can apply different lifecycle/retention policies from cloud storage providers to the `/incrementals` directory as needed.

If it's ever necessary, you can then use the [`RESTORE`][restore] statement to restore your cluster, database(s), and/or table(s). Restoring from incremental backups requires previous full and incremental backups.

To restore from the latest backup in the collection, stored in the default `/incrementals` collection subdirectory, run:

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

To restore from a specific backup in the collection:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM '{subdirectory}' IN '{collectionURI}';
```

For example:

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

When you restore from an incremental backup, you're restoring the **entire** table, database, or cluster. CockroachDB uses both the latest (or a [specific](https://docs.cockroachlabs.com/docs/stable/restore.md#restore-a-specific-full-or-incremental-backup)) 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 [table](https://docs.cockroachlabs.com/docs/stable/restore.md#tables), [database](https://docs.cockroachlabs.com/docs/stable/restore.md#databases), or [cluster](https://docs.cockroachlabs.com/docs/stable/restore.md#full-cluster) with existing data. Refer to [Restore types](https://docs.cockroachlabs.com/docs/stable/restore.md#restore-types) for detail on the types of backups you can restore.

> [!NOTE]
>
> `RESTORE` will re-validate [indexes](https://docs.cockroachlabs.com/docs/stable/indexes.md) when [incremental backups](https://docs.cockroachlabs.com/docs/stable/take-full-and-incremental-backups.md) 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.

## Examples

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 [`CREATE EXTERNAL CONNECTION`](https://docs.cockroachlabs.com/docs/stable/create-external-connection) page.
* Amazon S3 connection strings with the **default** `AUTH=specified` parameter. For guidance on using `AUTH=implicit` authentication with Amazon S3 buckets instead, read [Cloud Storage Authentication](https://docs.cockroachlabs.com/docs/stable/cloud-storage-authentication.md).

For guidance on connecting to other storage options or using other authentication parameters instead, read [Use Cloud Storage](https://docs.cockroachlabs.com/docs/stable/use-cloud-storage.md#example-file-urls).

### Scheduled backups

You can use [`CREATE SCHEDULE FOR BACKUP`](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-backup) to set a recurring schedule for full and incremental backups.

Include the `FULL BACKUP ALWAYS` clause for a schedule to take only full backups. For example, to create a schedule for taking full cluster backups:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEDULE core_schedule_label
    FOR BACKUP INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now';
```

```
     schedule_id     |        name         | status |         first_run         | schedule |                                                                                       backup_stmt
---------------------+---------------------+--------+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  588799238330220545 | core_schedule_label | ACTIVE | 2020-09-11 00:00:00+00:00 | @daily   | BACKUP INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH detached
(1 row)
```

For more examples on how to schedule backups that take full and incremental backups, refer to [`CREATE SCHEDULE FOR BACKUP`](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-backup).

### Exclude a table's data from backups

In some situations, you may want to exclude a table's row data from a [backup](https://docs.cockroachlabs.com/docs/stable/backup.md#collectionURI-param). For example, you have a table that contains high-churn data that you would like to [garbage collect](https://docs.cockroachlabs.com/docs/stable/architecture/storage-layer.md#garbage-collection) more quickly than the [incremental backup](#incremental-backups) schedule for the database or cluster holding the table. You can use the `exclude_data_from_backup = true` parameter with a [`CREATE TABLE`](https://docs.cockroachlabs.com/docs/stable/create-table#create-a-table-with-data-excluded-from-backup) or [`ALTER TABLE`](https://docs.cockroachlabs.com/docs/stable/alter-table) statement to mark a table's row data for exclusion from a backup.

It is important to note that the backup produced contains an empty table with the data excluded. The backup process does not attempt to contact the [KV storage layer](https://docs.cockroachlabs.com/docs/stable/architecture/storage-layer.md) ranges to retrieve data when `exclude_data_from_backup = true`. Thus, backups will continue to succeed even when some ranges or regions are unavailable, if and only if, the unavailable ranges are in tables excluded from the backups.

Setting this parameter prevents the cluster or database backup from delaying [GC TTL](https://docs.cockroachlabs.com/docs/stable/configure-replication-zones.md#gc-ttlseconds) on the key span for this table, and it also respects the configured GC TTL. This is useful when you want to set a shorter garbage collection window for tables containing high-churn data to avoid an accumulation of unnecessary data and improves the reliability in partially unavailable clusters.

Using the `movr` database as an example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW TABLES;
```

```
schema_name |         table_name         | type  | owner | estimated_row_count | locality
--------------+----------------------------+-------+-------+---------------------+-----------
public      | promo_codes                | table | root  |                1021 | NULL
public      | rides                      | table | root  |                 730 | NULL
public      | user_promo_codes           | table | root  |                  58 | NULL
public      | users                      | table | root  |                 211 | NULL
public      | vehicle_location_histories | table | root  |               10722 | NULL
public      | vehicles                   | table | root  |                  69 | NULL
```

If the `user_promo_codes` table's data does not need to be included in future backups, you can run the following to exclude the table's row data:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = true);
```

Then back up the `movr` database:

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

Restore the database with a [new name](https://docs.cockroachlabs.com/docs/stable/restore.md#new-db-name):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE movr FROM LATEST IN 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH new_db_name = new_movr;
```

Move to the new database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
USE new_movr;
```

You'll find that the table schema is restored:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE user_promo_codes;
```

```
table_name    |                                                create_statement
-------------------+------------------------------------------------------------------------------------------------------------------
user_promo_codes | CREATE TABLE public.user_promo_codes (
              |     city VARCHAR NOT NULL,
              |     user_id UUID NOT NULL,
              |     code VARCHAR NOT NULL,
              |     "timestamp" TIMESTAMP NULL,
              |     usage_count INT8 NULL,
              |     CONSTRAINT user_promo_codes_pkey PRIMARY KEY (city ASC, user_id ASC, code ASC),
              |     CONSTRAINT user_promo_codes_city_user_id_fkey FOREIGN KEY (city, user_id) REFERENCES public.users(city, id)
              | ) WITH (exclude_data_from_backup = true)
```

However, the `user_promo_codes` table has no row data:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM user_promo_codes;
```

```
city | user_id | code | timestamp | usage_count
-----+---------+------+-----------+--------------
(0 rows)
```

To create a table with `exclude_data_from_backup`, see [Create a table with data excluded from backup](https://docs.cockroachlabs.com/docs/stable/create-table.md#create-a-table-with-data-excluded-from-backup).

### Advanced examples

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

* [Backup with revision history and point-in-time restore](https://docs.cockroachlabs.com/docs/stable/take-backups-with-revision-history-and-restore-from-a-point-in-time.md)
* [Locality-aware backup and restore](https://docs.cockroachlabs.com/docs/stable/take-and-restore-locality-aware-backups.md)
* [Encrypted backup and restore](https://docs.cockroachlabs.com/docs/stable/take-and-restore-encrypted-backups.md)
* [Restore into a different database](https://docs.cockroachlabs.com/docs/stable/restore.md#restore-tables-into-a-different-database)
* [Remove the foreign key before restore](https://docs.cockroachlabs.com/docs/stable/restore.md#remove-the-foreign-key-before-restore)
* [Restoring users from `system.users` backup](https://docs.cockroachlabs.com/docs/stable/restore#restoring-users-from-system-users-backup)
* [Exclude a table's data from backups](https://docs.cockroachlabs.com/docs/stable/take-full-and-incremental-backups.md)

## See also

* [`BACKUP`][backup]
* [`RESTORE`][restore]
* [Take and Restore Encrypted Backups](https://docs.cockroachlabs.com/docs/stable/take-and-restore-encrypted-backups.md)
* [Take and Restore Locality-aware Backups](https://docs.cockroachlabs.com/docs/stable/take-and-restore-locality-aware-backups.md)
* [Take Backups with Revision History and Restore from a Point-in-time](https://docs.cockroachlabs.com/docs/stable/take-backups-with-revision-history-and-restore-from-a-point-in-time.md)
* [Use the Built-in SQL Client](https://docs.cockroachlabs.com/docs/stable/cockroach-sql.md)
* [`cockroach` Commands Overview](https://docs.cockroachlabs.com/docs/stable/cockroach-commands)
* [Expire Past Backups](https://docs.cockroachlabs.com/docs/stable/expire-past-backups.md)

[backup]: https://docs.cockroachlabs.com/docs/v26.3/backup.md

[restore]: https://docs.cockroachlabs.com/docs/v26.3/restore.md