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> [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";
Arguments
<database_name>
is the SingleStore database to restore to. The restored database always has the same number of partitions as the original database that you backed up. 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 linkconnection_name
.resource_pool_name
is optional. It is the name of the resource pool where the restore operation runs.Prior to 7.8 - 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 orpath
for S3 orprefix-key
for Azure blobs. For example, you can doBACKUP DATABASE db TO './path/'
followed byRESTORE DATABASE newdb FROM './path/db.backup'
. See the Examples section for examples. From 7.8, you can give the new name without specifying the full path.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 thebackup_path
orbucket/path
, orcontainer/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. IfWITH {SYNC | ASYNC} DURABILITY
is not specified, the durability setting from the database specified in thebackup_path
orbucket/path
, orcontainer/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 theincr_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 SingleStoreDB recovers a database snapshot during startup.CONFIG
andCREDENTIALS
can be specified in either order (CONFIG
followed byCREDENTIALS
orCREDENTIALS
followed byCONFIG
).For configuration examples refer BACKUP DATABASEA
RESTORE
operation temporarily puts the database being restored into therecovering snapshot
state. A database in this state cannot be queried. When theRESTORE
command finishes, it puts the database into theonline
state. See Database for more information.This command must be run on the master aggregator node. See Node Requirements for SingleStoreDB Commands for more information.
This command causes implicit commits. See COMMIT 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.See the Permission Matrix for the required permission.
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 thememsqld
process. Paths are resolved relative to thememsqlbin/data
directory.You can include
/
at the front of thebackup_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.As an alternative to using a
RESTORE ... FROM S3
statement where you specify theCONFIG
andCREDENTIALS
clauses, you can use theRESTORE ... FROM LINK
statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.
Restore From an S3-Compatible Storage Provider
RESTORE ... FROM S3
with theendpoint_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 theCONFIG
andCREDENTIALS
clauses, you can use theRESTORE ... 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 anendpoint_url
.The
CREDENTIALS
clause is requiredWe 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 theCONFIG
clause, you can use theRESTORE ... 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.
Examples
Restore an Incremental Backup
The Backing Up and Restoring Data topic provides an example of restoring an incremental backup.
Note
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);
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.
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.