The SYSCS_UTIL.SYSCS_RESTORE_TABLE system procedure restores a table that was previously backed up with the    SYSCS_UTIL.SYSCS_BACKUP_TABLE procedure. You can restore the table to another table on the same cluster, or on a different cluster. The table and its indexes are created anew in your database.

ENTERPRISE ONLY: This feature is available only for the Splice Machine Enterprise version of our On-Premise Database product; contact Splice Machine Sales for information.

Note that if the backup from which you are restoring was created with version (August 5, 2019) or later of Splice Machine, statistics for the table are also restored. This means that you don’t need to use analyze to generate fresh statistics for the table immediately after restoring it.

The table to which you are restoring must not already exist in the database; if it does, RESTORE_TABLE will not proceed.


                                VARCHAR destTable,
                                VARCHAR sourceSchema,
                                VARCHAR sourceTable,
                                VARCHAR directory,
                                BIGINT  backupId,
                                BOOLEAN validate );


The name of the schema to which you want the table restored.


The name of the restored table.


The name of the schema from which the table was backed up.


The name of the table that was backed up.


Specifies the path to the directory containing the backup from which you want to restore your table. This can be a local directory if you’re using the standalone version of Splice Machine, or a directory in your cluster’s file system (HDFS or MapR-FS).

Relative paths are resolved based on the current user directory. To avoid confusion, we strongly recommend that you use an absolute path when specifying the backup location.

You must specify the backup’s directory when you call this procedure because, if your database has become corrupted and needs to be restored, the data in the BACKUP.BACKUP table (which includes the location of each backup) may also be corrupted.


The ID of the backup job from which you want to restore the table.


A Boolean value that specifies whether to validate the table backup before restoring from it:

  • If validate is false, the restore proceeds without any pre-validation.
  • If validate is true, the backup is validated before the restoration is started. (See   SYSCS_UTIL.VALIDATE_TABLE_BACKUP). If the validation check finds inconsistencies, the errors are reported to the user, and the table is not restored. If the inconsistencies are minor, you can choose to re-run this procedure with validate set to false.


This procedure does not return a result.

Backup and Restore Compatibility

Note that you can only use specific restore procedures with specific types of backups. For example, you can use the RESTORE_TABLE procedure to restore from a backup created by BACKUP_TABLE, but you cannot use RESTORE_TABLE to restore from a backup created by BACKUP_DATABASE. The following table summarizes backup-restore compatibility:

If you backed up with this procedure: You can restore with these procedures:




Execute Privileges

If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this function by default. The database owner can grant access to other users.

SQL Example: Backup, Validate, and Restore a Table

This example shows you how to back up a table, then validate and restore it, in these steps:

Backing Up the Table

This command line performs a full backup of the TPCH100 LINEITEM table to the /backup directory on HDFS:

splice> CALL SYSCS_UTIL.SYSCS_BACKUP_TABLE('TPCH100', 'LINEITEM', '/backup', 'full');
FULL backup to /backup

1 row selected

See the reference page for the SYSCS_UTIL.SYSCSBACKUP_TABLE system procedure for more information about backing up a table.

Examining the Backup

After the backup completes, you can examine the sys.sysbackup table to find the ID of our new backup:

splice> SELECT * FROM sys.sysbackup;
587516417      |2018-09-25 00:12:33.896  |2018-09-25 00:42:53.546  |SUCCESS    |TABLE     |false|-1                  |3

You can use the ID of your backup job to examine the sys.sysbackupitems and verify that the base table and two indexes have been backed up:

splice> SELECT * FROM sys.sysbackupitems WHERE backup_Id=587516417 ;
587516417   |splice:292000    |2018-09-25 00:12:40.512   |2018-09-25 00:32:14.856
587516417   |splice:292033    |2018-09-25 00:12:40.513   |2018-09-25 00:42:48.573
587516417   |splice:292017    |2018-09-25 00:12:40.512   |2018-09-25 00:41:25.683

3 rows selected

Validating the Backup

Before restoring the table, you can validate the backup:

splice> CALL SYSCS_UTIL.VALIDATE_TABLE_BACKUP( 'TPCH100', 'LINEITEM', '/backup', 587516417 );
No corruptions found for backup.

1 row selected

See the reference page for the SYSCS_UTIL.VALIDATE_TABLE_BACKUP system procedure for more information about backup validation.

Restoring the Backup

You can restore the table to another table on the same cluster, or on a different cluster.

This command restores the backed-up table to table named LINEITEM in the SPLICE schema:

splice> CALL SYSCS_UTIL.SYSCS_RESTORE_TABLE('SPLICE', 'LINEITEM', 'TPCH100', 'LINEITEM', '/backup', 587516417, false);
Statement executed.

See Also