SingleStore DB

RESTORE DATABASE

Restores a database from a binary backup file.

Refer to Backing Up and Restoring Data for additional information.

Syntax
Restore from a Local or Network Drive
RESTORE [DATABASE] <database_name> FROM "backup_path" [WITH FILE incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)]

sync_options:
    WITH {SYNC | ASYNC} DURABILITY
  | WITH {SYNC | ASYNC} REPLICATION
  | WITH {SYNC | ASYNC} DURABILITY {SYNC | ASYNC} REPLICATION
Restore From S3
RESTORE [DATABASE] <database_name> FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH FILE incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)]

configuration_json:
'{"region":"your_region",
  "endpoint_url":"http://other_endpoint"
  [, "x-amz-server-side-encryption-customer-algorithm":"<encryption_type>",
     "x-amz-server-side-encryption-customer-key":"<encrypted_or_unencrypted_key>",
     "x-amz-server-side-encryption-customer-key-MD5":"<key>"
  ]
}'

credentials_json:
'{"aws_access_key_id": "replace_with_your_access_key_id",
  "aws_secret_access_key": "replace_with_your_secret_access_key",
  ["aws_session_token": "replace_with_your_temp_session_token",]
  ["role_arn":"replace_with_your_role_arn"]
}'

sync_options:
    WITH {SYNC | ASYNC} DURABILITY
  | WITH {SYNC | ASYNC} REPLICATION
  | WITH {SYNC | ASYNC} DURABILITY {SYNC | ASYNC} REPLICATION
Restore From an S3 Compatible Storage Provider
RESTORE [DATABASE] <database_name> FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH FILE incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)]

configuration_json:
'{"endpoint_url":"http://storage_provider_endpoint"}'

credentials_json:
'{"aws_access_key_id": "replace_with_your_access_key_id",
  "aws_secret_access_key": "replace_with_your_secret_access_key"
}'

sync_options:
    WITH {SYNC | ASYNC} DURABILITY
  | WITH {SYNC | ASYNC} REPLICATION
  | WITH {SYNC | ASYNC} DURABILITY {SYNC | ASYNC} REPLICATION
Restore From Google Cloud Storage
RESTORE [DATABASE] <database_name> FROM GCS "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH FILE incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)]

configuration_json:
'{}'

credentials_json:
'{"access_id": "replace_with_your_google_access_key",
  "secret_key": "replace_with_your_google_secret_key"
}'

sync_options:
    WITH {SYNC | ASYNC} DURABILITY
  | WITH {SYNC | ASYNC} REPLICATION
  | WITH {SYNC | ASYNC} DURABILITY {SYNC | ASYNC} REPLICATION

See the remarks for details on the aws_access_key_id and aws_secret_access_key.

Restore from Azure blobs
RESTORE [DATABASE] <database_name> FROM AZURE "container/blob-prefix" CREDENTIALS credentials_json [WITH FILE incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)]

credentials json:
'{"account_name": "your_account_name_here",
  "account_key": "your_account_key_here"
}'

sync_options:
    WITH {SYNC | ASYNC} DURABILITY
  | WITH {SYNC | ASYNC} REPLICATION
  | WITH {SYNC | ASYNC} DURABILITY {SYNC | ASYNC} REPLICATION
Arguments
  • <database_name> is the SingleStore DB database to restore to. This database doesn’t need to have the same number of partitions or leaves as the source cluster from the backup. This database may also have a different redundancy level so long as it is using a Network File System.

  • link_database_name is the database that stores the connection link connection_name.

  • resource_pool_name is optional. It is the name of the resource pool where the restore operation runs.

  • If you wish to restore the backup of one database into a differently-named database, you can do so by specifying the full path to the old database’s .backup file in backup_path for local backups or path for S3 or prefix-key for Azure blobs. For example, you can do BACKUP DATABASE db TO './path/' followed by RESTORE DATABASE newdb FROM './path/db.backup'. See the Examples section for examples.

  • WITH {SYNC | ASYNC} REPLICATION specifies, following the database restore, whether high availability, redundancy-2 replication will be done synchronously or asynchronously. Synchronous replication from the master partitions will complete on all replicas before the commit of the transaction is acknowledged to the client application. If {SYNC | ASYNC} REPLICATION is not specified, the replication setting from the database specified in the backup_path or bucket/path, or container/blob-prefix is used.

    See Replication and Durability Concepts for more information.

  • WITH {SYNC | ASYNC} DURABILITY specifies, following the database restore, whether in-memory database updates you make using DDL and DML commands are also saved to the log on disk synchronously or asynchronously. Synchronous updates to the log on disk will complete before the commit of the transaction is acknowledged to the client application. If WITH {SYNC | ASYNC} DURABILITY is not specified, the durability setting from the database specified in the backup_path or bucket/path, or container/blob-prefix is used.

    See Replication and Durability Concepts for more information.

  • You cannot specify WITH SYNC DURABILITY ASYNC REPLICATION.

  • [WITH FILE = incr_backup_id]restores an incremental backup with the incr_backup_id that was assigned to the backup when it was taken.

Remarks
  • The RESTORE command replays a binary backup file in the same manner in which SingleStore DB recovers a database snapshot during startup.

  • A RESTORE operation temporarily puts the database being restored into the recovering snapshot state. A database in this state cannot be queried. When the RESTORE command finishes, it puts the database into the online state. See Database for more information.

  • This command must be run on the master aggregator node. See Node Requirements for SingleStore DB Commands for more information.

  • You can restore to S3, Azure, or GCS using a connection link. However, you require the SHOW LINK permission, provided by your administrator, to use a connection link.

Warning

SingleStore does not support restoring database backups from a newer version of SingleStore into an older version.

Local Restore Remarks
  • backup_path is the path used in BACKUP DATABASE .

  • The path backup_path needs to be accessible by the memsqld process. Paths are resolved relative to the memsqlbin/data directory.

  • You can include / at the front of the backup_path to override the default and use an absolute path.

Restore from S3
  • aws_session_token is optional. This is only needed if your AWS account uses the AWS Security Token Service.

  • role_arn is optional. This is only needed if your AWS security is setup to require a role.

  • endpoint_url is optional.

  • You should not specify the compatibility_mode setting, as you would when restoring from Google Cloud Storage using the S3 interface.

Restore From an S3-Compatible Storage Provider
  • RESTORE ... FROM S3 with the endpoint_url set to the URL of the S3 compatible storage provider restores an S3 compatible backup from the storage provider.

  • aws_access_key_id is optional. It is the access key id for accessing the storage provider.

  • aws_secret_access_key is optional. It is the secret access key for accessing the storage provider.

  • You should not specify the compatibility_mode setting, as you would when restoring from Google Cloud Storage using the S3 interface.

Restore From Google Cloud Storage
  • The CONFIG clause may optionally specify an endpoint_url.

  • The CREDENTIALS clause is required

  • We support only HMAC keys

  • The CREDENTIALS clause should be a JSON object with two fields:

access_id: usually a 24 or 60 character alphanumeric string, which is linked to the Google account, typically all uppercase and starts with GOOG.

secret_key: usually a 40 character Base-64 encoded string that is linked to a specific access_id.

Restore from Azure
  • The time limit given to download a file from Azure is 30 minutes. This should be sufficient for most files at most download speeds. If that is not sufficient to download a file, you may get an error saying truncated tar archive, in which case you may want to check your connection and retry the operation.

Examples
Restore an Incremental Backup

The Backing Up and Restoring Data topic provides an example of restoring an incremental backup.

Notice

Incremental backups cannot be restored from a local file system.

All of the examples shown below restore full backups.

Restore from a Local Drive

The following example restores from the /var/lib/memsql/data/ directory.

RESTORE DATABASE memsql_demo FROM "./";
Restore from S3

The following example restores from an S3 bucket.

RESTORE DATABASE memsql_demo FROM S3 "backup_bucket/backups/6_1_2018"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"replace_with_your_access_key_id","aws_secret_access_key":"replace_with_your_secret_access_key"}';

The following example restores a database(memsql_demo) from an S3 bucket to a database with a different name(new_memsql_demo).

RESTORE DATABASE new_memsql_demo FROM S3 "backup_bucket/backups/6_1_2018/memsql_demo.backup"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"replace_with_your_access_key_id","aws_secret_access_key":"replace_with_your_secret_access_key"}';

The following example demonstrates the CONFIG clause for restoring from SSE-C encrypted buckets.

--- For Server-Side Encryption with Customer-Provided Encryption Keys (SSE-C) ---
CONFIG '{"region":"us-east-1", "endpoint_url":"<target_endpoint_url>",
"x-amz-server-side-encryption-customer-algorithm":"AES256",
"x-amz-server-side-encryption-customer-key":"<key>",
"x-amz-server-side-encryption-customer-key-MD5":"<key>"}'
Restore From Google Cloud Storage using the S3 Interface

The following example uses the S3 interface to restore from a Google Cloud Storage bucket.

RESTORE DATABASE memsql_demo FROM S3 "backup_bucket/backups/12_15_2019"
  CONFIG '{"compatibility_mode":true,"endpoint_url":"https://storage.googleapis.com"}'
  CREDENTIALS '{"aws_access_key_id":"replace_with_your_google_access_key","aws_secret_access_key":"replace_with_your_google_secret_key"}';
Restore from Azure

The following example restores from an Azure Container.

RESTORE DATABASE memsql_demo FROM AZURE "backup_container/backups/6_1_2018" CREDENTIALS '{"account_name":"your_account_name_here","account_key": "your_account_key_here"}';

The following example restores a database(memsql_demo) from an Azure container to a database with a different name(new_memsql_demo).

RESTORE DATABASE new_memsql_demo FROM AZURE "backup_container/backups/6_1_2018/memsql_demo.backup" CREDENTIALS '{"account_name":"your_account_name_here","account_key": "your_account_key_here"}';
Restore with sync durability and sync replication

The following example restores from the local disk with sync durability and sync replication.

RESTORE DATABASE memsql_demo FROM "./" WITH SYNC DURABILITY SYNC REPLICATION;
Restore Using a Resource Pool

The following example restores a database from /my-backup-dir, using an existing resource pool, general.

RESTORE DATABASE memsql_demo FROM "/my-backup-dir" (OPTION resource_pool = general);
Common Errors

A common error is giving incorrect paths to the backup files to restore command. The path that you give to the RESTORE command should match the path that you gave to the BACKUP command, unless you are restoring to a database with a different name in S3 or Azure restore, in which case, /old_database_name.backup should be appended to your restore path.