> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# RESTORE BACKUP

Restores the given database objects & data from an existing *full*,
*incremental*, or *differential snapshot* within a
[backup](https://docs.kinetica.com/content/sql/backup_restore/create-backup.md#sql-backup-create); retrieving the backed-up files at the
location accessible through the given
[data source](https://docs.kinetica.com/content/sql/ddl/create-data-source.md#sql-create-data-source).

Each restored object is placed in its original *schema*.

```sql title="RESTORE BACKUP Syntax" theme={null}
RESTORE BACKUP <backup name>
DATA SOURCE <data source name>
OBJECTS (<object type> = <object name>[,...])
[WITH OPTIONS (<restore option name> = '<restore option value>'[,...])]
```

## Parameters

> [accordion] \<backup name>
>
> Name of the *backup* from which objects will be restored.

> [accordion] \<data source name>
>
> Name of the [data source](https://docs.kinetica.com/content/sql/ddl/create-data-source.md#sql-create-data-source) to use to retrieve the database
> *backup* files from the remote file store.

> [accordion] OBJECTS
>
> List of key/value pairs, each being an `object type` and an `object name` of that type, to
> restore.
>
> `object type` designations include:
>
> <table>
>   <thead>
>     <tr>
>       <th>Object Type</th>
>       <th>Description</th>
>     </tr>
>   </thead>
>
>   <tbody>
>     <tr>
>       <td>`ALL`</td>
>       <td>[Schema](https://docs.kinetica.com/content/sql/ddl/create-schema.md#sql-create-schema), and all the objects & data it contains</td>
>     </tr>
>
>     <tr>
>       <td>`CONTEXT`</td>
>       <td>[SQL-GPT context](https://docs.kinetica.com/content/sql/sqlgpt/create-context.md#sql-gpt-create-context)</td>
>     </tr>
>
>     <tr>
>       <td>`CREDENTIAL`</td>
>       <td>[Credential](https://docs.kinetica.com/content/sql/ddl/create-credential.md#sql-create-credential)</td>
>     </tr>
>
>     <tr>
>       <td>`DATA SINK`</td>
>       <td>[Data sink](https://docs.kinetica.com/content/sql/ddl/create-data-sink.md#sql-create-data-sink)</td>
>     </tr>
>
>     <tr>
>       <td>`DATA SOURCE`</td>
>       <td>[Data source](https://docs.kinetica.com/content/sql/ddl/create-data-source.md#sql-create-data-source)</td>
>     </tr>
>
>     <tr>
>       <td>`DIRECTORY`</td>
>       <td>[KiFS directory](https://docs.kinetica.com/content/sql/kifs.md)</td>
>     </tr>
>
>     <tr>
>       <td>`FUNCTION`</td>
>       <td>[UDF](https://docs.kinetica.com/content/sql/udf/create-function.md#sql-create-function)</td>
>     </tr>
>
>     <tr>
>       <td>`FUNCTION ENVIRONMENT`</td>
>       <td>[UDF environment](https://docs.kinetica.com/content/sql/udf/create-function-environment.md#sql-create-function-environment)</td>
>     </tr>
>
>     <tr>
>       <td>`GRAPH`</td>
>       <td>[Graph](https://docs.kinetica.com/content/sql/graph.md).  Source tables, if applicable, are required in order to restore it.</td>
>     </tr>
>
>     <tr>
>       <td>`PROCEDURE`</td>
>       <td>[SQL procedure](https://docs.kinetica.com/content/sql/procedure.md#sql-procedures)</td>
>     </tr>
>
>     <tr>
>       <td>`RESOURCE GROUP`</td>
>       <td>[Resource group](https://docs.kinetica.com/content/sql/resource_group/create-resource-group.md#sql-rg-create)</td>
>     </tr>
>
>     <tr>
>       <td>`ROLE`</td>
>       <td>[User role](https://docs.kinetica.com/content/sql/security/create-role.md#sql-security-role-mgmt)</td>
>     </tr>
>
>     <tr>
>       <td>`STREAM`</td>
>       <td>[Stream](https://docs.kinetica.com/content/sql/ddl/create-stream.md#sql-create-stream)</td>
>     </tr>
>
>     <tr>
>       <td>`TABLE`</td>
>       <td>[Table](https://docs.kinetica.com/content/sql/ddl/create-table.md#sql-create-table) or [view](https://docs.kinetica.com/content/sql/ddl/create-view.md#sql-create-view)</td>
>     </tr>
>
>     <tr>
>       <td>`USER`</td>
>       <td>[User](https://docs.kinetica.com/content/sql/security/create-user.md#sql-security-user-mgmt)</td>
>     </tr>
>   </tbody>
> </table>

> [accordion] WITH OPTIONS
>
> Optional indicator that a comma-delimited list of *backup* restoration option name/value
> assignments will follow.
>
> See [Options](https://docs.kinetica.com/content/sql/backup_restore/restore-backup.md#sql-backup-restore-opt) for the complete list of options.

## Options

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

> [accordion] BACKUP\_ID
>
> ID of *snapshot* from which objects will be restored.  The default is to use the most recent one.

> [accordion] CHECKSUM
>
> Verify checksums calculated during back up process before restoring.  The default (`false`) is
> to not verify checksums.

> [accordion] CREATE\_SCHEMA\_IF\_NOT\_EXIST
>
> Whether to create the [schema](https://docs.kinetica.com/content/sql/ddl/create-schema.md#sql-create-schema) containing objects being restored if it
> doesn't exist.  The default (`true`) is to create the *schema* if it doesn't exist.

> [accordion] DDL\_ONLY
>
> For any table, only restore its DDL; don't restore its data.  The default (`false`) is to
> restore data and DDL.

> [accordion] DRY\_RUN
>
> Execute a dry run of a restoration without actually restoring any files.  The default (`false`)
> is to not do a dry run.

> [accordion] RENAMED\_OBJECTS\_SCHEMA
>
> If `RESTORE_POLICY` is `rename`, each existing database objects will be relocated to this
> [schema](https://docs.kinetica.com/content/sql/ddl/create-schema.md#sql-create-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>`.
>
> > [!NOTE]
> >
> > 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.

> [accordion] 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.
>
> > [!NOTE]
> >
> > The set of skipped objects is not available to a SQL client.  Each restoration is
> > recorded in the [ki\_backup\_history](https://docs.kinetica.com/content/catalogs/kinetica.md) 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](https://docs.kinetica.com/content/api/rest/restore_backup_rest.md) instead.
>
> Other policy choices include:
>
> <table>
>   <thead>
>     <tr>
>       <th>Policy</th>
>       <th>Description</th>
>     </tr>
>   </thead>
>
>   <tbody>
>     <tr>
>       <td>`rename`</td>
>       <td>If any object being restored already exists in the database in its corresponding [schema](https://docs.kinetica.com/content/sql/ddl/create-schema.md#sql-create-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. \[!NOTE] 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.</td>
>     </tr>
>
>     <tr>
>       <td>`replace`</td>
>       <td>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`. \[!NOTE] 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.</td>
>     </tr>
>
>     <tr>
>       <td>`clean`</td>
>       <td>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.</td>
>     </tr>
>
>     <tr>
>       <td>`clean_all`</td>
>       <td>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.</td>
>     </tr>
>   </tbody>
> </table>

## 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`:

```sql RESTORE BACKUP (Schema) Example theme={null}
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`:

```sql RESTORE BACKUP (Objects w/ Rename Schema) Example theme={null}
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'
)
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.