Use Managed-Service Backups

Your cluster's Backups page displays a list of your full and incremental cluster backups. Use the calendar drop-down to view all backups taken on a certain date.

For each backup, the following details display:

• Data From: The date and time the backup was taken.
• Type: Whether the backup is a full or incremental backup.
• Size: The size of the backup, measured in KiB.
• Expires In: The remaining number of days Cockroach Labs will retain the backup.
• Databases: The number of databases included in the backup.

Your cluster's Backups page displays a list of your full cluster backups. Use the calendar drop-down to view all backups taken on a certain date.

For each backup, the following details display:

• Data From: The date and time the backup was taken.
• Status: Whether the backup is In Progress or Complete.
• Expires In: The remaining number of days Cockroach Labs will retain the backup.

Databases

To view the databases included in the backup, click the number in the Databases column on the cluster view of the Backups page.

For each database in the backup, the following details display:

• The Name of the database.

• The Size of the database data captured in the backup.

  Note:

  If the Size listed for a database in an incremental backup is 0 B, it means no changes were made in the database since the last full backup.

• The number of Tables in the database.

  To view the tables in the database, click the number in the Tables column.

To restore a database, click Restore in the corresponding row.

Tables

To view the tables in a database, click the number in the Tables column on the Databases page.

For each table in the database, the following details display:

• The Name of the table.

• The Size of the table data captured in the backup.

  Note:

  If the Size listed for a table in an incremental backup is 0.00 B, it means no changes were made in the table since the last full backup.

• The number of Rows captured in the backup.

Incomplete Backups

To view any failed or pending backups, click the Incomplete Backups tab on your cluster's Backups page.

For each incomplete backup, the following details display:

• Started: The date and time the backup job began.
  
• Duration: The amount of time the backup job ran for.
  
• Status: The error code and message for failed backup jobs.
• Description: The SQL command corresponding to the failed or pending backup job.

Ways to restore data

Org Administrators can perform the following from the Console:

• Restore a cluster
• Restore a database
• Restore a table

Additional ways to restore data:

• Back up a self-hosted CockroachDB cluster and restore into a CockroachDB Cloud cluster
• Back up and restore data manually

Restore a cluster

To restore a cluster:

1. Find the cluster backup on the Backups page.

2. Click Restore for the cluster you want to restore.

   The Restore cluster module displays with backup details.

3. Click Continue.

   Warning:

   The restore will completely erase all data in the cluster. All cluster data will be replaced with the data from the backup.

4. Once you have reviewed the restore details, click Restore.

   The Restore Jobs tab will show you the status of your restore and update when the restore job has been created successfully.

Restore a database

To restore a database:

1. Find the cluster backup containing the database you want to restore, and click the number in the corresponding Databases column.

2. In the Databases view, click Restore for the database you want to restore.

   The Restore database module displays with backup details.

3. In the Restore to field, enter the name of the destination database. For multi-region databases on v21.2, see Restore a multi-region database.

   Note:

   Resolve any naming conflicts by using DROP or RENAME on the existing database. If you enter a unique name in the Restore to field, a new database will be created.

4. Select any of the Dependency options to skip. You can:

   • Skip missing foreign keys, which will remove missing foreign key constraints (i.e., when the referenced table is not in the backup or is not being restored) before restoring.
   • Skip missing sequences, which will ignore sequence dependencies (i.e., the DEFAULT expression that uses the sequence).
   • Skip missing views, which will skip restoring views that cannot be restored because their dependencies are not being restored at the same time.

5. Click Continue.

6. Once you have reviewed the restore details, click Restore.

   When the restore job has been created successfully, you will be taken to the Restore Jobs tab, which will show you the status of your restore.

When the restore is complete, be sure to set any database-specific zone configurations and, if applicable, grant privileges.

Restore a multi-region database to a new database in v21.2

1. Find the cluster backup containing the database you want to restore, and click the number in the corresponding Databases column.

2. In the Databases view, click Restore for the database you want to restore.

   The Restore database module displays with backup details.

3. In the Restore to field, enter the name of the destination database. You can only restore to the same database name as the backed-up database. Therefore, you need to DROP or RENAME your existing database before restoring.

   To restore a multi-region database you have backed up to a different database name, you can use the following procedure. (In this example, the original, backed-up database is movr and the new database is new_movr.)

   In the SQL shell, create a new database named new_movr:

   CREATE DATABASE new_movr;
   

   Add the regions that are in the backup of movr to your new database. The database regions in your new database must match the regions of the backed-up database (movr in this example). You must:

   • Ensure the databases have the same primary region.
   • Add the regions to the new database in the same region order as the backed-up database.

   To verify the regions in your backed-up database, use SHOW REGIONS:

   SHOW REGIONS FROM DATABASE movr;
   

   If the backed-up database has a primary region of us-east1, and then you had added us-west1 followed by us-west2 to the database, you must add regions to the new database in the same order:

   ALTER DATABASE new_movr SET PRIMARY REGION "us-east1";
   

   ALTER DATABASE new_movr ADD region "us-west1";
   

   ALTER DATABASE new_movr ADD region "us-west2";
   

4. From the Console, go to the Backups page:

   1. Choose the backup you want to restore.
   2. Click on the number of tables in the database. You will find a list of all the tables contained in the database's backup.
   3. Click Restore for each table you want to restore into the new database.
   4. Provide the new database's name (e.g., new_movr) in Restore to for the Destination database name.

   For more detail on "matching" regions, see Restoring to multi-region databases.

5. Select any of the Dependency options to skip. You can:

   • Skip missing foreign keys, which will remove missing foreign key constraints (i.e., when the referenced table is not in the backup or is not being restored) before restoring.
   • Skip missing sequences, which will ignore sequence dependencies (i.e., the DEFAULT expression that uses the sequence).
   • Skip missing views, which will skip restoring views that cannot be restored because their dependencies are not being restored at the same time.

6. Click Continue

7. Once you have reviewed the restore details, click Restore.

When the restore job has been created successfully, you will be taken to the Restore Jobs tab, which will show you the status of your restore.

When the restore is complete, be sure to set any database-specific zone configurations and, if applicable, grant privileges.

Restore a table

To restore a table:

1. Find the cluster backup containing the table you want to restore, and click the number in the corresponding Databases column.

2. In the Databases view, find the database containing the table you want to restore, and click the number in the corresponding Tables column.

   The Tables view displays.

3. Click Restore for the table you want to restore.

   The Restore table module displays with backup details.

4. In the Restore to field, enter the name of the destination database.

   Note:

   If you enter the name of an existing database, the table will be restored into that existing database. To use the name of an existing database, first resolve any naming conflicts by using DROP or RENAME on the existing database. If you enter a unique name in the Restore to field, a new database will be created.

5. Select any of the Dependency options to skip. You can:

   • Skip missing foreign keys, which will remove missing foreign key constraints (i.e., when the referenced table is not in the backup or is not being restored) before restoring.
   • Skip missing sequences, which will ignore sequence dependencies (i.e., the DEFAULT expression that uses the sequence).
   • Skip missing views, which will skip restoring views that cannot be restored because their dependencies are not being restored at the same time.

6. Click Continue.

7. Once you have reviewed the restore details, click Restore.

When the restore job has been created successfully, you will be taken to the Restore Jobs tab, which will show you the status of your restore.

Back up a self-hosted CockroachDB cluster and restore into a CockroachDB Cloud cluster

To back up a self-hosted CockroachDB cluster into a CockroachDB Cloud cluster:

1. While connected to your self-hosted CockroachDB cluster, back up your databases and/or tables to an external location:

   BACKUP DATABASE example_database INTO 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

   Warning:

   If you are backing up the data to AWS or GCP, use the specified option for the AUTH parameter, as CockroachDB Cloud will need the specified credentials upon RESTORE. For more information on authentication parameters to cloud storage providers, see Cloud Storage Authentication.

2. Connect to your CockroachDB Cloud cluster:

   Mac Linux Windows

   cockroach sql --url 'postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name>-ca.crt'
   

   cockroach sql --url 'postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name>-ca.crt'
   

   cockroach sql --url "postgresql://<user>@<cluster-name>-<short-id>.<region>.cockroachlabs.cloud:26257/<database>?sslmode=verify-full&sslrootcert=$env:appdata\CockroachCloud\certs\$<cluster-name>-ca.crt"
   

   Where:

   • <user> is the SQL user. By default, this is your CockroachDB Cloud account username.
   • <cluster-name>-<short-id> is the short name of your cluster plus the short ID. For example, funny-skunk-3ab.
   • <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.
   • <region> is the region in which your cluster is running. If you have a multi-region cluster, you can choose any of the regions in which your cluster is running. For example, aws-us-east-1.
   • <database> is the name for your database. For example, defaultdb.

   You can find these settings in the Connection parameters tab of the Connection info dialog.

3. Restore to your CockroachDB Cloud cluster.

   Use SHOW BACKUPS with your external location to find the backup's subdirectory:

   SHOW BACKUPS IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

           path
   ------------------------
   2021/03/23-213101.37
   2021/03/24-172553.85
   2021/03/24-210532.53
   (3 rows)
   

   Use the subdirectory to specify the backup to restore:

   RESTORE DATABASE example_database FROM '2021/03/23-213101.37' IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

Troubleshooting

Resolve a database naming conflict

The databases you want to restore cannot have the same name as an existing database in the target cluster. Before you restore a database, verify that the database name is not already in use. To do this, connect to the target cluster with the CockroachDB SQL client and run the following:

> SHOW DATABASES;

If the database's name is already in use, either drop the existing database:

> DROP DATABASE example_database;

Or change the existing database's name:

> ALTER DATABASE example_database RENAME TO archived_example_database;

Resolve a table naming conflict

The table you want to restore cannot have the same name as an existing table in the target database. Before you restore a table, verify that the table name is not already in use. To do this, connect to the target cluster with the CockroachDB SQL client and run the following:

> SHOW TABLES FROM database_name;

If the table's name is already in use, either drop the existing table:

> DROP TABLE target_database.example_table;

Or change the existing table's name:

> ALTER TABLE target_database.example_table RENAME TO target_database.archived_example_table;