ATTACH DATABASE

Syntax

ATTACH DATABASE remote_database_name [AS new_database_name] [AT MILESTONE milestone_name | AT TIME attach_time]

Arguments

Remarks

• If remote_database_name is currently attached, it must be detached (using DETACH DATABASE) before it can be reattached. At the time of reattaching ensure the object_store_settings and configurations are the same as those used at the time of the database creation.

• AT TIME attaches the database, with its contents restored to the specified point in time. The specified time may be a DATETIME or a DATETIME(6) literal. An error will be returned if you attempt to attach a database at time literal that is later than the time of the latest detach.

• If AT MILESTONE and AT TIME are not specified, the database is attached at the latest state where all information is available for committed transactions.

• You cannot specify both AT MILESTONE and AT TIME.

• ATTACH DATABASE remote_database_name AS new_database_name renames remote_database_name to new_database_name, following the attach.

• Attaching an unlimited storage database can be faster than restoring an equivalent local storage database. This is because an attach of an unlimited storage database does not copy all data to the workspace, as is the case with the restore of a local storage database. Note that after an unlimited storage database is attached, queries may be slower for some time until remote data is cached locally in the workspace.

• This command cannot be run on system databases.

Examples

ATTACH DATABASE db01;

ATTACH DATABASE db01 AT MILESTONE m01;

ATTACH DATABASE db01 AT TIME “2022-09-03 11:37:07”;

Attach a Database to a Workspace

You can only attach a database to a workspace within a single workspace group.

A database cannot have more than one read-write attachment.

Databases with a large number of partitions cannot be attached to a small workspace. The engine automatically performs the following check: core count * 8 >= total number of partitions.

Here is an example of the error message when the total number of partitions is greater: "Creating or attaching a database with 32 total partitions to a cluster or workspace with 2 total cores is not supported. Please use a larger cluster or workspace."

To attach an existing database in the read-write mode to a workspace, connect to the target workspace and execute the following command:

ATTACH DATABASE db_name;

To attach an existing database to a workspace in the read-only mode, execute the following command:

ATTACH DATABASE db_name READ ONLY;

The read-only mode is mutually exclusive with the PITR options. Hence it cannot be combined with the PITR options like milestones or attach_time. It always attaches to latest and always replicates changes from the writable attach of the database if such an attachment exists.

When the Enable SmartDR and Database Branching checkbox is selected at the time of workspace group creation, you can attach a database to a milestone but you cannot rename the database. The branch created is displayed as attached and the parent database is detached. This is available at the three AWS regions: us-west-2, us-east-1, and us-east-2 (Virginia, Ohio, Oregon)