Database Backup/Restore

Kinetica provides a SQL interface for performing full, incremental, & differential hot backups and restoration of schema objects and data. For information on full system backups, see System Backup instead.

The ability to manage backups is available using the following commands:

CREATE BACKUP
ALTER BACKUP
RESTORE BACKUP
SHOW BACKUP
DESCRIBE BACKUP

See Backing Up & Restoring the Cluster for details on backing up and restoring data.

CREATE BACKUP

Creates a new full, incremental, or differential backup of the given database objects and data; storing the backup files at the location pointed to by the given data sink.

Full Backup Syntax
1
2
3
4
CREATE BACKUP <backup name>
DATA SINK = <data sink name>
[OBJECTS (<object type> = <object name>[,...])]
[WITH OPTIONS (<backup option name> = '<backup option value>'[,...])]
Incremental Backup Syntax
1
2
3
4
BACKUP <backup name>
DATA SINK = <data sink name>
[OBJECTS (<object type> = <object name>[,...])]
[WITH OPTIONS (<backup option name> = '<backup option value>'[,...])]
Differential Backup Syntax
1
2
3
4
5
BACKUP <backup name>
DATA SINK = <data sink name>
TYPE = 'differential'
[OBJECTS (<object type> = <object name>[,...])]
[WITH OPTIONS (<backup option name> = '<backup option value>'[,...])]

Parameters Description

CREATE Keyword indicating a full backup should be created. If omitted, an incremental backup will be created, using the most recent full or incremental backup as the baseline.

<backup name> Name of the backup to create.

<data sink name> Name of the data sink to use to send the database backup files to the remote file store.

TYPE

Type of backup to create.

Backup type designations include:

Backup Type	Description
`full`	Create a full backup of the specified database objects. This is the default when `CREATE` is specified.
`incremental`	Create a backup of the changes to the specified database objects since the most recent backup of any kind was created. This is the default when `CREATE` is omitted.
`differential`	Create a backup of the changes to the specified database objects since the most recent full backup was created.

OBJECTS

List of key/value pairs, each being an object type and an object name of that type, to back up.

Important

The OBJECTS clause should only be specified for full backups. Both incremental & differential backups will operate on the objects specified during the original full backup associated with the backup name specified for them.

object type designations include:

Object Type	Description
`ALL`	Schema, and all the objects & data it contains
`CONTEXT`	SQL-GPT context
`CREDENTIAL`	Credential
`DATA SINK`	External data sink
`DATA SOURCE`	External data source
`PROCEDURE`	SQL stored procedure
`ROLE`	User roles
`STREAM`	Stream
`TABLE`	Table (or view)
`USER`	Users

WITH OPTIONS

Optional indicator that a comma-delimited list of backup option name/value assignments will follow.

See Backup Options for the complete list of options.

Backup Options

The following options can be specified to further modify a backup configuration.

Option	Description
`CHECKSUM`	Calculate and store a checksum of all backed-up objects. The default (`true`) is to create checksums.
`COMMENT`	Comment to associate with the backup.
`DDL_ONLY`	For any table, only back up its DDL; don't back up its data. The default (`false`) is to back up data and DDL. Note In this mode, subsequent incremental & differential attempts will return a warning and create no additional backup iteration if only data has changed since the last backup.
`DELETE_INTERMEDIATE_BACKUPS`	For differential backup types only, delete all prior intermediate differential & incremental backups between the last full backup and this one for the given `backup name`. The default (`false`) is to not delete intermediate backups.
`DRY_RUN`	Execute a dry run of a backup without actually backing up any files. The default (`false`) is to not do a dry run.
`MAX_INCREMENTAL_BACKUPS_TO_KEEP`	Maximum number of incremental backups to keep for the given `backup name`; if creating this incremental backup will cause the total number to exceed this maximum, the oldest incremental backup will be removed. The default is unlimited.
`RECREATE`	For full backup types only, replace the existing backup object with a new backup, if it exists. The default (`false`) is to not replace the existing backup and return an error, if one exists.

Examples

For example, to create a full backup named backup_schema of schema example_backup, copying backup files to a target via data source backup_ds:

CREATE BACKUP (Full) Example
1
2
3
CREATE BACKUP schema_backup
DATA SINK = backup_ds
OBJECTS (ALL = example_backup)

To create an incremental backup based on the previous backup of the same name:

CREATE BACKUP (Incremental) Example
1
2
BACKUP schema_backup
DATA SINK = backup_ds

To create a differential backup based on the previous full backup of the same name:

CREATE BACKUP (Differential) Example
1
2
3
BACKUP schema_backup
TYPE = 'differential'
DATA SINK = backup_ds

ALTER BACKUP

Any of the following facets of a backup can be altered:

Backup iterations can be merged into a single backup
Backup iterations can be individually deleted
Backup options can be set

ALTER BACKUP Syntax
1
2
3
4
ALTER BACKUP <backup name>
DATA SINK = <data sink name>
< MERGE | PURGE <backup id> | SET <property name> = '<property value>'[,...] >
[WITH OPTIONS (<alter option name> = '<alter option value>'[,...])]

Parameters Description

<backup name> Name of the backup to alter.

<data sink name> Name of the data sink used to alter the database backup files on the remote file store.

MERGE Merges all iterations of a given backup into a single, full backup. An error will be reported if the backup contains only one backup (no incremental or differential backups).

PURGE Deletes the backup iteration given in <backup id>. Only applicable to incremental and differential backup iterations.

SET

Sets the given list of properties to their given corresponding values.

Note

These settings will affect future backup iterations of the given backup; existing backup iterations will remain unchanged.

Alterable properties include:

Property	Description
`CHECKSUM`	Whether or not to calculate checksums on backed-up objects.
`DDL_ONLY`	Whether or not to only back up DDL for any tables.
`MAX_INCREMENTAL_BACKUPS_TO_KEEP`	Maximum number of incremental backups to keep.

WITH OPTIONS

Optional indicator that a comma-delimited list of alteration option name/value assignments will follow.

Alter options include:

Option	Description
`COMMENT`	A new comment for the backup that is created by a `MERGE`; only valid for `MERGE` operations.
`DRY_RUN`	Whether or not to execute a dry run of a backup alteration and not actually alter the backup.

Examples

For example, to merge all iterations of a backup into a single backup and add a comment to the merged backup:

MERGE Backup Example
1
2
3
4
ALTER BACKUP schema_backup
DATA SINK = backup_ds
MERGE
WITH OPTIONS (COMMENT = 'Merged last 30 days of backups')

To purge a given backup iteration by ID number:

PURGE Backup Example
1
2
3
ALTER BACKUP schema_backup
DATA SINK = backup_ds
PURGE 1654321012345

To set the maximum number of incremental backup iterations of a backup to keep to 5:

SET Backup Properties Example
1
2
3
ALTER BACKUP schema_backup
DATA SINK = backup_ds
SET MAX_INCREMENTAL_BACKUPS_TO_KEEP = 5

RESTORE BACKUP

Restores the given database objects & data from an existing full, incremental, or differential backup; retrieving the backup files at the location pointed to by the given data source.

Each restored object is placed in its original schema.

RESTORE BACKUP Syntax
1
2
3
4
RESTORE BACKUP <backup name>
DATA SOURCE <data source name>
OBJECTS (<object type> = <object name>[,...])
[WITH OPTIONS (<restore option name> = '<restore option value>'[,...])]

Parameters Description

<backup name> Name of the backup from which objects will be restored.

<data source name> Name of the data source to use to retrieve the database backup files from the remote file store.

OBJECTS

List of key/value pairs, each being an object type and an object name of that type, to restore.

object type designations include:

Object Type	Description
`ALL`	Schema, and all the objects & data it contains
`CONTEXT`	SQL-GPT context
`CREDENTIAL`	Credential
`DATA SINK`	External data sink
`DATA SOURCE`	External data source
`PROCEDURE`	SQL stored procedure
`ROLE`	User roles
`STREAM`	Stream
`TABLE`	Table (or view)
`USER`	Users

WITH OPTIONS

Optional indicator that a comma-delimited list of backup restoration option name/value assignments will follow.

See Restore Options for the complete list of options.

Restore Options

The following options can be specified to further modify a backup restoration.

Option Description

BACKUP_ID ID of backup iteration from which objects will be restored. The default is to use the most recent one.

CHECKSUM Verify checksums calculated during backup process before restoring. The default (true) is to verify checksums.

CREATE_SCHEMA_IF_NOT_EXIST Whether to create the schema containing objects being restored if it doesn't exist. The default (true) is to create the schema if it doesn't exist.

DDL_ONLY For any table, only restore its DDL; don't restore its data. The default (false) is to restore data and DDL.

DRY_RUN Execute a dry run of a restoration without actually restoring any files. The default (false) is to not do a dry run.

RENAMED_OBJECTS_SCHEMA If RESTORE_POLICY is rename, existing database objects will be relocated to this schema when restored database object would otherwise collide with them. If not specified, the name will be automatically generated, using the format: BACKUP_<UUID>

RESTORE_POLICY

Restoration scheme to use when restoring objects that already exist. The default policy is to abort the restoration and return an error.

Other policy choices include:

Policy Description

rename

If any object being restored already exists in the database in its corresponding schema, move all already-existing objects that would otherwise be overwritten by the restoration into another schema, and restore all objects to their original schema.

The name of the schema holding the relocated objects is determined by RENAMED_OBJECTS_SCHEMA, and will be created if it doesn't exist.

replace If the object being restored already exists in the database in its corresponding schema, overwrite the existing object with it.

Examples

For example, to restore the example_backup schema from a backup named backup_schema, copying data from a source via data source backup_ds:

RESTORE BACKUP (Schema) Example
1
2
3
RESTORE BACKUP schema_backup
DATA SOURCE = restore_ds
OBJECTS (ALL = example_backup)

To restore individual tables from a backup named backup_schema, restoring tables that already exist into a new schema, example_backup_restore:

RESTORE BACKUP (Objects w/ Rename Schema) Example
1
2
3
4
5
6
7
8
RESTORE BACKUP schema_backup
DATA SOURCE = restore_ds
OBJECTS (TABLE = example_backup.employee, example_backup.product)
WITH OPTIONS
(
    RESTORE_POLICY = 'rename',
    RENAMED_OBJECTS_SCHEMA = 'example_backup_restore'
)

SHOW BACKUP

Outputs the DDL statement used to initially create one or all backups.

SHOW BACKUP Syntax
1
2
SHOW BACKUP < <backup name> | * >
DATA SOURCE <data source name>

Note

The response to SHOW BACKUP is a single-column result set with the DDL statement as the value in the DDL column.

Parameters	Description
`<backup name>`	Name of the backup to show; use `` instead to output the DDL of all backups* accessible via the data source given by `<data source name>`.
`<data source name>`	Name of the data source to use to retrieve the database backup file information from the remote file store.

Examples

For example, to output the DDL for a backup, schema_backup, via a data source, restore_ds, through which the backup files can be accessed:

SHOW BACKUP Example
1
2
SHOW BACKUP schema_backup
DATA SOURCE = restore_ds

To output the DDL for all backups accessible via the restore_ds data source:

SHOW BACKUP (All Backups at Data Source) Example
1
2
SHOW BACKUP *
DATA SOURCE = restore_ds

DESCRIBE BACKUP

Outputs the detail of backup iterations of one or all backups.

DESCRIBE BACKUP Syntax
1
2
DESC[RIBE] BACKUP < <backup name> | * >
DATA SOURCE <data source name>

Listing options:

Note

The response to DESCRIBE BACKUP is a 12-column result set:

BACKUP_NAME - unique name for the backup file set
BACKUP_ID - unique ID of the backup iteration within the backup
BACKUP_TYPE - type of the backup iteration; see CREATE BACKUP for values
TIME - timestamp at which the backup iteration was initiated
DURATION - duration, in milliseconds, of the backup iteration processing
CLUSTER_ID - identifier of the cluster backed-up, within an HA ring
HOST_NAME - hostname of the server on which the backup iteration was created
HOST_ID - IP address of the server on which the backup iteration was created
OBJECTS - list of objects backed up and their respective types
SIZE - size, in bytes, of the backed-up data files contained within the backup iteration
FILES - number of backed-up data files contained within the backup iteration
RECORDS - number of data records contained within the backup iteration
COMMENT - comment associated with the backup iteration

Parameters	Description
`<backup name>`	Name of the backup to describe; use `` instead to output the detail of all backups* accessible via the data source given by `<data source name>`.
`<data source name>`	Name of the data source to use to retrieve the database backup file information from the remote file store.

Examples

For example, to output the detail for a backup, schema_backup, via a data source, restore_ds, through which the backup files can be accessed:

DESCRIBE BACKUP Example
1
2
DESCRIBE BACKUP schema_backup
DATA SOURCE = restore_ds

To output the detail for all backups accessible via the restore_ds data source:

DESCRIBE BACKUP (All Backups at Data Source) Example
1
2
DESCRIBE BACKUP *
DATA SOURCE = restore_ds