ALTER BACKUP SCHEDULE

On this page

The ALTER BACKUP SCHEDULE statement modifies an existing backup schedule. You can use ALTER BACKUP SCHEDULE to do the following:

Set a different name for a backup schedule.
Change a scheduled backup's storage location.
Apply additional backup options or schedule options to backups and schedules.
Adjust the cadence and type of scheduled backups.

Required privileges

To alter a backup schedule, you must be the owner of the backup schedule, i.e., the user that created the backup schedule.

Synopsis

Parameters

Parameter	Description
`schedule_id`	The schedule's ID that `CREATE SCHEDULE FOR BACKUP` and `SHOW SCHEDULES` display.
`schedule_label`	The name or label given to the backup schedule.
`collectionURI`	The URI where you want to store the backup. See Backup file URLs for detail on forming the URI.
`option`	Control the backup behavior with a comma-separated list of these options.
`RECURRING crontab`	Specify when the backup should be taken. By default, these are incremental backups. 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 `ALTER BACKUP SCHEDULE`. Define the schedule as a `STRING` in crontab format. All times in UTC. Example: `'@daily'` (run daily at midnight)
`FULL BACKUP crontab / ALWAYS`	Specify when to take a new full backup. Define the schedule as a `STRING` in crontab format or as `ALWAYS`. `FULL BACKUP ALWAYS` will trigger `RECURRING` to always take full backups. Note: If you do not have an Enterprise license then you can only take full backups. `ALWAYS` is the only accepted value of `FULL BACKUP`. If you omit the `FULL BACKUP` clause, the default backup schedule will be as follows: If `RECURRING` <= 1 hour: Default to `FULL BACKUP '@daily'` If `RECURRING` <= 1 day: Default to `FULL BACKUP '@weekly'` Otherwise: Default to `FULL BACKUP ALWAYS`
`schedule_option`	Control the schedule behavior with a comma-separated list of these schedule options.
`EXECUTE [FULL] IMMEDIATELY`	Update the schedule to run immediately (at the current time). With `ALTER BACKUP SCHEDULE ... EXECUTE IMMEDIATELY` if the schedule includes full and incremental backups, regardless of which ID is specified, the incremental schedule will run. To run the full backup schedule instead, use `ALTER BACKUP SCHEDULE ... EXECUTE FULL IMMEDIATELY`. You can only use `EXECUTE [FULL] IMMEDIATELY` with active schedules. You need to resume a paused schedule before it will execute. Use `PAUSE SCHEDULE` following the schedule run to pause the schedule once again.

Backup options

You can use the backup options in this table to control the behavior of your backups. See Apply different options to scheduled backups for an example.

Option	Value	Description
`revision_history`	`BOOL` / None	Create a backup with full revision history, 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 `BACKUP` or `CREATE SCHEDULE FOR BACKUP`. A value is required when using `ALTER BACKUP SCHEDULE` to apply different options to scheduled backups.
`encryption_passphrase`	`STRING`	The passphrase used to encrypt the files (`BACKUP` manifest and data files) that the `BACKUP` statement generates. This same passphrase is needed to decrypt the file when it is used to restore and to list the contents of the backup when using `SHOW BACKUP`. There is no practical limit on the length of the passphrase.
`detached`	`BOOL` / 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 job creation 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 example. To check on the job status, use the `SHOW JOBS` 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 Take Locality-restricted backups for usage and reference detail.
`kms`	`STRING`	The URI of the cryptographic key stored in a key management service (KMS), or a comma-separated list of key URIs, used to take and restore encrypted backups. Refer to URI Formats. The key or keys are used to encrypt the manifest and data files that the `BACKUP` statement generates and to decrypt them during a restore operation, and to list the contents of the backup when using `SHOW BACKUP`. AWS KMS, Google Cloud KMS, and Azure Key Vault are supported.
`incremental_location`	`STRING`	Create an incremental backup in a different location than the default incremental backup location. `WITH incremental_location = 'explicit_incrementals_URI'` See Incremental backups with explicitly specified destinations for usage.

Schedule options

You can use the schedule options in this table to control the behavior of your backup schedule. See Apply different options to scheduled backups for an example.

Option	Value	Description
`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 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`.
`updates_cluster_last_backup_time_metric`	N/A	(`admin` privileges required) When set during backup schedule creation, this option updates the `schedules_backup_last_completed_time` metric for the scheduled backup.

Examples

The examples in this section start with the following created backup schedule. Each section follows on from the previous example's schedule state.

First, create a schedule that will take daily full backups of the cluster:

CREATE SCHEDULE aws_backups
  FOR BACKUP INTO 'external://s3_storage'
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now', ignore_existing_backups;

This statement specifies:

'external://s3_storage': Use the storage location represented by this external connection URI.
first_run = 'now': Take the first full backup immediately rather than wait for its next RECURRING time.
ignore_existing_backups: Ignore any existing backups already present in the storage location.

The command returns the following output. Note that the detached option is implicitly added, because this backup has been configured to run on a schedule:

     schedule_id     |   label     | status |           first_run           | schedule |                    backup_stmt
---------------------+-------------+--------+-------------------------------+----------+----------------------------------------------------
  814155335856521217 | aws_backups | ACTIVE | 2022-11-15 16:48:10.667767+00 | @daily   | BACKUP INTO 'external://s3_storage' WITH detached
(1 row)

Change the storage location for scheduled backups

You can change the storage location to which your backup schedule is taking backups with the SET INTO command. Use the schedule ID to specify the schedule to modify and the new storage location URI. This statement also changes the schedule's label to match the change in backup location:

ALTER BACKUP SCHEDULE 814155335856521217 SET INTO 'external://gcs_storage', SET LABEL gcs_backups;

     schedule_id     |    label    | status |       first_run        | schedule |                    backup_stmt
---------------------+-------------+--------+------------------------+----------+-----------------------------------------------------
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached
(1 row)

Note:

Incremental backups require a full backup in the storage location. Therefore, when you change the storage location for a backup schedule, CockroachDB will pause any scheduled incremental backups until the next full backup runs on its regular schedule cadence. Consider that if you change the storage location and then adjust the frequency of your scheduled backups before the next full backup, any newly added incremental backups will not be part of the pause after a storage location change. This could result in a reported error state for the incremental backups, which will not resolve until the next scheduled full backup.

Adjust frequency of scheduled backups

To adjust the frequency of your scheduled backups, use SET with FULL BACKUP and RECURRING for full and incremental backups. You can either define the frequency as a STRING or in crontab format. See the Parameters table for more detail.

The following command adds incremental backups to the schedule occurring hourly:

ALTER BACKUP SCHEDULE 814155335856521217 SET FULL BACKUP '@daily', SET RECURRING '@hourly';

The output shows the two scheduled jobs:

     schedule_id     |    label    | status |       first_run        | schedule |                         backup_stmt
---------------------+-------------+--------+------------------------+----------+---------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE | 2022-11-15 18:00:00+00 | @hourly  | BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached
(2 rows)

You can use the SHOW SCHEDULE statement with one of the schedule IDs to show details for each of the jobs. For the full backup job:

SHOW SCHEDULE 814155335856521217;

This shows that the full backup has a dependent schedule, which lists the incremental backup's schedule ID:

          id         |    label    | schedule_status |        next_run        | state | recurrence | jobsrunning | owner |            created            |                                                                                       command
---------------------+-------------+-----------------+------------------------+-------+------------+-------------+-------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  814155335856521217 | gcs_backups | ACTIVE          | 2022-11-18 00:00:00+00 | NULL  | @daily     |           0 | root  | 2022-11-15 16:48:10.667783+00 | {"backup_statement": "BACKUP INTO 'external://gcs_storage' WITH detached, "dependent_schedule_id": 814168045421199361}
(1 row)

For the incremental backup:

SHOW SCHEDULE 814168045421199361;

The following includes the backup_type as 1. This signifies that this schedule is for an incremental backup:

          id         |    label    | schedule_status |        next_run        | state | recurrence | jobsrunning | owner |            created            |                                                                       command
---------------------+-------------+-----------------+------------------------+-------+------------+-------------+-------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE          | 2022-11-15 19:00:00+00 | NULL  | @hourly    |           0 | root  | 2022-11-15 17:52:49.327263+00 | {"backup_statement": "BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached", "backup_type": 1, "dependent_schedule_id": 814155335856521217}
(1 row)

Full backups are implicitly of backup_type 0, and so does not display in the schedule details.

Apply different options to scheduled backups

You can modify the behavior of your backup schedule and the backup jobs with SET SCHEDULE OPTION and SET WITH. See the Schedule options table and the Backup options table for a list of the available options.

This statement changes the default wait value for the on_previous_running schedule option to start. If a previous backup started by the schedule is still running, the scheduled job will now start the new backup anyway, rather than waiting. The backup option incremental_location modifies the storage location for incremental backups:

ALTER BACKUP SCHEDULE 814168045421199361 SET SCHEDULE OPTION on_previous_running = 'start', SET WITH incremental_location = 'external://gcs_incremental_storage';

The incremental backup schedule's BACKUP statement shows that it will read files in the full backup location 'external://gcs_storage' and ultimately store the incremental backup in 'external://gcs_incremental_storage' on an hourly basis:

     schedule_id     |    label    | status |       first_run        | schedule |                                                        backup_stmt
---------------------+-------------+--------+------------------------+----------+----------------------------------------------------------------------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE | 2022-11-15 21:00:00+00 | @hourly  | BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached, incremental_location = 'external://gcs_incremental_storage'
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached, incremental_location = 'external://gcs_incremental_storage'
(2 rows)

Execute a schedule immediately

You can alter an active schedule to run an incremental schedule immediately with EXECUTE IMMEDIATELY:

ALTER BACKUP SCHEDULE 814168045421199361 EXECUTE IMMEDIATELY;

To run the full backup schedule instead, specify FULL:

ALTER BACKUP SCHEDULE 814168045421199361 EXECUTE FULL IMMEDIATELY;

The schedule must be active to use EXECUTE [FULL] IMMEDIATELY. You need to resume a paused schedule before it will execute. Use PAUSE SCHEDULE following the schedule run to pause the schedule once again.

Pricing

Contact us

Sign In