SHOW BACKUP

The SHOW BACKUP lists the contents of a backup created with the statement.

On self-hosted clusters, a feature is available in that improves backup query performance and simplifies backup operations. Refer to Query backups more efficiently.

Required privileges

SHOW BACKUP requires read permissions to its target destination. You can grant a user the EXTERNALIOIMPLICITACCESS to interact with external resources that require implicit access. Either the EXTERNALIOIMPLICITACCESS or the role is required for the following scenarios:

Interacting with a cloud storage resource using .
Using a custom endpoint on S3.
Using the 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 storage.

We recommend using .

Synopsis

Parameters

Parameter	Description
`SHOW BACKUPS IN collectionURI`	List the backup paths (subdirectories) in the given . Returns the `path` column with subdirectory paths in `<year/<month/<day-<timestamp` format. See the example.
`SHOW BACKUPS IN collectionURI [NEWER THAN interval] [OLDER THAN interval]`	(Preview) When the session variable is enabled, list backups with their IDs and optional time filtering. Returns `id`, `backup_time`, and `revision_start_time` columns. See the examples.
`SHOW BACKUP FROM {subdirectory \| backup_id} IN collectionURI`	Show the details of backups in the subdirectory or with the specified backup ID at the given . Also, use `FROM LATEST in collectionURI` to show the most recent backup. See the example.
`SHOW BACKUP SCHEMAS FROM {subdirectory \| backup_id} IN collectionURI`	Show the schema details of the backup in the given . See the example.
`collectionURI`	The URI for the . Note that `SHOW BACKUP` does not support listing backups if the 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

Option	Value	Description
`as_json`	N/A	Display the 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 for an example.
`debug_ids`	N/A	Display descriptor IDs of every object in the backup, including the object’s database and parent schema.
`encryption_passphrase`		The passphrase used to that the `BACKUP` statement generates (the data files and its manifest, containing the backup’s metadata).
`kms`		The URI of the cryptographic key stored in a key management service (KMS), or a comma-separated list of key URIs, used to . Refer to .
`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`		The database name.
`parent_schema_name`		The name of the parent schema.
`object_name`		The name of the , , , or schema.
`object_type`		The type of object: , , , or schema.
`backup_type`		The type of backup: or .
`start_time`		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`		The time to which data can be restored. This is equivalent to the of the backup. If the backup was not taken with , 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`		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`		Number of rows in tables that are part of the backup.
`create_statement`		The `CREATE` statement used to create , , or that are stored within the backup. This displays when `SHOW BACKUP SCHEMAS` is used. Note that tables with references to will only display foreign key constraints if the table to which the constraint relates to is also included in the backup.
`is_full_cluster`		Whether the backup is of a full cluster or not.
`regions`		The statement(s) used to configure , if they exist. If the database is a part of a single region cluster configuration, `NULL` will show.
`file_bytes`		(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`		The list of the ’s subdirectories. This field is returned for `SHOW BACKUPS IN collectionURI` only. The path format is `<year/<month/<day-<timestamp`.

See Show a backup with descriptor IDs for the responses displayed when the WITH debug_ids option is specified.

Query backups more efficiently

This feature is available in for self-hosted clusters.

To improve query performance and simplify backup operations, enable the session variable. This enables a more efficient backup query interface with the following benefits:

Significantly faster performance for listing backups in a collection
Server-side time filtering, using NEWER THAN and OLDER THAN clauses
Unique backup IDs that simplify
No AS OF SYSTEM TIME required when restoring from a backup ID

Backups taken when the cluster was on versions prior to v26.1 will not appear when using use_backups_with_ids=true. The backup ID interface requires backup index files that were introduced in v26.1. After your pre-v26.1 backups have expired, per your retention policy, this limitation will no longer affect your use of this feature.

Enable the backup ID interface

To use the backup ID interface, set the session variable:

SET use_backups_with_ids = true;

List backups with time filtering

When use_backups_with_ids is enabled, SHOW BACKUPS IN returns the backup ID, backup time, and revision start time for each backup:

SHOW BACKUPS IN 'external://backup_s3' NEWER THAN '-7d' OLDER THAN '-1d';

         id        |      backup_time
-------------------+------------------------
  Wnfqrp0BAAD4tw8= | 2026-04-18 08:42:11+00
  clvprp0BAADQmww= | 2026-04-17 10:15:23+00
  osDlrp0B         | 2026-04-16 14:23:55+00
(3 rows)

The response includes:

id: A unique identifier for the backup
backup_time: The end time of the backup (equivalent to the time you can restore to)

Time filtering

Use NEWER THAN and OLDER THAN to filter backups by age:

-- Backups from the last week
SHOW BACKUPS IN 'external://backup_s3' NEWER THAN '-1w';

-- Backups between 1 and 2 weeks old
SHOW BACKUPS IN 'external://backup_s3' NEWER THAN '-2w' OLDER THAN '-1w';

Time filters can be specified in either order. If no time filter is provided, the response is paginated to 50 rows.

Show a specific backup by ID

To view the contents of a specific backup, use its backup ID (from the id column in the SHOW BACKUPS IN output):

SHOW BACKUP FROM 'osDlrp0B' IN 'external://backup_s3';

This returns the same detailed backup information as the standard SHOW BACKUP output, including all databases, tables, and metadata for that specific backup.

Show revision history backup windows

For backups taken with , use the WITH REVISION START TIME option to display the revision history window for each backup:

SHOW BACKUPS IN 'external://backup_s3' WITH REVISION START TIME;

         id        |      backup_time       |  revision_start_time
-------------------+------------------------+------------------------
  Wnfqrp0BAAD4tw8= | 2026-04-18 08:42:11+00 | 2026-04-17 08:42:11+00
  clvprp0BAADQmww= | 2026-04-17 10:15:23+00 | 2026-04-16 10:15:23+00
  osDlrp0B         | 2026-04-16 14:23:55+00 | 2026-04-15 14:23:55+00
(3 rows)

The revision_start_time column shows the earliest timestamp you can restore to for each backup. This represents the start of the revision history window. For backups without revision history, this column displays NULL. Without the WITH REVISION START TIME option, the revision_start_time column does not appear in the output, and only the id and backup_time columns are shown. For details on performing point-in-time restores using revision history backups, see .

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 page.
Amazon S3 connection strings with the default AUTH=specified parameter. For guidance on using AUTH=implicit authentication with Amazon S3 buckets instead, read .

For guidance on connecting to other storage options or using other authentication parameters instead, read .

View a list of the available full backup subdirectories

To view a list of the available subdirectories, use the following command:

SHOW BACKUPS IN 'external://backup_s3';

        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:

SHOW BACKUP FROM LATEST IN 'external://backup_s3';

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 and backups in a specific subdirectory, use the following command:

SHOW BACKUP FROM '2022/04/08-142355.33' IN 'external://backup_s3';

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 locality-aware backups

To view a list of , pass the endpoint that is set as the default location with COCKROACH_LOCALITY=default:

> SHOW BACKUPS IN 's3://{default collection URI}/{path}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}';

        path
-------------------------
/2023/02/23-150925.62
/2023/03/08-192859.44
(2 rows)

To view a , pass locality-aware backup URIs to SHOW BACKUP:

> 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');

  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

SHOW BACKUP SCHEMAS FROM '2022/04/08-142601.69' IN 'external://backup_s3';

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

SHOW BACKUP FROM '2022/07/07-160311.96' IN 'external://backup_s3' WITH privileges;

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

Show an encrypted backup

Depending on how the backup was , use the and the same passphrase that was used to create the backup:

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:

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

  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:

SHOW BACKUP FROM '/2021/11/15-150703.21' IN 'external://backup_s3' WITH debug_ids;

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

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

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:

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

If SHOW BACKUP ... check_files cannot read from a file, it will return an error message similar to the following:
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 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:

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 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 and their responses. 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 indented and with newlines use the function:

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;

  {
      "dataSize": "458371",
      "indexEntries": "1015",
      "rows": "2565"
  }

To query for particular data, use the to expand the desired elements from the JSON response. The following query returns the paths to each of the data files within the backup:

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

          ?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

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Synopsis

Parameters

Options

Response

Query backups more efficiently

Enable the backup ID interface

List backups with time filtering

Time filtering

Show a specific backup by ID

Show revision history backup windows

Examples

View a list of the available full backup subdirectories

Show the most recent backup

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

Show locality-aware backups

Show a backup with schemas

Show a backup with privileges

Show details for scheduled backups

Show an encrypted backup

Show a backup with descriptor IDs

Validate a backup’s files

Show a backup’s internal metadata

See also

​Required privileges

​Synopsis

​Parameters

​Options

​Response

​Query backups more efficiently

​Enable the backup ID interface

​List backups with time filtering

​Time filtering

​Show a specific backup by ID

​Show revision history backup windows

​Examples

​View a list of the available full backup subdirectories

​Show the most recent backup

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

​Show locality-aware backups

​Show a backup with schemas

​Show a backup with privileges

​Show details for scheduled backups

​Show an encrypted backup

​Show a backup with descriptor IDs

​Validate a backup’s files

​Show a backup’s internal metadata

​See also

Required privileges

Synopsis

Parameters

Options

Response

Query backups more efficiently

Enable the backup ID interface

List backups with time filtering

Time filtering

Show a specific backup by ID

Show revision history backup windows

Examples

View a list of the available full backup subdirectories

Show the most recent backup

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

Show locality-aware backups

Show a backup with schemas

Show a backup with privileges

Show details for scheduled backups

Show an encrypted backup

Show a backup with descriptor IDs

Validate a backup’s files

Show a backup’s internal metadata

See also