Detach and re-attach a MotherDuck database
After creating a remote MotherDuck database,
the DETACH <database> command may be used to detach it.
This will prevent access and modifications to the database until it is re-attached using the ATTACH <database> command.
This pattern can be used to isolate queries and changes to a specific set of databases.
Note that this is a convenience feature and not a security feature, as a MotherDuck database may be reattached at any time.
Database shares behave slightly differently than non-shared databases, so if you want to ATTACH and DETACH shares, please have a look at how to manage shared MotherDuck databases.
Creating, detaching, and re-attaching a database
This guide will show how to CREATE, DETACH, and ATTACH a database using the CLI and the UI.
- CLI
- UI
CREATE DATABASE my_new_md_database;
DETACH my_new_md_database;
ATTACH 'my_new_md_database';
-- OR
ATTACH 'md:my_new_md_database';
To create a database, add a new cell and enter the SQL command CREATE DATABASE <database name>.
Click the Run button.
Click on the menu of the database you would like to detach and select Detach.
The database will be moved to the "Detached Databases" section of the object explorer.
To re-attach, click on the menu of the database in the "Detached Databases" section and select Attach.
The database will be returned to the "My Databases" section.
Show All Databases
To see all databases, both attached and detached, use the SHOW ALL DATABASES command.
- CLI
SHOW ALL DATABASES;
Example output:
┌──────────────────────────────────────────┬─────────────┬──────────────────┬───────── ────────────────────────────────────────────────────────────────────────────────┐
│ alias │ is_attached │ type │ fully_qualified_name │
│ varchar │ boolean │ varchar │ varchar │
├──────────────────────────────────────────┼─────────────┼──────────────────┼─────────────────────────────────────────────────────────────────────────────────────────┤
│ TEST_DB_02d6fc2158094bd693b6f285dbd402f7 │ true │ motherduck │ md:TEST_DB_02d6fc2158094bd693b6f285dbd402f7 │
│ TEST_DB_62b53d968a4f4b6682ed117a7251b814 │ true │ motherduck │ md:TEST_DB_62b53d968a4f4b6682ed117a7251b814 │
│ base │ false │ motherduck │ md:base │
│ base2 │ true │ motherduck │ md:base2 │
│ db1 │ false │ motherduck │ md:db1 │
│ integration_test_001 │ false │ motherduck │ md:integration_test_001 │
│ my_db │ true │ motherduck │ md:my_db │
│ my_share_1 │ true │ motherduck share │ md:_share/integration_test_001/18d6dbdb-e130-4cdf-97c4-60782ed5972b │
│ sample_data │ false │ motherduck │ md:sample_data │
│ source_db │ true │ motherduck │ md:source_db │
│ test_db_115 │ false │ motherduck │ md:test_db_115 │
│ test_db_28d │ false │ motherduck │ md:test_db_28d │
│ test_db_cc9 │ false │ motherduck │ md:test_db_cc9 │
│ test_share │ true │ motherduck share │ md:_share/source_db/b990b424-2f9a-477a-b216-680a22c3f43f │
│ test_share_002 │ true │ motherduck share │ md:_share/integration_test_001/06cc5500-e49a-4f62-9203-105e89a4b8ae │
├──────────────────────────────────────────┴─────────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────────────────┤
│ 15 rows (15 shown) 4 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘