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

# Manage a Backup Schedule

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

You can create schedules in CockroachDB for periodic backups. Once a [backup schedule is created](#create-a-new-backup-schedule), you can do the following:

* [Set up monitoring for the backup schedule](#set-up-monitoring-for-the-backup-schedule)
* [View scheduled backup details](#view-scheduled-backup-details)
* [View and control the backup schedule](#view-and-control-the-backup-schedule)
* [View and control a backup initiated by a schedule](#view-and-control-a-backup-initiated-by-a-schedule)
* [Restore from a scheduled backup](#restore-from-a-scheduled-backup)

## Supported products

The feature described on this page is available in **CockroachDB Basic**, **CockroachDB Standard**, **CockroachDB Advanced**, and **CockroachDB self-hosted** clusters when you are running <InternalLink version="cockroachcloud" path="take-and-restore-self-managed-backups">self-managed backups</InternalLink>. For a full list of features, refer to <InternalLink path="backup-and-restore-overview#backup-and-restore-support">Backup and restore product support</InternalLink>.

## Considerations

### Protected timestamps and scheduled backups

Scheduled backups ensure that the data to be backed up is protected from garbage collection until it has been successfully backed up. This active management of <InternalLink path="architecture/storage-layer#protected-timestamps">protected timestamps</InternalLink> means that you can run scheduled backups at a cadence independent from the <InternalLink path="configure-replication-zones">GC TTL</InternalLink> of the data. This is unlike non-scheduled backups that are tightly coupled to the GC TTL. See <InternalLink path="take-full-and-incremental-backups#garbage-collection-and-backups">Garbage collection and backups</InternalLink> for more detail.

The data being backed up will not be eligible for garbage collection until a successful backup completes. At this point, the schedule will release the existing protected timestamp record and write a new one to protect data for the next backup that is scheduled to run. It is important to consider that when a scheduled backup fails there will be an accumulation of data until the next successful backup. Resolving the backup failure or <InternalLink path="drop-schedules">dropping the backup schedule</InternalLink> will make the data eligible for garbage collection once again.

You can also use the `exclude_data_from_backup` option with a scheduled backup as a way to prevent protected timestamps from prolonging garbage collection on a table. See the example <InternalLink path="take-full-and-incremental-backups">Exclude a table's data from backups</InternalLink> for usage information.

### Backup collection storage URI and schedule backups

You will encounter an error if you run multiple <InternalLink path="take-full-and-incremental-backups#backup-collections">backup collections</InternalLink> to the same storage URI. Backup collections can contain multiple full and incremental backups, but each collection's URI must be unique. If you are using backup schedules, each schedule must have a unique URI.

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

## Create a new backup schedule

To create a new backup schedule, use the <InternalLink path="create-schedule-for-backup">`CREATE SCHEDULE FOR BACKUP`</InternalLink> statement. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEDULE schedule_label
  FOR BACKUP INTO 's3://test/backups/test_schedule_1?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    WITH revision_history
    RECURRING '@daily'
    WITH SCHEDULE OPTIONS first_run = 'now';
```

In this example, a schedule labeled `schedule_label` is created to take daily (incremental) backups with revision history in AWS S3, with the first backup being taken now. A second <InternalLink path="create-schedule-for-backup">schedule for weekly full backups</InternalLink> is also created by default. Both schedules have the same `label` (i.e., `schedule_label`).

For more information about the different options available when creating a backup schedule, see <InternalLink path="create-schedule-for-backup">`CREATE SCHEDULE FOR BACKUP`</InternalLink>.

<Note>
  Further guidance on connecting to Amazon S3, Google Cloud Storage, Azure Storage, and other storage options is outlined in <InternalLink path="use-cloud-storage">Use Cloud Storage</InternalLink>.
</Note>

## Set up monitoring for the backup schedule

We recommend that you <InternalLink path="prometheus-endpoint">monitor your backup schedule with Prometheus</InternalLink>, and alert when there are anomalies such as backups that have failed or no backups succeeding over a certain amount of time—at which point, you can inspect schedules by running <InternalLink path="show-schedules">`SHOW SCHEDULES`</InternalLink>.

Metrics for scheduled backups fall into two categories:

* Backup schedule-specific metrics, aggregated across all schedules:
  * `schedules.BACKUP.started`: The total number of backups started by a schedule.
  * `schedules.BACKUP.succeeded`: The number of backups started by a schedule that succeeded.
  * `schedules.BACKUP.failed`: The number of backups started by a schedule that failed.

    When `schedules.BACKUP.failed` increments, run <InternalLink path="show-schedules">`SHOW SCHEDULES`</InternalLink> to check which schedule is affected and to inspect the error in the `status` column. If a backup job encounters too many retryable errors, it will enter a <InternalLink path="show-jobs#job-status">`failed` state</InternalLink> with the most recent error, which allows subsequent backups the chance to succeed. Refer to the <InternalLink path="backup-and-restore-monitoring">Backup and Restore Monitoring</InternalLink> page for metrics to track backup failures.
  * `schedules.BACKUP.protected_age_sec`: The age of the oldest <InternalLink path="architecture/storage-layer#protected-timestamps">protected timestamp</InternalLink> record protected by backup schedules.
  * `schedules.BACKUP.protected_record_count`: The number of <InternalLink path="architecture/storage-layer#protected-timestamps">protected timestamp</InternalLink> records held by backup schedules.
* Scheduler-specific metrics:
  * `schedules.round.reschedule-wait`: The number of schedules that were rescheduled due to a currently running job. A value greater than 0 indicates that a previous backup was still running when a new scheduled backup was supposed to start. This corresponds to the <InternalLink path="create-schedule-for-backup">`on_previous_running=wait`</InternalLink> schedule option.
  * `schedules.round.reschedule-skip`: The number of schedules that were skipped due to a currently running job. A value greater than 0 indicates that a previous backup was still running when a new scheduled backup was supposed to start. This corresponds to the <InternalLink path="create-schedule-for-backup">`on_previous_running=skip`</InternalLink> schedule option.

<Note>
  `schedules.round.reschedule-wait` and `schedules.round.reschedule-skip` are gauge metrics and can be graphed. A continual positive value for either of these metrics may indicate a misconfigured backup cadence, and you should consider adjusting the cadence to avoid waiting for or skipping the next backup.
</Note>

For a tutorial on how to use Prometheus to set up monitoring and alerting, see <InternalLink path="monitor-cockroachdb-with-prometheus">Monitor CockroachDB with Prometheus</InternalLink>.

## View scheduled backup details

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 location. To view details for a backup created by a schedule, you can use the following:

* `SHOW BACKUPS IN collectionURI` statement to <InternalLink path="show-backup#view-a-list-of-the-available-full-backup-subdirectories">view a list of the full backup's subdirectories</InternalLink>.
* `SHOW BACKUP FROM subdirectory IN collectionURI` statement to <InternalLink path="show-backup#view-a-list-of-the-full-and-incremental-backups-in-a-specific-full-backup-subdirectory">view a list of the full and incremental backups that are stored in a specific full backup's subdirectory</InternalLink>.
* Use the <InternalLink path="ui-schedules-page">Schedules page</InternalLink> in the <InternalLink path="ui-overview">DB Console</InternalLink> to view a list of created backup schedules and their individual details.

For more details, see <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>.

## View and control the backup schedule

Once a backup schedule is successfully created, you can [view the schedule](#view-the-schedule), [pause the schedule](#pause-the-schedule), [resume the schedule](#resume-the-schedule), or [drop the schedule](#drop-the-schedule).

### View the schedule

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEDULES FOR BACKUP;
```

For more information, see <InternalLink path="show-schedules">`SHOW SCHEDULES`</InternalLink>.

### Pause the schedule

To pause a schedule, you can either specify the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE SCHEDULE 589963390487363585;
```

Or nest a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `PAUSE SCHEDULES` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'schedule_database';
```

For more information, see <InternalLink path="pause-schedules">`PAUSE SCHEDULES`</InternalLink>.

### Resume the schedule

To resume a paused schedule, you can either specify the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESUME SCHEDULE 589963390487363585;
```

Or nest a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `RESUME SCHEDULES` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESUME SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'schedule_database';
```

For more information, see <InternalLink path="resume-schedules">`RESUME SCHEDULES`</InternalLink>.

### Drop the schedule

To drop a schedule, you can either specify the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP SCHEDULE 589963390487363585;
```

Or nest a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `DROP SCHEDULES` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'schedule_database';
```

When `DROP SCHEDULES` removes a <InternalLink path="create-schedule-for-backup#create-a-schedule-for-full-backups-only">full backup schedule</InternalLink>, it removes the associated <InternalLink path="create-schedule-for-backup#incremental-backup-schedules">incremental backup schedule</InternalLink>, if it exists. For more information, see <InternalLink path="drop-schedules">`DROP SCHEDULES`</InternalLink>.

<Danger>
  `DROP SCHEDULE` does **not** cancel any in-progress jobs started by the schedule. Before you drop a schedule, <InternalLink path="cancel-job">cancel any in-progress jobs</InternalLink> first, as you will not be able to look up the job ID once the schedule is dropped.
</Danger>

## View and control a backup initiated by a schedule

After CockroachDB successfully initiates a scheduled backup, it registers the backup as a job. You can [view](#view-the-backup-job), [pause](#pause-the-backup-job), [resume](#resume-the-backup-job), or [cancel](#cancel-the-backup-job) each individual backup job.

### View the backup job

To view jobs for a specific <InternalLink path="create-schedule-for-backup">backup schedule</InternalLink>, use the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW JOBS FOR SCHEDULE 590204387299262465;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        job_id       | job_type |                                                                                                             description                                                                                   | statement | user_name | status  | running_status |             created              | started | finished |             modified             | fraction_completed | error | coordinator_id
---------------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+---------+----------------+----------------------------------+---------+----------+----------------------------------+--------------------+-------+-----------------
  590205481558802434 | BACKUP   | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:20:00+00:00' WITH revision_history, detached |           | root      | running | NULL           | 2020-09-15 16:20:18.347383+00:00 | NULL    | NULL     | 2020-09-15 16:20:18.347383+00:00 |                  0 |       |              0
(1 row)
```

You can also view multiple schedules by nesting a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `SHOW JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        job_id       | job_type |                                                                                                                 description                                                                                      | statement | user_name |  status   | running_status |             created              | started |             finished             |             modified             | fraction_completed | error | coordinator_id
---------------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+-----------+----------------+----------------------------------+---------+----------------------------------+----------------------------------+--------------------+-------+-----------------
  590204496007299074 | BACKUP   | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:14:44.991631+00:00' WITH revision_history, detached |           | root      | succeeded | NULL           | 2020-09-15 16:15:17.720725+00:00 | NULL    | 2020-09-15 16:15:20.913789+00:00 | 2020-09-15 16:15:20.910594+00:00 |                  1 |       |              0
  590205481558802434 | BACKUP   | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:20:00+00:00' WITH revision_history, detached        |           | root      | succeeded | NULL           | 2020-09-15 16:20:18.347383+00:00 | NULL    | 2020-09-15 16:20:48.37873+00:00  | 2020-09-15 16:20:48.374256+00:00 |                  1 |       |              0
(2 rows)
```

For more information, see <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink>.

### Pause the backup job

To pause jobs for a specific <InternalLink path="create-schedule-for-backup">backup schedule</InternalLink>, use the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS FOR SCHEDULE 590204387299262465;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE JOBS FOR SCHEDULES 1
```

You can also pause multiple schedules by nesting a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `PAUSE JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE JOBS FOR SCHEDULES 2
```

For more information, see <InternalLink path="pause-job">`PAUSE JOB`</InternalLink>.

### Resume the backup job

To resume jobs for a specific <InternalLink path="create-schedule-for-backup">backup schedule</InternalLink>, use the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESUME JOBS FOR SCHEDULE 590204387299262465;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESUME JOBS FOR SCHEDULES 1
```

You can also resume multiple schedules by nesting a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `PAUSE JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESUME JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESUME JOBS FOR SCHEDULES 2
```

For more information, see <InternalLink path="resume-job">`RESUME JOB`</InternalLink>.

### Cancel the backup job

To cancel jobs for a specific <InternalLink path="create-schedule-for-backup">backup schedule</InternalLink>, use the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CANCEL JOBS FOR SCHEDULE 590204387299262465;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CANCEL JOBS FOR SCHEDULES 1
```

You can also CANCEL multiple schedules by nesting a <InternalLink path="select-clause">`SELECT` clause</InternalLink> that retrieves `id`(s) inside the `CANCEL JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CANCEL JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CANCEL JOBS FOR SCHEDULES 2
```

For more information, see <InternalLink path="cancel-job">`CANCEL JOB`</InternalLink>.

## Restore from a scheduled backup

To restore from a scheduled backup, use the <InternalLink path="restore">`RESTORE`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> RESTORE
    FROM '2020/08/19-035600.00' IN 's3://test/backups/test_schedule_1?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    AS OF SYSTEM TIME '2020-08-19 03:50:00+00:00';
```

To view the backups stored within a collection, use the [`SHOW BACKUP`](#view-scheduled-backup-details) statement.

## See also

* <InternalLink path="create-schedule-for-backup">`CREATE SCHEDULE FOR BACKUP`</InternalLink>
* <InternalLink path="backup">`BACKUP`</InternalLink>
* <InternalLink path="restore">`RESTORE`</InternalLink>
* <InternalLink path="show-backup">`SHOW BACKUP`</InternalLink>
* <InternalLink path="show-schedules">`SHOW SCHEDULES`</InternalLink>
* <InternalLink path="pause-schedules">`PAUSE SCHEDULES`</InternalLink>
* <InternalLink path="resume-schedules">`RESUME SCHEDULES`</InternalLink>
* <InternalLink path="drop-schedules">`DROP SCHEDULES`</InternalLink>
* <InternalLink path="pause-job">`PAUSE JOB`</InternalLink>
* <InternalLink path="pause-job">`RESUME JOB`</InternalLink>
* <InternalLink path="cancel-job">`CANCEL JOB`</InternalLink>
* <InternalLink path="take-full-and-incremental-backups">Take Full and Incremental Backups</InternalLink>
* <InternalLink path="cockroach-sql">Use the Built-in SQL Client</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>