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

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

The `SHOW BACKUP` <InternalLink path="sql-statements">statement</InternalLink> lists the contents of a backup created with the <InternalLink path="backup">`BACKUP`</InternalLink> statement.

<Danger>
  The `SHOW BACKUP` syntax **without** the `IN` keyword has been removed from CockroachDB v24.3 and later.

  For guidance on the syntax for `SHOW BACKUP FROM`, refer to the [Synopsis](#synopsis) and [examples](#examples) on this page.
</Danger>

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

## Required privileges

`SHOW BACKUP` requires read permissions to its target destination.

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:

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

## Synopsis

<ShowBackup />

## Parameters

| Parameter                                                | Description                                                                                                                                                                                                                                                                                                                 |
| -------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `SHOW BACKUPS IN collectionURI`                          | List the backup paths in the given <InternalLink path="backup#backup-file-urls">collection URI</InternalLink>. [See the example](#view-a-list-of-the-available-full-backup-subdirectories).                                                                                                                                 |
| `SHOW BACKUP FROM subdirectory IN collectionURI`         | Show the details of backups in the subdirectory at the given <InternalLink path="backup#backup-file-urls">collection URI</InternalLink>. Also, use `FROM LATEST in collectionURI` to show the most recent backup. [See the example](#show-the-most-recent-backup).                                                          |
| `SHOW BACKUP SCHEMAS FROM subdirectory IN collectionURI` | Show the schema details of the backup in the given <InternalLink path="backup#backup-file-urls">collection URI</InternalLink>. [See the example](#show-a-backup-with-schemas).                                                                                                                                              |
| `collectionURI`                                          | The URI for the <InternalLink path="use-cloud-storage">backup storage</InternalLink>. Note that `SHOW BACKUP` does not support listing backups if the <InternalLink path="cockroach-nodelocal-upload">`nodelocal`</InternalLink> storage location is a symlink. Cockroach Labs recommends using remote storage for backups. |
| `kv\_option\_list`                                       | Control the behavior of `SHOW BACKUP` with a comma-separated list of [these options](#options).                                                                                                                                                                                                                             |

### Options

| Option                   | Value                                               | Description                                                                                                                                                                                                                                                                                                                                            |
| ------------------------ | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `as\_json`               | N/A                                                 | [Display the backup's internal metadata](#show-a-backup’s-internal-metadata) as JSON in the response.                                                                                                                                                                                                                                                  |
| `check\_files`           | N/A                                                 | Validate that all files belonging to a backup are in the expected location in storage. See [Validate a backup's files](#validate-a-backup’s-files) for an example.                                                                                                                                                                                     |
| `debug\_ids`             | N/A                                                 | [Display descriptor IDs](#show-a-backup-with-descriptor-ids) of every object in the backup, including the object's database and parent schema.                                                                                                                                                                                                         |
| `encryption\_passphrase` | <InternalLink path="string">`STRING`</InternalLink> | The passphrase used to <InternalLink path="take-and-restore-encrypted-backups">encrypt the files</InternalLink> that the `BACKUP` statement generates (the data files and its manifest, containing the backup's metadata).                                                                                                                             |
| `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>. |
| `incremental\_location`  | <InternalLink path="string">`STRING`</InternalLink> | [List the details of an incremental backup](#show-a-backup-taken-with-the-incremental-location-option) taken with the <InternalLink path="backup">`incremental\_location` option</InternalLink>.                                                                                                                                                       |
| `privileges`             | N/A                                                 | List which users and roles had which privileges on each table in the backup. Displays original ownership of the backup.                                                                                                                                                                                                                                |

## Response

The following fields are returned:

| Field                  | Value                                                       | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| ---------------------- | ----------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database\_name`       | <InternalLink path="string">`STRING`</InternalLink>         | The database name.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `parent\_schema\_name` | <InternalLink path="string">`STRING`</InternalLink>         | The name of the parent schema.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `object\_name`         | <InternalLink path="string">`STRING`</InternalLink>         | The name of the <InternalLink path="create-database">database</InternalLink>, <InternalLink path="create-table">table</InternalLink>, <InternalLink path="create-type">type</InternalLink>, or schema.                                                                                                                                                                                                                                                                                                                                       |
| `object\_type`         | <InternalLink path="string">`STRING`</InternalLink>         | The type of object: <InternalLink path="create-database">database</InternalLink>, <InternalLink path="create-table">table</InternalLink>, <InternalLink path="create-type">type</InternalLink>, or schema.                                                                                                                                                                                                                                                                                                                                   |
| `backup\_type`         | <InternalLink path="string">`STRING`</InternalLink>         | The type of backup: <InternalLink path="take-full-and-incremental-backups#full-backups">full</InternalLink> or <InternalLink path="take-full-and-incremental-backups#incremental-backups">incremental</InternalLink>.                                                                                                                                                                                                                                                                                                                        |
| `start\_time`          | <InternalLink path="timestamp">`TIMESTAMPTZ`</InternalLink> | The time of the earliest data encapsulated in the backup. Note that this only displays for incremental backups. For a full backup, this is `NULL`.                                                                                                                                                                                                                                                                                                                                                                                           |
| `end\_time`            | <InternalLink path="timestamp">`TIMESTAMPTZ`</InternalLink> | The time to which data can be restored. This is equivalent to the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> of the backup. If the backup was *not* taken with <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">revision history</InternalLink>, the `end\_time` is the *only* time the data can be restored to. If the backup was taken with revision history, the `end\_time` is the latest time the data can be restored to.                                       |
| `size\_bytes`          | <InternalLink path="int">`INT`</InternalLink>               | The size of the backup objects, in bytes. Note that `size\_bytes` indicates the logical size of the table objects, which is computed as the sum of the size of each key value pair. See `file\_bytes` in this table for more detail.                                                                                                                                                                                                                                                                                                         |
| `rows`                 | <InternalLink path="int">`INT`</InternalLink>               | Number of rows in tables that are part of the backup.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `create\_statement`    | <InternalLink path="string">`STRING`</InternalLink>         | The `CREATE` statement used to create <InternalLink path="create-table">table(s)</InternalLink>, <InternalLink path="create-view">view(s)</InternalLink>, or <InternalLink path="create-sequence">sequence(s)</InternalLink> that are stored within the backup. This displays when `SHOW BACKUP SCHEMAS` is used. Note that tables with references to <InternalLink path="foreign-key">foreign keys</InternalLink> will only display foreign key constraints if the table to which the constraint relates to is also included in the backup. |
| `is\_full\_cluster`    | <InternalLink path="bool">`BOOL`</InternalLink>             | Whether the backup is of a full cluster or not.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `regions`              | <InternalLink path="string">`STRING`</InternalLink>         | The <InternalLink path="alter-database">`ALTER DATABASE`</InternalLink> statement(s) used to configure <InternalLink path="multiregion-overview#database-regions">multiple database regions</InternalLink>, if they exist. If the database is a part of a single region cluster configuration, `NULL` will show.                                                                                                                                                                                                                             |
| `file\_bytes`          | <InternalLink path="int">`INT`</InternalLink>               | (With the `check\_files` option only) The estimated bytes in external storage for a particular table object. This is the physical bytes that a given table object is taking up. For example, when the files are written to disk in storage they could be compressed. If you total all file bytes, the result is the physical bytes in your storage location. Note that for smaller tables the byte size in `file\_bytes` may be larger than `size\_bytes` because of the overhead required to create an SST file.                            |
| `path`                 | <InternalLink path="string">`STRING`</InternalLink>         | The list of the <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink>'s subdirectories. This field is returned for `SHOW BACKUPS IN collectionURI` only. The path format is `//-`.                                                                                                                                                                                                                                                                                                                  |

See [Show a backup with descriptor IDs](#show-a-backup-with-descriptor-ids) for the responses displayed when the `WITH debug_ids` option is specified.

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

### View a list of the available full backup subdirectories

To view a list of the available <InternalLink path="take-full-and-incremental-backups#full-backups">full backups</InternalLink> subdirectories, use the following command:

```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
-------------------------
/2022/04/13-202334.48
/2022/04/13-202413.70
(2 rows)
```

The path format is `<year/<month/<day-<timestamp`.

### Show the most recent backup

To view the most recent backup, use the `LATEST` syntax:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM LATEST 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 |                                             regions
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+------+-----------------+--------------------------------------------------------------------------------------------------
NULL          | NULL               | movr                       | database    | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | ALTER DATABASE movr SET PRIMARY REGION "us-east-2"; ALTER DATABASE movr ADD REGION "us-west-1";
movr          | NULL               | public                     | schema      | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
movr          | public             | users                      | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |      30069 |  327 |        f        | NULL
movr          | public             | vehicles                   | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |      17456 |   88 |        f        | NULL
movr          | public             | rides                      | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     251254 |  862 |        f        | NULL
movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     660910 | 9649 |        f        | NULL
movr          | public             | promo_codes                | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |     227789 | 1020 |        f        | NULL
movr          | public             | user_promo_codes           | table       | full        | NULL       | 2023-05-23 15:27:33.251745 |       6973 |   83 |        f        | NULL
movr          | public             | crdb_internal_region       | type        | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
movr          | public             | _crdb_internal_region      | type        | full        | NULL       | 2023-05-23 15:27:33.251745 |       NULL | NULL |        f        | NULL
(10 rows)
```

### View a list of the full and incremental backups in a specific full backup subdirectory

To view a list of the <InternalLink path="take-full-and-incremental-backups#full-backups">full</InternalLink> and <InternalLink path="take-full-and-incremental-backups#incremental-backups">incremental</InternalLink> backups in a specific subdirectory, use the following command:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '2022/04/08-142355.33' 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
--------------+--------------------+----------------------------+-------------+-------------+---------------------------+----------------------------+------------+-------+------------------
NULL          | NULL               | movr                       | database    | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false
movr          | NULL               | public                     | schema      | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false
movr          | public             | users                      | table       | full        | NULL                      | 2022-04-08 14:23:55.33557  |      25856 |   281 |      false
NULL          | NULL               | system                     | database    | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |       NULL |  NULL |      true
system        | public             | users                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         99 |     2 |      true
system        | public             | zones                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        236 |     8 |      true
system        | public             | settings                   | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        372 |     5 |      true
system        | public             | ui                         | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |          0 |     0 |      true
system        | public             | jobs                       | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |      30148 |    23 |      true
system        | public             | locations                  | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |        261 |     5 |      true
system        | public             | role_members               | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         94 |     1 |      true
. . .
```

### Show a backup taken with the incremental location option

To view an incremental backup that was taken with the `incremental_location` option, run `SHOW BACKUP` with the full backup and incremental backup location following the original `BACKUP` statement.

You can use the option to show the most recent backup where `incremental_location` has stored the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM LATEST IN {'full backup collectionURI'} WITH incremental_location = {'incremental backup collectionURI'};
```

```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
--------------+--------------------+----------------------------+-------------+-------------+----------------------------+----------------------------+------------+-------+------------------
NULL          | NULL               | movr                       | database    | full        | NULL                       | 2022-04-13 20:01:15.177739 |       NULL |  NULL |      false
movr          | NULL               | public                     | schema      | full        | NULL                       | 2022-04-13 20:01:15.177739 |       NULL |  NULL |      false
movr          | public             | rides                      | table       | full        | NULL                       | 2022-04-13 20:01:15.177739 |     395716 |  1415 |      false
NULL          | NULL               | system                     | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
system        | public             | users                      | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |         99 |     2 |      true
system        | public             | scheduled_jobs             | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |        250 |     1 |      true
system        | public             | database_role_settings     | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |          0 |     0 |      true
system        | public             | tenant_settings            | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |          0 |     0 |      true
NULL          | NULL               | defaultdb                  | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
defaultdb     | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
NULL          | NULL               | postgres                   | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
postgres      | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
NULL          | NULL               | movr                       | database    | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
movr          | NULL               | public                     | schema      | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |       NULL |  NULL |      true
movr          | public             | users                      | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |      81098 |   892 |      true
movr          | public             | vehicles                   | table       | incremental | 2022-04-13 20:01:15.177739 | 2022-04-13 20:05:04.2049   |      57755 |   296 |      true
. . .
```

### Show locality-aware backups

<Note>
  `SHOW BACKUP` is able to display metadata using `check_files` for locality-aware backups taken with the <InternalLink path="show-backup#show-a-backup-taken-with-the-incremental-location-option">`incremental_location`</InternalLink> option.
</Note>

To view a list of <InternalLink path="take-and-restore-locality-aware-backups">locality-aware backups</InternalLink>, pass the endpoint <InternalLink path="backup#backup-file-urls">collection URI</InternalLink> that is set as the `default` location with `COCKROACH_LOCALITY=default`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW BACKUPS IN 's3://{default collection URI}/{path}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        path
-------------------------
/2023/02/23-150925.62
/2023/03/08-192859.44
(2 rows)
```

To view a <InternalLink path="take-and-restore-locality-aware-backups">locality-aware backup</InternalLink>, pass locality-aware backup URIs to `SHOW BACKUP`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW BACKUP FROM LATEST IN ('s3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=default', 's3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=region%3Dus-west');
```

```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
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+------+------------------
  NULL          | NULL               | movr                       | database    | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | NULL               | public                     | schema      | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | public             | users                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       5633 |   58 |        f
  movr          | public             | vehicles                   | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       3617 |   17 |        f
  movr          | public             | rides                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     159269 |  511 |        f
  movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |      79963 | 1092 |        f
  movr          | public             | promo_codes                | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     221763 | 1003 |        f
  movr          | public             | user_promo_codes           | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |        927 |   11 |        f
(8 rows)
```

### Show a backup with schemas

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP SCHEMAS FROM '2022/04/08-142601.69' 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 |                                                                                                                create_statement
--------------+--------------------+----------------------------+-------------+-------------+---------------------------+----------------------------+------------+-------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL          | NULL               | movr                       | database    | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false      | NULL
movr          | NULL               | public                     | schema      | full        | NULL                      | 2022-04-08 14:23:55.33557  |       NULL |  NULL |      false      | NULL
movr          | public             | users                      | table       | full        | NULL                      | 2022-04-08 14:23:55.33557  |      25856 |   281 |      false      | CREATE TABLE users (
              |                    |                            |             |             |                           |                            |            |       |                 |     id UUID NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     city VARCHAR NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     name VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     address VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     credit_card VARCHAR NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     CONSTRAINT users_pkey PRIMARY KEY (city ASC, id ASC)
              |                    |                            |             |             |                           |                            |            |       |                 | )
NULL          | NULL               | system                     | database    | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |       NULL |  NULL |      true       | NULL
system        | public             | users                      | table       | incremental | 2022-04-08 14:23:55.33557 | 2022-04-08 14:26:01.699694 |         99 |     2 |      true       | CREATE TABLE users (
              |                    |                            |             |             |                           |                            |            |       |                 |     username STRING NOT NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     "hashedPassword" BYTES NULL,
              |                    |                            |             |             |                           |                            |            |       |                 |     "isRole" BOOL NOT NULL DEFAULT false,
              |                    |                            |             |             |                           |                            |            |       |                 |     CONSTRAINT "primary" PRIMARY KEY (username ASC),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "primary" (username),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "fam_2_hashedPassword" ("hashedPassword"),
              |                    |                            |             |             |                           |                            |            |       |                 |     FAMILY "fam_3_isRole" ("isRole")
              |                    |                            |             |             |                           |                            |            |       |                 | )
. . .
```

### Show a backup with privileges

Use the `WITH privileges` [option](#options) to view a list of which users and roles had which privileges on each database and table in the backup. This parameter also displays the original owner of objects in the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '2022/07/07-160311.96' IN 'external://backup_s3' WITH privileges;
```

```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 |                                                                                        privileges                                                                                         | owner
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+-------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
NULL            | NULL               | system                     | database    | full        | NULL       | 2022-07-07 16:03:11.962683 |       NULL |  NULL |      true       | GRANT CONNECT ON DATABASE system TO admin; GRANT CONNECT ON DATABASE system TO root;                                                                                                      | node
system          | public             | users                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |         99 |     2 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE users TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE users TO root;                                                  | node
system          | public             | zones                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |        236 |     8 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE zones TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE zones TO root;                                                  | node
system          | public             | settings                   | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |        423 |     6 |      true       | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE settings TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON TABLE settings TO root;                                            | root
movr            | NULL               | public                     | schema      | full        | NULL       | 2022-07-07 16:03:11.962683 |       NULL |  NULL |      true       | GRANT ALL ON SCHEMA public TO admin; GRANT CREATE, USAGE ON SCHEMA public TO public; GRANT ALL ON SCHEMA public TO root;                                                                  | admin
movr            | public             | users                      | table       | full        | NULL       | 2022-07-07 16:03:11.962683 |      57787 |   634 |      true       | GRANT ALL ON TABLE users TO admin; GRANT ALL ON TABLE users TO root;                                                                                                                      | root
```

You will receive an error if there is a collection of backups in the storage location that you pass to `SHOW BACKUP`. It is necessary to run `SHOW BACKUP` with the specific backup directory rather than the backup collection's top-level directory. Use [`SHOW BACKUPS IN`](#show-backups-in) with your storage location to list the backup directories it contains, which can then be run with `SHOW BACKUP` to inspect the metadata.

### Show details for scheduled backups

When a <InternalLink path="create-schedule-for-backup">backup is created by a schedule</InternalLink>, it is stored within a collection of backups in the given collection URI. To view details for a backup created by a schedule, you can use the following:

* `SHOW BACKUPS IN collectionURI` statement to [view a list of the available full backup subdirectories](#view-a-list-of-the-available-full-backup-subdirectories).
* `SHOW BACKUP FROM subdirectory IN collectionURI` statement to [view a list of the full and incremental backups that are stored in a specific full backup's subdirectory](#view-a-list-of-the-full-and-incremental-backups-in-a-specific-full-backup-subdirectory).

### Show an encrypted backup

Depending on how the backup was <InternalLink path="take-and-restore-encrypted-backups">encrypted</InternalLink>, use the <InternalLink path="backup">`encryption_passphrase` option</InternalLink> and the same passphrase that was used to create the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '2020/09/24-190540.54' IN 's3://{bucket name}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}'
      WITH encryption_passphrase = 'password123';
```

Or, use the `kms` option and the same KMS URI that was used to create the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '2020/09/24-190540.54' IN 's3://test/backups/test_explicit_kms?AWS_ACCESS_KEY_ID=123&AWS_SECRET_ACCESS_KEY=123'
      WITH kms = 'aws:///arn:aws:kms:us-east-1:123456789:key/1234-abcd-5678-efgh-90ij?AWS_ACCESS_KEY_ID=123456&AWS_SECRET_ACCESS_KEY=123456&REGION=us-east-1';
```

```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
----------------+--------------------+----------------------------+-------------+-------------+------------------------------------------------+------------+------+------------------
  NULL          | NULL               | system                     | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  system        | public             | users                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        144 |    3 |      true
  system        | public             | zones                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        201 |    7 |      true
  system        | public             | settings                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        431 |    6 |      true
  system        | public             | ui                         | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  system        | public             | jobs                       | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     434302 |   62 |      true
  system        | public             | locations                  | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        261 |    5 |      true
  system        | public             | role_members               | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        184 |    2 |      true
  system        | public             | comments                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  system        | public             | scheduled_jobs             | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |        875 |    2 |      true
  NULL          | NULL               | defaultdb                  | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  NULL          | NULL               | postgres                   | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  NULL          | NULL               | movr                       | database    | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
  movr          | public             | users                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       4911 |   50 |      true
  movr          | public             | vehicles                   | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       3182 |   15 |      true
  movr          | public             | rides                      | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     156387 |  500 |      true
  movr          | public             | vehicle_location_histories | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |      73918 | 1000 |      true
  movr          | public             | promo_codes                | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |     216083 | 1000 |      true
  movr          | public             | user_promo_codes           | table       | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |          0 |    0 |      true
  defaultdb     | NULL               | org_one                    | schema      | full        |  NULL       | 2020-09-24 19:05:40.542168+00:00 |       NULL | NULL |      true
(20 rows)
```

### Show a backup with descriptor IDs

Use `WITH debug_ids` to display the descriptor IDs related to each object in the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH debug_ids;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
database_name | database_id | parent_schema_name | parent_schema_id |        object_name         | object_id | object_type | backup_type |         start_time         |          end_time          | size_bytes | rows  | is_full_cluster
--------------+-------------+--------------------+------------------+----------------------------+-----------+-------------+-------------+----------------------------+----------------------------+------------+-------+------------------
NULL          |        NULL | NULL               |             NULL | movr                       |        52 | database    | full        | NULL                       | 2021-10-04 15:18:29.872912 |       NULL |  NULL |      false
movr          |          52 | public             |               29 | users                      |        53 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      35876 |   392 |      false
movr          |          52 | public             |               29 | vehicles                   |        54 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      25404 |   129 |      false
movr          |          52 | public             |               29 | rides                      |        55 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     280020 |   971 |      false
movr          |          52 | public             |               29 | vehicle_location_histories |        56 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     865205 | 12686 |      false
movr          |          52 | public             |               29 | promo_codes                |        57 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |     229155 |  1043 |      false
movr          |          52 | public             |               29 | user_promo_codes           |        58 | table       | full        | NULL                       | 2021-10-04 15:18:29.872912 |      10824 |   128 |      false
NULL          |        NULL | NULL               |             NULL | movr                       |        52 | database    | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |       NULL |  NULL |      false
movr          |          52 | public             |               29 | users                      |        53 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | vehicles                   |        54 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | rides                      |        55 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | vehicle_location_histories |        56 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | promo_codes                |        57 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
movr          |          52 | public             |               29 | user_promo_codes           |        58 | table       | incremental | 2021-10-04 15:18:29.872912 | 2021-10-04 15:18:53.354707 |          0 |     0 |      false
```

### Validate a backup's files

1. Use `SHOW BACKUP... check_files` with a backup for validation:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SHOW BACKUP "2022/09/19-134123.64" IN "s3://bucket?AWS_ACCESS_KEY_ID={Access Key ID}&AWS_SECRET_ACCESS_KEY={Secret Access Key}" WITH check_files;
   ```

   This will return the following output after validating that the backup files are correct and present:

   ```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 | file_bytes
   ----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+-------+-----------------+-------------
     NULL          | NULL               | movr                       | database    | full        | NULL       | 2022-09-19 13:41:23.645189 |       NULL |  NULL |        f        |       NULL
     movr          | NULL               | public                     | schema      | full        | NULL       | 2022-09-19 13:41:23.645189 |       NULL |  NULL |        f        |       NULL
     movr          | public             | users                      | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |      31155 |   340 |        f        |      16598
     movr          | public             | vehicles                   | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |      22282 |   113 |        f        |      12459
     movr          | public             | rides                      | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     261950 |   902 |        f        |     135831
     movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     742557 | 10850 |        f        |     318583
     movr          | public             | promo_codes                | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |     228320 |  1034 |        f        |     118376
     movr          | public             | user_promo_codes           | table       | full        | NULL       | 2022-09-19 13:41:23.645189 |       9320 |   111 |        f        |       4832
   ```

   The output will return `file_bytes` along with the columns you receive from `SHOW BACKUP` without `check_files`. The `file_bytes` column indicates the estimated bytes in external storage for a particular table object. For more detail on the output columns, see the `SHOW BACKUP` <InternalLink path="show-backup#response">Response</InternalLink> table.
2. If `SHOW BACKUP... check_files` cannot read from a file, it will return an error message similar to the following:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ERROR: The following files are missing from the backup:
     s3:/bucket-name/2022/09/19-134123.64/data/797981063156727810.sst
   ```

   `SHOW BACKUP... check_files` will return up to ten file paths for incorrect or missing files.

For more information on validating a backup, see the <InternalLink path="backup-validation">Backup Validation</InternalLink> page.

### Show a backup's internal metadata

Use the `WITH as_json` option to output a backup's internal metadata, contained in its manifest file, as a JSON value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH as_json;
```

The response will include a `manifest` column with the file's contents as the JSON value. Use <InternalLink path="functions-and-operators#jsonb-functions">JSONB functions</InternalLink> to query particular data or edit the format of the response.

The response returned from `SHOW BACKUP FROM... WITH as_json` is a backup's internal metadata. This content is subject to change from version to version of CockroachDB and does not offer the same stability guarantees as the other `SHOW BACKUP` [options](#options) and their [responses](#response). As a result, `as_json` should **only** be used for debugging or general inspection purposes.

For example, to return a specific entry from the JSON response as a <InternalLink path="string">`string`</InternalLink> indented and with newlines use the <InternalLink path="functions-and-operators#jsonb-functions">`jsonb_pretty()`</InternalLink> function:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' with as_json) SELECT jsonb_pretty(manifest->'entryCounts') AS f FROM x;
```

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  {
      "dataSize": "458371",
      "indexEntries": "1015",
      "rows": "2565"
  }
```

To query for particular data, use the <InternalLink path="functions-and-operators#jsonb-functions">`jsonb_array_elements()` function</InternalLink> to expand the desired elements from the JSON response. The following query returns the paths to each of the data files within the backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH as_json) SELECT f->>'path' FROM (SELECT jsonb_array_elements(manifest->'files') AS f FROM x);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
          ?column?
-------------------------------
  data/710798326337404929.sst
  data/710798326337404929.sst
  data/710798328891998209.sst
  data/710798326337404929.sst
  data/710798326337404929.sst
  data/710798328434982913.sst
  data/710798328891998209.sst
  data/710798326337404929.sst
  data/710798326337404929.sst
```

## See also

* <InternalLink path="backup">`BACKUP`</InternalLink>
* <InternalLink path="restore">`RESTORE`</InternalLink>