RESTORE BACKUP - Kinetica Docs

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
`DIRECTORY`	KiFS directory
`FUNCTION`	UDF
`FUNCTION ENVIRONMENT`	UDF environment
`GRAPH`	Graph. Source tables, if applicable, are required in order to restore it.
`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>.

Specify this option when restoring via SQL; the automatically generated schema name is not available to a SQL client, leaving the relocated objects difficult to find.

RESTORE_POLICY

Restoration scheme to use when restoring objects that already exist. Under the default policy, an existing object is not replaced; it is skipped & reported as failed, and the restoration continues with the remaining objects.

The set of skipped objects is not available to a SQL client. Each restoration is recorded in the ki_backup_history catalog table, with its status & the set of objects requested; compare that against the database to determine which were skipped. To retrieve the failed objects directly, use /restore/backup instead.

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. Applicable only to credentials, data sinks, data sources, table monitors, SQL procedures, tables, & non-temporary views. An object of any other type is left in place and is not restored.
`replace`	If the object being restored already exists in the database in its corresponding schema, overwrite the existing object with it. Objects with dependent objects will not be restored, as they are not able to be dropped before being restored due to those dependencies. When applied to a KiFS directory, the target directory and all its contents will be deleted before restoring the backup content to it; this includes removing files that are not part of the restored backup.

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

​Parameters

​Options

​Examples

Parameters

Options

Examples