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. Only objects in the backup are dropped; existing objects that are not in the backup are kept, so the result is a merge of the backup into the database. To drop those as well, use `clean`. 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.
`clean`	Before restoring, drop every existing object of each object type being restored, whether or not it is in the backup, including objects that are not persisted; object types not being restored are left untouched. Schemas are not dropped. A data sink generated for a table monitor is dropped along with the monitor, and a home directory only along with its user. Objects created by other sessions while the restore runs are not dropped. Unlike `replace`, the clean is scoped by object type, not by name: restoring a single table drops every table in the database first, and a warning is returned when the objects to restore name specific objects of a type rather than all of them. The restore is aborted, before anything is dropped, if an object that would be dropped is still used by an object of a type that is not being restored, or cannot be dropped, such as a table locked by a backup in progress. As graphs, contexts, table monitors and SQL procedures all depend on tables, restoring tables requires restoring each of those types of which any objects exist. Run with `DRY_RUN` first to check this without dropping anything. If the restore fails after the clean, the dropped objects are not put back. System objects, the data source being restored from and its credential, the user running the restore and `admin` (with their default schemas, home directories and resource groups), and the roles of the user running the restore are never dropped, nor are the schemas holding any of these. Requires system administrator permission.
`clean_all`	Before restoring, drop every existing object in the database, including users, roles, non-system schemas and objects that are not persisted, regardless of which objects are being restored. The restore is aborted, before anything is dropped, if an object cannot be dropped, such as a table locked by a backup in progress, or is still used by an object the clean keeps; `CREATE_SCHEMA_IF_NOT_EXIST` must not be set to `false`. If the restore fails after the clean, the dropped objects are not put back. System objects, the data source being restored from and its credential, the user running the restore and `admin` (with their default schemas, home directories and resource groups), and the roles of the user running the restore are never dropped, nor are the schemas holding any of these. Requires system administrator permission.

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