On this page 
Warning:
CockroachDB v20.1 is no longer supported as of November 12, 2021. For more details, refer to the Release Support Policy.
The SHOW BACKUP statement lists the contents of an enterprise backup created with the BACKUP statement.
Required privileges
Only members of the admin role can run SHOW BACKUP. By default, the root user belongs to the admin role.
Synopsis
Parameters
| Parameter | Description |
|---|---|
location |
The location of the backup to inspect. For more details, see Backup File URLs. |
kv_option_list |
Control the show behavior with a comma-separated list of these options. |
Options
| Option | Value | Description |
|---|---|---|
privileges |
N/A | New in v20.1: List which users and roles had which privileges on each table in the backup. |
encryption_passphrase |
STRING |
New in v20.1: The passphrase used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates. |
Response
The following fields are returned.
| Field | Description |
|---|---|
database_name |
The database name. |
table_name |
The table name. |
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 AS OF SYSTEM TIME of the backup. If the backup was not taken with revision history, 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, in bytes. |
create_statement |
The CREATE statement used to create table(s), view(s), or sequence(s) that are stored within the backup. This displays when SHOW BACKUP SCHEMAS is used. Note that tables with references to foreign keys will only display foreign key constraints if the table to which the constraint relates to is also included in the backup. |
is_full_cluster |
New in v20.1: Whether the backup is of a full cluster or not. |
Example
Show a backup
> SHOW BACKUP 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
database_name | table_name | start_time | end_time | size_bytes | rows | is_full_cluster
----------------+----------------------------+------------+----------------------------------+------------+------+------------------
system | users | NULL | 2020-04-08 14:38:45.288266+00:00 | 99 | 2 | true
system | zones | NULL | 2020-04-08 14:38:45.288266+00:00 | 268 | 7 | true
system | settings | NULL | 2020-04-08 14:38:45.288266+00:00 | 374 | 5 | true
system | ui | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true
system | jobs | NULL | 2020-04-08 14:38:45.288266+00:00 | 9588 | 16 | true
system | locations | NULL | 2020-04-08 14:38:45.288266+00:00 | 261 | 5 | true
system | role_members | NULL | 2020-04-08 14:38:45.288266+00:00 | 94 | 1 | true
system | comments | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true
movr | users | NULL | 2020-04-08 14:38:45.288266+00:00 | 4911 | 50 | true
movr | vehicles | NULL | 2020-04-08 14:38:45.288266+00:00 | 3182 | 15 | true
movr | rides | NULL | 2020-04-08 14:38:45.288266+00:00 | 156387 | 500 | true
movr | vehicle_location_histories | NULL | 2020-04-08 14:38:45.288266+00:00 | 73918 | 1000 | true
movr | promo_codes | NULL | 2020-04-08 14:38:45.288266+00:00 | 219973 | 1000 | true
movr | user_promo_codes | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true
(14 rows)
Show a backup with schemas
You can add number of rows and the schema of the backed up table.
> SHOW BACKUP SCHEMAS 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
database_name | table_name | start_time | end_time | size_bytes | rows | is_full_cluster | create_statement
----------------+----------------------------+------------+----------------------------------+------------+------+-----------------+----------------------------------------------------------------------------------------------------------------------------------
...
movr | users | NULL | 2020-04-08 14:38:45.288266+00:00 | 4911 | 50 | true | CREATE TABLE users (
| | | | | | | id UUID NOT NULL,
| | | | | | | city VARCHAR NOT NULL,
| | | | | | | name VARCHAR NULL,
| | | | | | | address VARCHAR NULL,
| | | | | | | credit_card VARCHAR NULL,
| | | | | | | CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| | | | | | | FAMILY "primary" (id, city, name, address, credit_card)
| | | | | | | )
movr | vehicles | NULL | 2020-04-08 14:38:45.288266+00:00 | 3182 | 15 | true | CREATE TABLE vehicles (
| | | | | | | id UUID NOT NULL,
| | | | | | | city VARCHAR NOT NULL,
| | | | | | | type VARCHAR NULL,
| | | | | | | owner_id UUID NULL,
| | | | | | | creation_time TIMESTAMP NULL,
| | | | | | | status VARCHAR NULL,
| | | | | | | current_location VARCHAR NULL,
| | | | | | | ext JSONB NULL,
| | | | | | | CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
| | | | | | | CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id),
| | | | | | | INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
| | | | | | | FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
| | | | | | | )
...
(14 rows)
Show a backup with privileges
New in v20.1: To view a list of which users and roles had which privileges on each database and table in the backup, use the WITH privileges parameter:
> SHOW BACKUP 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]' WITH privileges;
database_name | table_name | start_time | end_time | size_bytes | rows | is_full_cluster | privileges
----------------+----------------------------+------------+----------------------------------+------------+------+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
system | users | NULL | 2020-04-08 14:38:45.288266+00:00 | 99 | 2 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON users TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON users TO root;
system | zones | NULL | 2020-04-08 14:38:45.288266+00:00 | 268 | 7 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON zones TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON zones TO root;
system | settings | NULL | 2020-04-08 14:38:45.288266+00:00 | 374 | 5 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON settings TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON settings TO root;
system | ui | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON ui TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON ui TO root;
system | jobs | NULL | 2020-04-08 14:38:45.288266+00:00 | 9588 | 16 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON jobs TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON jobs TO root;
system | locations | NULL | 2020-04-08 14:38:45.288266+00:00 | 261 | 5 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON locations TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON locations TO root;
system | role_members | NULL | 2020-04-08 14:38:45.288266+00:00 | 94 | 1 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON role_members TO admin; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON role_members TO root;
system | comments | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true | GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON comments TO admin; GRANT SELECT ON comments TO public; GRANT DELETE, GRANT, INSERT, SELECT, UPDATE ON comments TO root;
movr | users | NULL | 2020-04-08 14:38:45.288266+00:00 | 4911 | 50 | true | GRANT ALL ON users TO admin; GRANT ALL ON users TO root;
movr | vehicles | NULL | 2020-04-08 14:38:45.288266+00:00 | 3182 | 15 | true | GRANT ALL ON vehicles TO admin; GRANT ALL ON vehicles TO root;
movr | rides | NULL | 2020-04-08 14:38:45.288266+00:00 | 156387 | 500 | true | GRANT ALL ON rides TO admin; GRANT ALL ON rides TO root;
movr | vehicle_location_histories | NULL | 2020-04-08 14:38:45.288266+00:00 | 73918 | 1000 | true | GRANT ALL ON vehicle_location_histories TO admin; GRANT ALL ON vehicle_location_histories TO root;
movr | promo_codes | NULL | 2020-04-08 14:38:45.288266+00:00 | 219973 | 1000 | true | GRANT ALL ON promo_codes TO admin; GRANT ALL ON promo_codes TO root;
movr | user_promo_codes | NULL | 2020-04-08 14:38:45.288266+00:00 | 0 | 0 | true | GRANT ALL ON user_promo_codes TO admin; GRANT ALL ON user_promo_codes TO root;
(14 rows)
See also
Was this helpful?
