# RESTORE DATABASE Restores a database from a binary backup file. Refer to [Back Up and Restore Data](https://docs.singlestore.com/cloud/manage-data/back-up-and-restore-data.md) for additional information. ## Syntax ## Restore From S3 ```sql RESTORE [DATABASE] [AS ] FROM S3 "bucket/path" [CONFIG ] CREDENTIALS [WITH FILE = incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)] : '{"region":"your_region", "endpoint_url":"http://other_endpoint" [, "x-amz-server-side-encryption-customer-algorithm":"", "x-amz-server-side-encryption-customer-key":"", "x-amz-server-side-encryption-customer-key-MD5":"" ] }' : '{"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 ```sql RESTORE [DATABASE] [AS ] FROM S3 "bucket/path" [CONFIG ] CREDENTIALS [WITH FILE = incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)] : '{"endpoint_url":"http://storage_provider_endpoint"}' : '{"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 ```sql RESTORE [DATABASE] [AS ] FROM GCS "bucket/path" [CONFIG ] CREDENTIALS [WITH FILE = incr_backup_id] [sync_options] [ (OPTION resource_pool = resource_pool_name)] : '{}' : '{"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](https://docs.singlestore.com/#UUID-64cf5356-221c-9091-d17a-8ad624210275.md) for details on the `aws_access_key_id` and `aws_secret_access_key`. ## Restore from Azure blobs ```sql RESTORE [DATABASE] [AS ] FROM AZURE "container/blob-prefix" CREDENTIALS [CONFIG ] [WITH FILE = incr_backup_id] [sync_options] [(OPTION resource_pool = resource_pool_name)] : '{"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 ```sql RESTORE [DATABASE] [AS ] FROM LINK [link_database_name.]connection_name "backup_path"; ``` ## Arguments * `` 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. * `link_database_name` is the database that stores the connection link `connection_name`. * If you wish to restore the backup of one database into a differently-named database, you can do so by specifying the `path` for S3 or `prefix-key` for Azure blobs. 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. * `WITH {SYNC | ASYNC} DURABILITY` specifies, following the database restore, whether in-memory database updates you make using [DDL](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl.md) and [DML](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml.md) 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. * 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. * `ON WORKSPACE ` specifies the workspace to which to restore the database. When `RESTORE DATABASE` is run on a child aggregator (that is part of a workspace), the `ON WORKSPACE` syntax is not necessary. The database is restored to the workspace of the child aggregator automatically. When restoring on the MA directly (i.e., using the DDL endpoint in SingleStore Helios) use `ON WORKSPACE` to specify the workspace to restore to. ## Remarks * The `RESTORE` command replays a binary backup file in the same manner in which SingleStore Helios recovers a database snapshot during startup. * Database restore is parallelized at the partition level. Therefore, the performance of a restore operation is affected by the number of partitions, the more the number of partitions, the faster the restore is likely to be. The rule of thumb is that you should have at least a 1:1 CPU:PARTITION count. * `CONFIG` and `CREDENTIALS` can be specified in either order (`CONFIG` followed by `CREDENTIALS` or `CREDENTIALS` followed by `CONFIG`).For configuration examples refer [BACKUP DATABASE](https://docs.singlestore.com/cloud/reference/sql-reference/operational-commands/backup-database.md) * 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](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-databases.md) for more information. * When using the `[AS ]` option, ensure that there is no database with either the `` or the `` already existing at the restore location. For example, if a database named DB01 exists and you execute `RESTORE DATABASE db01 AS db01_test`, you will encounter an error indicating that the database, DB01 already exists. * This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information. * You can restore to S3, Azure, or GCS using a [connection link](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). However, you require the `SHOW LINK` permission, provided by your administrator, to use a connection link. * Refer to the [Permissions Matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions. > **⚠️ Warning**: SingleStore does not support restoring database backups from a newer version of SingleStore into an older version. ## 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](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). * * 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](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). ## Restore From Google Cloud Storage * The `CONFIG` clause may optionally specify an `endpoint_url`. * The `CREDENTIALS` clause is required * We support only [HMAC keys](https://cloud.google.com/storage/docs/authentication/hmackeys) * 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](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). ## 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](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). ## Restore from a Connection Link * The `RESTORE.. FROM LINK` command restores a database from the specified container using a [connection link](https://docs.singlestore.com/cloud/security/database-access/configuring-and-using-connection-links.md). 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. ## Examples ## Restore from S3 The following example restores from an S3 bucket. ```sql 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'). If you attempt to restore the backup to a new database name, such as 'new\_memsql\_demo', but do not use the `AS` clause, SingleStore looks for a backup directory named 'new\_memsql\_demo.backup' within the path you specified. Since this directory does not exist, the restore operation will fail with an error indicating the path is inaccessible or does not point to a valid backup file. ```sql RESTORE DATABASE memsql_demo AS 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. ```sql --- For Server-Side Encryption with Customer-Provided Encryption Keys (SSE-C) --- CONFIG '{"region":"us-east-1", "endpoint_url":"", "x-amz-server-side-encryption-customer-algorithm":"AES256", "x-amz-server-side-encryption-customer-key":"", "x-amz-server-side-encryption-customer-key-MD5":""}' ``` The following example restores a database from an S3 bucket to a the `test-workspace` workspace. ```sql 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. ```sql 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. ```sql 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`). ```sql 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`. ```sql 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](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/create-link.md) for details on creating a connection link. ```sql 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`. ## 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. ## Related Topics * [BACKUP](https://docs.singlestore.com/cloud/reference/sql-reference/operational-commands/backup-database.md) *** Modified at: May 12, 2026 Source: [/cloud/reference/sql-reference/operational-commands/restore-database/](https://docs.singlestore.com/cloud/reference/sql-reference/operational-commands/restore-database/) (An index of the documentation is available at /llms.txt)