SYSCS_UTIL.SYSCS_RESTORE_DATABASE

The SYSCS_UTIL.SYSCS_RESTORE_DATABASE system procedure restores your database to the state it was in when a specific backup was performed, using a backup that you previously created using either the   SYSCS_UTIL_SYSCS_SCHEDULE_DAILY_BACKUP system procedure.

You can restore your database from any previous full or incremental backup.

There are several important things to know about restoring your database from a previous backup:

  • Restoring a database wipes out your database and replaces it with what had been previously backed up.
  • You cannot use your cluster while restoring your database.
  • You must reboot your database after the restore is complete. See the Starting Your Database topics in this book for instructions on restarting your database.

When you restore from a backup, Splice Machine automatically determines and runs whatever sequence of restores may be required to accomplish the restoration of your database; this means that when you select an incremental backup from which to restore, Splice Machine will detect that it needs to first restore from the previous full backup and then apply any incremental restorations.

Syntax

SYSCS_UTIL.SYSCS_RESTORE_DATABASE( VARCHAR backupDir,
                                   BIGINT backupId );

backupDir

Specifies the path to the directory containing the backup from which you want to restore your database. 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.

backupId

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

The system Backing Up and Restoring topic for more information.

Usage

Restoring you database can take a while, and has several major implications:

There are several important things to know about restoring your database from a previous backup:

  • Restoring a database wipes out your database and replaces it with what had been previously backed up.
  • You cannot use your cluster while restoring your database.
  • You must reboot your database after the restore is complete by first Starting Your Database.

As noted at the top of this topic: if you are restoring from an incremental backup, you must first restore from the most recent full backup, and then incrementally restore from each subsequent incremental backup. See Example 2 below.

Results

This procedure does not return a result.

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.

Examples

The following example first queries the system backup table to find the ID of the backup from which we want to restore, and then initiates the restoration.

Stop using your database before backing up, and keep in mind that restoring a database may take several minutes, depending on the size of your database.

splice> SELECT * FROM SYS.SYSBACKUP;
BACKUP_ID |BEGIN_TIMESTAMP          |END_TIMESTAMP            |STATUS    |FILESYSTEM      |SCOPE |INCR&|INCREMENTAL_PARENT_&|BACKUP_ITEM
----------------------------------------------------------------------------------------------------------------------------------------
74101     |2015-11-30 17:46:41.431  |2015-11-30 17:46:56.664  |S         |./dbBackups/    |D     |true |40975               |30
40975     |2015-11-25 09:32:53.04   |2015-11-25 09:33:09.081  |S         |~/splicemachine |D     |false|-1                  |93

2 rows selected

splice> CALL SYSCS_UTIL.SYSCS_RESTORE_DATABASE('./dbBackups/', 74101);
Statement executed.

Once the restoration is complete, reboot your database by the Starting Your Database.

See Also