Skip to main content

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.

CREATE DATABASE my_new_md_database;

DETACH my_new_md_database;

ATTACH 'my_new_md_database';
-- OR
ATTACH 'md:my_new_md_database';

Show All Databases

To see all databases, both attached and detached, use the SHOW ALL DATABASES command.

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 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘