You are viewing an older version of this section. View current production version.


Restore data from a binary backup file.


Local Restore


S3 Restore

RESTORE [DATABASE] db_name FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH {SYNC | ASYNC} REPLICATION]


'{"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",]


  • db_name is the name of the MemSQL database to restore to.
  • If you wish to restore the backup into a differently-named database, you can do so by specifying the full path to the .backup file in backup_path for local backups or path for S3. For example, you can do BACKUP DATABASE db TO './path/' followed by RESTORE DATABASE newdb FROM './path/db.backup'. See the Examples section for an example using S3.
  • The RESTORE command replays a binary backup file in the same manner in which MemSQL recovers a database snapshot during startup.
  • WITH {SYNC | ASYNC} REPLICATION controls whether replication in high availability will be done synchronously or asynchronously. The default mode is ASYNC. Partitions replicated synchronously will ensure commits have reached all replicas before acknowledging the transaction to the user client. While providing stronger consistency guarantees, this can negatively impact write performance. For more information, see Managing High Availability.
  • 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 the Database topic for more information).
  • This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands).

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

Local Restore Remarks

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

S3 Restore Remarks

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


Local Restore

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

RESTORE DATABASE memsql_demo FROM "./";

S3 Restore

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 from an S3 bucket to a database with a different name.

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

This MemSQL feature is only available in MemSQL Enterprise Edition. MemSQL Enterprise Edition includes 24x7 support and several enterprise-only features for critical deployments. For more information about MemSQL Developer and MemSQL Enterprise, see the MemSQL Editions page.