title	summary	toc
CREATE SCHEDULE FOR BACKUP	The CREATE SCHEDULE FOR BACKUP statement creates a schedule for periodic backups.	true

New in v20.2: The CREATE SCHEDULE FOR BACKUP statement creates a schedule for periodic backups.

For more information about creating, managing, monitoring, and restoring from a scheduled backup, see Manage a Backup Schedule.

{{site.data.alerts.callout_info}} Core users can only use backup scheduling for full backups of clusters, databases, or tables.

To use the other backup features, you need an Enterprise license. {{site.data.alerts.end}}

Required privileges

Only members of the admin role can run CREATE SCHEDULE FOR BACKUP. By default, the root user belongs to the admin role.
BACKUP requires full read and write (including delete and overwrite) permissions to its target destination.

Synopsis

CREATE SCHEDULE <label>
FOR BACKUP [<targets>] INTO <location>
[WITH <backup_options>[=<value>] [, ...]]
RECURRING [crontab] [FULL BACKUP <crontab|ALWAYS>]
[WITH SCHEDULE OPTIONS <schedule_option>[= <value>] [, ...] ]

Targets:
   Empty targets list: backup full cluster.
   TABLE <table_pattern> [, ...]
   DATABASE <database_name> [, ...]

Parameters

Parameter | Description ----------------------------------------+------------------------------------------------------------------------------------------------------------------------- label | The name used to identify the backup schedule. This is optional and does not need to be unique. If not provided, the schedule will be assigned the name BACKUP. table_pattern | The table(s) or view(s) you want to back up. database_name | The name of the database(s) you want to back up (i.e., create backups of all tables and views in the database). location | The URI where you want to store the backup. The backup files will be stored in year > month > day subdirectories. The location can be cloud storage, or nodelocal.

Note: If you want to schedule a backup using temporary credentials, we recommend that you use implicit authentication; otherwise, you'll need to drop and then recreate schedules each time you need to update the credentials. backup_options | Control the backup behavior with a comma-separated list of options. RECURRING crontab | Specifies when the backup should be taken. By default, these are incremental backups that capture changes since the last backup and append to the current full backup. The schedule is specified as a STRING in crontab format. All times in UTC.

Example: '@daily' (run daily at midnight) FULL BACKUP crontab | Specifies when to take a new full backup. The schedule is specified as a STRING in crontab format or as ALWAYS.

If FULL BACKUP ALWAYS is specified, then the backups triggered by the RECURRING clause will always be full backups. For free users, ALWAYS is the only accepted value of FULL BACKUP.

If the FULL BACKUP clause is omitted, CockroachDB will default to the following full backup schedule:

RECURRING <= 1 hour: Default to FULL BACKUP '@daily'
RECURRING <= 1 day: Default to FULL BACKUP '@weekly'
Otherwise: Default to FULL BACKUP ALWAYS

WITH SCHEDULE OPTIONS schedule_option | Experimental feature. Control the schedule behavior with a comma-separated list of these options.

{{site.data.alerts.callout_info}} For schedules that include both full and incremental backups, CockroachDB will create two schedules (one for each type). {{site.data.alerts.end}}

Backup options

{% include {{ page.version.version }}/backups/backup-options.md %}

Schedule options

{{site.data.alerts.callout_danger}} This is an experimental feature. Its interface, options, and outputs are subject to change, and there may be bugs.

If you encounter a bug, please file an issue. {{site.data.alerts.end}}

Option | Value | Description ----------------------------+-----------------------------------------+------------------------------ first_run | TIMESTAMPTZ / now | Execute the schedule at the specified time in the future. If not specified, the default behavior is to execute the schedule based on its next RECURRING time. on_execution_failure | retry / reschedule / pause | If an error occurs during the backup execution, do the following:

retry: Retry the backup right away.
reschedule: Retry the backup by rescheduling it based on the RECURRING expression.
pause: Pause the schedule. This requires manual intervention to resume the schedule.

Default: reschedule on_previous_running | start / skip / wait | If the previous backup started by the schedule is still running, do the following:

start: Start the new backup anyway, even if the previous one still running.
skip: Skip the new backup and run the next backup based on the RECURRING expression.
wait: Wait for the previous backup to complete.

Default: wait ignore_existing_backups | N/A | If backups were already created in the destination that the new schedule references, this option must be passed to acknowledge that the new schedule may be backing up different objects.

Considerations

We recommend that you schedule your backups at a cadence that your cluster can keep up with; for example, if a previous backup is still running when it is time to start the next one, adjust the schedule so the backups do not end up falling behind or update the on_previous_running option.
To prevent scheduled backups from falling behind, first determine how long a single backup takes and use that as your starting point for the schedule's cadence.
Ensure you are monitoring your backup schedule (e.g., Prometheus) and alerting metrics that will confirm that your backups are completing, but also that they're not running more concurrently than you expect.
Ensure that your GC window is long enough to accommodate your backup schedule, otherwise your incremental backups will throw an error. For example, if you set up your schedule to be RECURRING '@daily' but your GC window is less than 1 day, all your incremental backups will fail.
The AS OF SYSTEM TIME clause cannot be set on scheduled backups. Scheduled backups are started shortly after the scheduled time has passed by an internal polling mechanism and are automatically run with AS OF SYSTEM TIME set to the time at which the backup was scheduled to run.
If you want to schedule a backup using temporary credentials, we recommend that you use implicit authentication; otherwise, you'll need to drop and then recreate schedules each time you need to update the credentials.

View and control backup schedules

Once a backup schedule is successfully created, you can do the following:

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 do the following with each individual backup job:

You can also visit the Jobs page of the DB Console to view job details. The BACKUP statement will return when the backup is finished or if it encounters an error.

Examples

Create a schedule for full backups only (core)

Core users can only use backup scheduling for full backups of clusters, databases, or tables. Full backups are taken with the FULL BACKUP ALWAYS clause, for example: