RESTORE DATABASE

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

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.

• When restoring in Helios via S3 gateway endpoint, you should just specify the bucket name without the endpoint_url . This is because a VPC endpoint is always created for S3 by default, hence all requests to S3 go internally through the AWS network.

• 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 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>"}'

The following example restores a database from an S3 bucket to a the test-workspace workspace.

RESTORE DATABASE test FROM S3 'path_to/mybucket/db.backup' 
  CONFIG '{"region":"us-west-2","endpoint-url":"https://s3.amazonaws.com"}' 
  CREDENTIALS '{"aws_access_key_id" : "replace_with_your_access_key_id","aws_secret_access_key":"replace_with_your_secret_access_key"}' 
  ON WORKSPACE test-workspace;

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