Database Backup/Restore

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:

See Backing Up & Restoring the Cluster 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.

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>'[,...])]
ParametersDescription
CREATEKeyword 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.
<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 snapshot to create.

Snapshot type designations include:

Snapshot TypeDescription
fullCreate a full snapshot of the specified database objects. This is the default when CREATE is specified.
incrementalCreate 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.
differentialCreate 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.

Important

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 TypeDescription
ALLSchema, and all the objects & data it contains
CONTEXTSQL-GPT context
CREDENTIALCredential
DATA SINKData sink
DATA SOURCEData source
PROCEDURESQL procedure
ROLEUser role
STREAMStream
TABLETable or view
USERUser
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.

OptionDescription
CHECKSUMCalculate and store a checksum of all backed-up objects. The default (false) is to not create checksums.
COMMENTComment 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.

Note

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_BACKUPSFor 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_RUNExecute 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_KEEPMaximum 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.
RECREATEFor 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 backup_schema with a full snapshot of schema example_backup, copying backup files to a target via data source backup_ds:

CREATE BACKUP (Full Snapshot) Example 
1
2
3

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 
1
2

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 
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, 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 
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>'[,...])]
ParametersDescription
<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.
MERGEMerges all snapshots of a given backup into a single, full snapshot. An error will be reported if the backup contains only one full snapshot.
PURGEDeletes 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.

Note

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

Alterable properties include:

PropertyDescription
CHECKSUMWhether or not to calculate checksums on backed-up objects.
DDL_ONLYWhether or not to only back up DDL for any tables.
MAX_INCREMENTAL_BACKUPS_TO_KEEPMaximum 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:

OptionDescription
COMMENTA new comment for the backup that is created by a MERGE; only valid for MERGE operations.
DRY_RUNWhether 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 
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 snapshot 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 snapshots 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 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 
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>'[,...])]
ParametersDescription
<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 TypeDescription
ALLSchema, and all the objects & data it contains
CONTEXTSQL-GPT context
CREDENTIALCredential
DATA SINKData sink
DATA SOURCEData source
PROCEDURESQL procedure
ROLEUser role
STREAMStream
TABLETable or view
USERUser
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.

OptionDescription
BACKUP_IDID of snapshot from which objects will be restored. The default is to use the most recent one.
CHECKSUMVerify checksums calculated during back up process before restoring. The default (false) is to not verify checksums.
CREATE_SCHEMA_IF_NOT_EXISTWhether 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_ONLYFor any table, only restore its DDL; don't restore its data. The default (false) is to restore data and DDL.
DRY_RUNExecute a dry run of a restoration without actually restoring any files. The default (false) is to not do a dry run.
RENAMED_OBJECTS_SCHEMAIf 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:

PolicyDescription
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.

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

DROP BACKUP

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

DROP BACKUP Syntax 
1
2
3

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

Name of the backup(s) to delete that are accessible via the data sink given by <data sink name>.

Use * at the end of the <backup name> to delete all backups that start with <backup name>.

Use * instead of <backup name> to delete all backups.

Note

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

<data sink name>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:

OptionDescription
DELETE_ALL_BACKUPSWhether 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_RUNWhether 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_EXISTSWhether 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 
1
2

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 security:

DROP BACKUP (All Matching Backups at Data Sink) Example 
1
2
3

DROP BACKUP security*
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 
1
2
3

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 
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.

ParametersDescription
<backup name>

Name of the backup(s) to show that are accessible via the data source given by <data source name>.

Use * at the end of the <backup name> to show all backups that start with <backup name>.

Use * instead of <backup name> to show all backups.

<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 whose name starts with security that are accessible via the restore_ds data source:

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

SHOW BACKUP security*
DATA SINK = 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 
1
2

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 
1
2

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

Listing options:

Note

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

  • 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
ParametersDescription
<backup name>

Name of the backup(s) to describe that are accessible via the data source given by <data source name>.

Use * at the end of the <backup name> to describe all backups that start with <backup name>.

Use * instead of <backup name> to describe all backups.

<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 whose name starts with security that are accessible via the restore_ds data source:

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

DESCRIBE BACKUP security*
DATA SINK = 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 
1
2

DESCRIBE BACKUP *
DATA SOURCE = restore_ds