Database Backup/Restore - Kinetica Docs

Kinetica provides a SQL interface for performing hot backups via full, incremental, & differential snapshots 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
DROP BACKUP
SHOW BACKUP
DESCRIBE BACKUP

See Backing Up/Restoring Kinetica for details on backing up and restoring data.

CREATE BACKUP

Creates a new full, incremental, or differential snapshot of the given database objects and data; storing the backup files at the location accessible through the given data sink.

CREATE BACKUP <backup name>
DATA SINK = <data sink name>
[OBJECTS (<object type> = <object name>[,...])]
[WITH OPTIONS (<backup option name> = '<backup option value>'[,...])]

Parameters

CREATE

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

Name of the backup to create.

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

TYPE

Type of snapshot to create.Snapshot type designations include:

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

OBJECTS

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

The OBJECTS clause should only be specified for full snapshots. Both incremental & differential snapshots will operate on the objects specified during the original full snapshot 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`	Data sink
`DATA SOURCE`	Data source
`FUNCTION`	UDF
`FUNCTION ENVIRONMENT`	UDF environment
`PROCEDURE`	SQL procedure
`RESOURCE GROUP`	Resource group
`ROLE`	User role
`STREAM`	Stream
`TABLE`	Table or view
`USER`	User

WITH OPTIONS

Optional indicator that a comma-delimited list of backup option name/value assignments will follow.See Options for the complete list of options.

Options

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

CHECKSUM

Calculate and store a checksum of all backed-up objects. The default (false) is to not create checksums.

COMMENT

Comment to associate with the snapshot.

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.

In this mode, subsequent incremental & differential snapshot attempts will return a warning and create no additional snapshot if only data has changed since the last snapshot.

DELETE_INTERMEDIATE_BACKUPS

For differential snapshot types only, delete all prior intermediate differential & incremental snapshots between the last full snapshot and this one for the given backup name. The default (false) is to not delete intermediate snapshots.

DRY_RUN

Execute a dry run of a back up 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 snapshots to keep for the given backup name; if creating this incremental snapshot will cause the total number to exceed this maximum, the oldest incremental snapshot will be removed. The default is unlimited.

RECREATE

For full snapshot types only, replace the existing backup 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 backup named schema_backup with a full snapshot of schema example_backup, copying backup files to a target via data source backup_ds:

CREATE BACKUP (Full Snapshot) Example

CREATE BACKUP schema_backup
DATA SINK = backup_ds
OBJECTS (ALL = example_backup)

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

CREATE BACKUP (Incremental Snapshot) Example

BACKUP schema_backup
DATA SINK = backup_ds

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

CREATE BACKUP (Differential Snapshot) Example

BACKUP schema_backup
TYPE = 'differential'
DATA SINK = backup_ds

ALTER BACKUP

Any of the following facets of a backup can be altered, via a given data sink:

Backup snapshots can be merged into a single full snapshot.
Backup snapshots can be individually deleted
Backup options can be set

ALTER BACKUP Syntax

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

Name of the backup to alter.

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

MERGE

Merges all snapshots of a given backup into a single, full snapshot. An error will be reported if the backup contains only one full snapshot.

PURGE

Deletes the snapshot identified with backup id. Only applicable to incremental and differential snapshots.

SET

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

These settings will affect future snapshots of the given backup; existing snapshots 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 snapshots 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 snapshots of a backup into a single full snapshot and add a comment to the merged backup:

MERGE Backup Example

ALTER BACKUP schema_backup
DATA SINK = backup_ds
MERGE
WITH OPTIONS (COMMENT = 'Merged last 30 days of backups')

To purge a given snapshot by ID number:

PURGE Backup Example

ALTER BACKUP schema_backup
DATA SINK = backup_ds
PURGE 1654321012345

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

SET Backup Properties Example

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 snapshot within a backup; retrieving the backed-up files at the location accessible through the given data source. Each restored object is placed in its original schema.

RESTORE BACKUP Syntax

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

Parameters

Name of the backup from which objects will be restored.

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`	Data sink
`DATA SOURCE`	Data source
`FUNCTION`	UDF
`FUNCTION ENVIRONMENT`	UDF environment
`PROCEDURE`	SQL procedure
`RESOURCE GROUP`	Resource group
`ROLE`	User role
`STREAM`	Stream
`TABLE`	Table or view
`USER`	User

WITH OPTIONS

Optional indicator that a comma-delimited list of backup restoration option name/value assignments will follow.See Options for the complete list of options.

Options

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

BACKUP_ID

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

CHECKSUM

Verify checksums calculated during back up process before restoring. The default (false) is to not 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, each existing database objects will be relocated to this schema if a restored database object would otherwise collide with it. If not specified, the schema 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 schema_backup, copying data from a source via data source restore_ds:

RESTORE BACKUP (Schema) Example

RESTORE BACKUP schema_backup
DATA SOURCE = restore_ds
OBJECTS (ALL = example_backup)

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

RESTORE BACKUP (Objects w/ Rename Schema) Example

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'
)

DROP BACKUP

Deletes one or more backups and all associated files from the remote store accessible through the given data sink.

DROP BACKUP Syntax

DROP BACKUP < <backup name> | "<backup name>*" | * >
DATA SINK <data sink name>
[WITH OPTIONS (<delete option name> = '<delete option value>'[,...])]

Parameters

Name of the backup(s) to delete that are accessible via the data sink given by data sink name.Use <backup name>* to delete all backups that start with backup name.Use * instead of <backup name> to delete all backups.

The DELETE_ALL_BACKUPS option must be set to true when using *.

Name of the data sink to use to delete the database backup files from the remote file store.

WITH OPTIONS

Optional indicator that a comma-delimited list of deletion option name/value assignments will follow.Delete options include:

Option	Description
`DELETE_ALL_BACKUPS`	Whether or not to allow multiple backups to be deleted if `` is used to specify more than one backup; default (`false`) is to prevent deleting multiple backups*.
`DRY_RUN`	Whether or not to execute a dry run of a backup deletion and not actually delete the backup files; default (`false`) is to delete backups.
`NO_ERROR_IF_NOT_EXISTS`	Whether or not to suppress an error if the given backup does not exist; default (`false`) is to return an error.

Examples

For example, to delete a backup, schema_backup, via a data sink, backup_ds, through which the backup files can be accessed:

DROP BACKUP Example

DROP BACKUP schema_backup
DATA SINK = backup_ds

To delete all backups accessible via the backup_ds data sink that have a backup name starting with conf:

DROP BACKUP (All Matching Backups at Data Sink) Example

DROP BACKUP "conf*"
DATA SINK = backup_ds
WITH OPTIONS (DELETE_ALL_BACKUPS = true)

To delete all backups accessible via the backup_ds data sink:

DROP BACKUP (All Backups at Data Sink) Example

DROP BACKUP *
DATA SINK = backup_ds
WITH OPTIONS (DELETE_ALL_BACKUPS = true)

SHOW BACKUP

Outputs the DDL statement used to initially create one or more backups accessible via the given data source.

SHOW BACKUP Syntax

SHOW BACKUP < <backup name> | "<backup name>*" | * >
DATA SOURCE <data source name>

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

Parameters

Name of the backup(s) to show that are accessible via the data source given by data source name.Use <backup name>* to show all backups that start with backup name.Use * instead of <backup name> to show all backups.

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

SHOW BACKUP schema_backup
DATA SOURCE = restore_ds

To output the DDL for all backups whose name starts with conf that are accessible via the restore_ds data source:

SHOW BACKUP (All Backups with Prefix at Data Source) Example

SHOW BACKUP "conf*"
DATA SOURCE = restore_ds

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

SHOW BACKUP (All Backups at Data Source) Example

SHOW BACKUP *
DATA SOURCE = restore_ds

DESCRIBE BACKUP

Outputs the detail of snapshots of one or more backups accessible via the given data source.

DESCRIBE BACKUP Syntax

DESC[RIBE] BACKUP < <backup name> | "<backup name>*" | * >
DATA SOURCE <data source name>

Parameters

Name of the backup(s) to describe that are accessible via the data source given by <data source name>.Use <backup name>* to describe all backups that start with backup name.Use * instead of <backup name> to describe all backups.

Name of the data source to use to retrieve the database backup file information from the remote file store.

Response

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

Output Column	Description
`BACKUP_NAME`	Unique name for the backup file set
`BACKUP_ID`	Unique ID of the snapshot within the backup
`BACKUP_TYPE`	Type of the snapshot; see CREATE BACKUP for values
`TIME`	Timestamp at which the snapshot was initiated
`DURATION`	Duration, in milliseconds, of the snapshot processing
`CLUSTER_ID`	Identifier of the cluster backed-up, within an HA ring
`HOST_NAME`	Hostname of the server on which the snapshot was created
`HOST_ID`	IP address of the server on which the snapshot 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 snapshot
`FILES`	Number of backed-up data files contained within the snapshot
`RECORDS`	Number of data records contained within the snapshot
`COMMENT`	Comment associated with the snapshot

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

DESCRIBE BACKUP schema_backup
DATA SOURCE = restore_ds

To output the detail for all backups whose name starts with conf that are accessible via the restore_ds data source:

DESCRIBE BACKUP (All Backups with Prefix at Data Source) Example

DESCRIBE BACKUP "conf*"
DATA SOURCE = restore_ds

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

DESCRIBE BACKUP (All Backups at Data Source) Example

DESCRIBE BACKUP *
DATA SOURCE = restore_ds

​CREATE BACKUP

​Parameters

​Options

​Examples

​ALTER BACKUP

​Parameters

​Examples

​RESTORE BACKUP

​Parameters

​Options

​Examples

​DROP BACKUP

​Parameters

​Examples

​SHOW BACKUP

​Parameters

​Examples

​DESCRIBE BACKUP

​Parameters

​Response

​Examples

CREATE BACKUP

Parameters

Options

Examples

ALTER BACKUP

Parameters

Examples

RESTORE BACKUP

Parameters

Options

Examples

DROP BACKUP

Parameters

Examples

SHOW BACKUP

Parameters

Examples

DESCRIBE BACKUP

Parameters

Response

Examples