RESTORE DATABASE

Restore from a Local or Network Drive

RESTORE [DATABASE] <database_name> [AS <new_db_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> [AS <new_db_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> [AS <new_db_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> [AS <new_db_name>]
FROM AZURE "container/blob-prefix" 
CREDENTIALS <credentials_json> 
[CONFIG <configuration_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

Restore from a Connection Link

RESTORE [DATABASE] <database_name> [AS <new_db_name>]
FROM LINK [link_database_name.]connection_name "backup_path";

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 set up to require a role. The role needs to be alive for the entire restore process and will result in an assume_role on every S3 API call.

• 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.

• As an alternative to using a RESTORE ... FROM S3 statement where you specify the CONFIG and CREDENTIALS clauses, you can use the RESTORE ... FROM LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

• You can restore a database to an HTTPS S3 target with an unverified SSL certificate by using the option: CONFIG '{"verify_ssl": false}' .

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.

• As an alternative to using a RESTORE ... FROM S3 statement where you specify the CONFIG and CREDENTIALS clauses, you can use the RESTORE ... FROM LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

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.

As an alternative to using a RESTORE ... FROM GCS statement where you specify the CONFIG and CREDENTIALS clauses, you can use the RESTORE ... FROM LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

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.

• As an alternative to using a RESTORE ... FROM AZURE statement where you specify the CONFIG clause, you can use the RESTORE ... FROM LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

Restore from a Connection Link

• The RESTORE.. FROM LINK command restores a database from the specified container using a connection link. To use this command, you only need to know the connection link name, not the connection details and configuration.

• This command supports S3, Azure, and GCS connections only.

Restore into an Unlimited Storage Database

RESTORE ... ON <object_store_settings> restores a local storage database into an unlimited storage database. For more information on local and unlimited storage databases, see Local and Unlimited Database Storage Concepts.

Restore an Incremental Backup

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

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);

Restore Using a Connection Link

The following example restores the database db1, from the bucket at the specified path, using an S3 connection link S3_link stored in the database db2. Ensure that the connection link S3_link already exists in db2. Refer to CREATE_LINK for details on creating a connection link.

RESTORE DATABASE db1 FROM LINK db2.S3_link "backup_path/backups/6_1_2020"

Note: db1 is the database to be restored while db2 is the database of the connection link S3_link.

Restore into an Unlimited Storage Database

See Migrate a Local Storage Database to an Unlimited Storage Database for an example.