CREATE SCHEDULE FOR BACKUP

The CREATE SCHEDULE FOR BACKUP creates a schedule for periodic .

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

Required privileges

Starting in v22.2, CockroachDB introduces a new that provides finer control over a user’s privilege to work with the database, including taking backups.

There is continued support for the legacy privilege model for backups in v22.2, however it will be removed in a future release of CockroachDB. We recommend implementing the new privilege model that follows in this section for all new and existing backups. You can the BACKUP privilege to a user or role depending on the type of backup:

Backup	Privilege
Cluster	Grant a user the BACKUP. For example, GRANT SYSTEM BACKUP TO user;.
Database	Grant a user the BACKUP privilege on the target database. For example, GRANT BACKUP ON DATABASE test\_db TO user;.
Table	Grant a user the BACKUP privilege at the table level. This gives the user the privilege to back up the schema and all user-defined types that are associated with the table. For example, GRANT BACKUP ON TABLE test\_db.table TO user;.

The listed privileges do not cascade to objects lower in the schema tree. For example, if you are granted database-level BACKUP privileges, this does not give you the privilege to back up a table. If you need the BACKUP privilege on a database to apply to all newly created tables in that database, use . You can add BACKUP to the user or role’s default privileges with .

You can grant the BACKUP privilege to a user or role without the SELECT privilege on a table. As a result, these users will be able to take backups, but they will not be able to run a SELECT query on that data directly. However, these users could still read this data indirectly, by restoring it from any backups they produce.

Members of the can run all three types of backups (cluster, database, and table) without the need to grant a specific BACKUP privilege. However, we recommend using the BACKUP privilege model to create users or roles and grant them BACKUP privileges as necessary for stronger access control.

Privileges for managing a backup job

To manage a backup job with , , or , users must have at least one of the following:

• Be a member of the .
• The .

To view a backup job with , users must have at least one of the following:

• The , which allows you to view all jobs (including admin -owned jobs).
• Be a member of the .
• The .

See for detail on granting privileges to a role or user.

Required privileges using the legacy privilege model

The following details the legacy privilege model that CockroachDB supports in v22.2 and earlier. Support for this privilege model will be removed in a future release of CockroachDB:

• can only be run by members of the . By default, the root user belongs to the admin role.
• For all other backups, the user must have on all objects being backed up. Database backups require CONNECT privileges, and table backups require SELECT privileges. Backups of user-defined schemas, or backups containing user-defined types, require USAGE privileges.

See the Required privileges section for the updated privilege model.

Destination privileges

You can grant a user the EXTERNALIOIMPLICITACCESS .

Either the EXTERNALIOIMPLICITACCESS system-level privilege 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 . You also need to ensure that the permissions at your storage destination are configured for the operation. See for a list of the necessary permissions that each bulk operation requires.

Synopsis

CREATE SCHEDULE [IF NOT EXISTS] <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

[, ...]
DATABASE  [, ...]

Parameters

Parameter	Description
IF NOT EXISTS	Use to specify that a scheduled backup should not be created if the label already exists. Produces an error if the schedule label already exists, or if the label is not specified.
	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.
targets	The targets you want to back up: cluster [ ] database DATABASE <database_name> [, ...] table TABLE <table_pattern> [, ...]
table\_pattern	The or you want to back up.
database\_name	The name of the 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 , 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. A separate schedule may be created automatically to write full backups at a regular cadence, depending on the frequency of the incremental backups. You can likewise modify this separate schedule with . The schedule is specified as a 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 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. 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 hour and <= 1 day: Default to FULL BACKUP '@weekly' Otherwise: Default to FULL BACKUP ALWAYS
WITH SCHEDULE OPTIONS schedule\_option	Control the schedule behavior with a comma-separated list of these options.

For schedules that include both , CockroachDB will create two schedules (one for each type). See Incremental backup schedules for more information.

Backup options

Option	Value	Description
revision\_history	/ None	Create a backup with full , which records every change made to the cluster within the garbage collection period leading up to and including the given timestamp. You can specify a backup with revision history without any value e.g., WITH revision\_history. Or, you can explicitly define WITH revision\_history = 'true' / 'false'. The revision\_history option defaults to true when used with or CREATE SCHEDULE FOR BACKUP. A value is required when using ALTER BACKUP SCHEDULE to .
encryption\_passphrase		The passphrase used to (BACKUP manifest and data files) that the BACKUP statement generates. This same passphrase is needed to decrypt the file when it is used to and to list the contents of the backup when using . There is no practical limit on the length of the passphrase.
detached	/ None	Note: Backups running on a schedule have the detached option applied implicitly. Therefore, you cannot modify this option for scheduled backups. When a backup runs in detached mode, it will execute asynchronously. The job ID will be returned after the backup completes. Note that with detached specified, further job information and the job completion status will not be returned. For more on the differences between the returned job data, see the . To check on the job status, use the statement.
	EXECUTION LOCALITY	Key-value pairs	Restricts the execution of the backup to nodes that match the defined locality filter requirements. For example, WITH EXECUTION LOCALITY = 'region=us-west-1a,cloud=aws'. Refer to for usage and reference detail.
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 . The key or keys are used to encrypt the manifest and data files that the BACKUP statement generates and to decrypt them during a operation, and to list the contents of the backup when using . AWS KMS, Google Cloud KMS, and Azure Key Vault are supported.
incremental\_location		Create an incremental backup in a different location than the default incremental backup location. WITH incremental\_location = 'explicit\_incrementals\_URI' See for usage. Warning: The incremental\_location parameter is deprecated as of v25.4 and will be removed in a future release. Until the parameter is removed, you can continue to work with existing backups that use a custom incremental\_location.

Schedule options

Option	Value	Description
first\_run	/ 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 . 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 is 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. The option affects backups started by the full backup schedule only. Incremental backups are always set to wait.
ignore\_existing\_backups	N/A	If backups were already created in the that the new schedule references, this option must be passed to acknowledge that the new schedule may be backing up different objects.
updates\_cluster\_last\_backup\_time\_metric	N/A	( required) When set during backup schedule creation, this option updates the metric for the scheduled backup.

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., ) and alerting metrics that will confirm that your backups are completing, but also that they’re not running more concurrently than you expect.
• 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.

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 means that you can run scheduled backups at a cadence independent from the of the data. This is unlike non-scheduled backups that are tightly coupled to the GC TTL. See 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 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 for usage information.

We recommend monitoring your backup schedule to alert for failed backups:

• See the page for a general overview and list of metrics available for backup, scheduled backup, and restore jobs.
• See for metrics and monitoring backup schedules specifically.

Incremental backup schedules

The incremental backup schedule is created in a paused state, and is only un-paused on completion of the first, scheduled full backup. This ensures that the first incremental backup is only executed once it has a full backup to build a chain from. Thereafter, the incremental backups are scheduled to run at its specified cadence.

Incremental backups always append to the latest, complete full backup. An incremental backup can run concurrently with a full backup, but in such a situation it will continue to append to the previous full backup that has already completed.

An incremental backup will always wait for another incremental backup started by the same schedule to complete before running. This prevents incremental backups from backing up overlapping spans of time in the same backup chain. To enforce this, backup schedules created or altered using the on_previous_running option will have the full backup schedule created with the user specified option, but will always default the incremental backup schedule option to on_previous_running = wait.

View and control backup schedules

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

Action	SQL Statement
View the schedule
Pause the schedule
Resume the schedule
Drop the schedule
Alter the schedule

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:

Action	SQL Statement
View the backup status
Pause the backup
Resume the backup
Cancel the backup

You can also visit the 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

To schedule full backups of clusters, databases, or tables, use the FULL BACKUP ALWAYS clause, for example:

> CREATE SCHEDULE core_schedule_label
  FOR BACKUP INTO 's3://test/schedule-test-core?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now';

     schedule_id     |        name         | status |         first_run         | schedule |                                                                                       backup_stmt
---------------------+---------------------+--------+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  588799238330220545 | core_schedule_label | ACTIVE | 2020-09-11 00:00:00+00:00 | @daily   | BACKUP INTO 's3://test/schedule-test-core?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
(1 row)

Create a scheduled backup for a cluster

This example creates a schedule for a cluster backup with revision history that’s taken every day at midnight:

> CREATE SCHEDULE schedule_label
  FOR BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    WITH revision_history
    RECURRING '@daily';

     schedule_id     |     name       |                     status                     |            first_run             | schedule |                                                                               backup_stmt
---------------------+----------------+------------------------------------------------+----------------------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------
  588796190000218113 | schedule_label | PAUSED: Waiting for initial backup to complete | NULL                             | @daily   | BACKUP INTO LATEST IN 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
  588796190012702721 | schedule_label | ACTIVE                                         | 2020-09-10 16:52:17.280821+00:00 | @weekly  | BACKUP INTO 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)

Because the FULL BACKUP clause is not included, CockroachDB also scheduled a full backup to run @weekly. This is the default cadence for incremental backups RECURRING > 1 hour but <= 1 day.

Create a scheduled backup for a database

This example creates a schedule for a backup of the database movr with revision history that’s taken every day 1 minute past midnight (00:00:01):

> CREATE SCHEDULE schedule_database
  FOR BACKUP DATABASE movr INTO 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    WITH revision_history
    RECURRING '1 0 * * *';

     schedule_id     |       name        |                     status                     |            first_run             | schedule  |                                                                           backup_stmt
---------------------+-------------------+------------------------------------------------+----------------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------
  588819866656997377 | schedule_database | PAUSED: Waiting for initial backup to complete | NULL                             | 1 0 * * * | BACKUP DATABASE movr INTO LATEST IN 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
  588819866674233345 | schedule_database | ACTIVE                                         | 2020-09-10 18:52:42.823003+00:00 | @weekly   | BACKUP DATABASE movr INTO 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)

Because the FULL BACKUP clause is not included, CockroachDB also scheduled a full backup to run @weekly. This is the default cadence for incremental backups RECURRING > 1 hour but <= 1 day.

You will encounter an error if you run multiple to the same storage URI. Each collection’s URI must be unique.

Create a scheduled backup for a table

This example creates a schedule for a backup of the table movr.vehicles with revision history that’s taken every hour:

> CREATE SCHEDULE schedule_table
  FOR BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    WITH revision_history
    RECURRING '@hourly';

     schedule_id     |       name     |                     status                     |            first_run             | schedule |                                                                             backup_stmt
---------------------+----------------+------------------------------------------------+----------------------------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------
  588820615348027393 | schedule_table | PAUSED: Waiting for initial backup to complete | NULL                             | @hourly  | BACKUP TABLE movr.vehicles INTO LATEST IN 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
  588820615382302721 | schedule_table | ACTIVE                                         | 2020-09-10 18:56:31.305782+00:00 | @daily   | BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)

Because the FULL BACKUP clause is not included, CockroachDB also scheduled a full backup to run @daily. This is the default cadence for incremental backups RECURRING <= 1 hour.

Create a scheduled backup with a scheduled first run

This example creates a schedule for a backup of the table movr.vehicles with revision history that’s taken every hour, with its first run scheduled for 2020-09-15 00:00:00.00 (UTC):

> CREATE SCHEDULE scheduled_first_run
  FOR BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    WITH revision_history
    RECURRING '@hourly'
    WITH SCHEDULE OPTIONS first_run = '2020-09-15 00:00:00.00';

     schedule_id     |        name         |                     status                     |         first_run         | schedule |                                                                                backup_stmt
---------------------+---------------------+------------------------------------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------
  589963390457741313 | scheduled_first_run | PAUSED: Waiting for initial backup to complete | NULL                      | @hourly  | BACKUP TABLE movr.vehicles INTO LATEST IN 's3://test/scheduled-first-run?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
  589963390487363585 | scheduled_first_run | ACTIVE                                         | 2020-09-15 00:00:00+00:00 | @daily   | BACKUP TABLE movr.vehicles INTO 's3://test/scheduled-first-run?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)

Because the FULL BACKUP clause is not included, CockroachDB also scheduled a full backup to run @daily. This is the default cadence for incremental backups RECURRING <= 1 hour.

Create a scheduled backup with schedule options

This example creates a schedule for a cluster backup with the on_previous_running option:

> CREATE SCHEDULE schedule_option
  FOR BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    RECURRING '@daily'
    WITH SCHEDULE OPTIONS on_previous_running = 'start';

     schedule_id     |      label      |                     status                     |          first_run           | schedule |                                                         backup_stmt
---------------------+-----------------+------------------------------------------------+------------------------------+----------+------------------------------------------------------------------------------------------------------------------------------
  866226603264475137 | schedule_option | PAUSED: Waiting for initial backup to complete | NULL                         | @daily   | BACKUP INTO LATEST IN 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
  866226603270635521 | schedule_option | ACTIVE                                         | 2023-05-18 14:56:20.39198+00 | @weekly  | BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
(2 rows)

The schedule starts a new backup, even if the previous one is still running because the user specified option for on_previous_running = 'start'. The incremental backup remains PAUSED until the initial full backup is complete.

Because the FULL BACKUP clause is not included, CockroachDB also schedules a full backup to run @daily. This is the default cadence for incremental backups RECURRING <= 1 hour.

View scheduled backup details

When a , 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 .
• SHOW BACKUP FROM subdirectory IN collectionURI statement to .
• Use the in the to view a list of created backup schedules and their individual details.

For more details, see .

See also