Syntax

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

Arguments

Remarks

• 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.DATETIME should be in the UTC format.

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

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

The following example uses the UTC timezone.

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.

Renaming a Database

1. To rename an existing database, create a new database branch from the original database. This branch serves as a point-in-time copy, containing all data from the original database exactly as it existed at the moment of branching.

   Copy

   ATTACH DATABASE x_db AS x_db_new_name;

2. After the branch is successfully created, you can detach or drop the original database.

3. Finally, rename the newly created branch to the original database name.