# ALTER DATABASE
> Update storage-related settings on a MotherDuck database.
The `ALTER DATABASE` statement updates storage-related settings for an existing MotherDuck database.

## Syntax

```sql
ALTER DATABASE <database_name> SET <option> = <value>[, <option> = <value> ...];
```

Values are string literals, integers, or `NULL`.

## Options

### Native storage databases

These options apply to standard and transient databases backed by MotherDuck native storage.

| Name | Data type | Description |
|------|-----------|-------------|
| `SNAPSHOT_RETENTION_DAYS` | INTEGER | Number of days to retain automatic and unnamed snapshots. Must be a non-negative integer within your [plan limits](#plan-limits-for-native-storage). `0` disables historical snapshots. Named snapshots are retained until unnamed. |

### DuckLake databases

These options apply to [DuckLake](/concepts/ducklake) databases (fully managed and BYOB).

| Name | Data type | Description |
|------|-----------|-------------|
| `SNAPSHOT_RETENTION_DAYS` | INTEGER or NULL | Number of days to retain DuckLake snapshots before they are eligible for expiration. Defaults to `NULL` (infinite retention). DuckLake snapshots are expired by running [maintenance operations](/concepts/ducklake#maintenance) manually; MotherDuck does not expire them automatically. |

### Iceberg databases

These options apply to attached [Iceberg catalogs](/integrations/file-formats/apache-iceberg#persisted-iceberg-catalogs). The change takes effect immediately: MotherDuck reattaches the catalog with the updated configuration, so the next query uses it.

| Name | Value | Description |
|------|-------|-------------|
| `secret` | String | Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. Can't be cleared once set. |
| `default_schema` | String | Schema used to resolve unqualified table names. Must exist in the catalog. Can't be cleared once set. |
| `default_region` | String | Per-catalog region override. |
| `access_delegation_mode` | `'vended_credentials'` or `'none'` | Whether to request vended credentials from the catalog. |
| `stage_create_tables` | `'true'` or `'false'` | Create tables through the catalog's stage-create flow. Turn it off for catalogs that don't support staged creates. |
| `skip_create_table_metadata_updates` | `'true'` or `'false'` | Skip the follow-up metadata update after a non-staged `CREATE TABLE`, for catalogs that reject it. |
| `disable_multi_table_commit` | `'true'` or `'false'` | Commit tables one at a time instead of using the catalog's multi-table commit endpoint. |
| `remove_files_on_delete` | `'true'` or `'false'` | Delete the underlying data files when a table is dropped. Turn it off for catalogs that handle their own cleanup, such as S3 Tables. |
| `purge_requested` | `'true'` or `'false'` | Ask the catalog to purge table data on `DROP TABLE`. |
| `support_nested_namespaces` | `'true'` or `'false'` | Address nested catalog namespaces as multi-level schema names. |
| `encode_entire_prefix` | `'true'` or `'false'` | Send the catalog prefix as a single URL-encoded path component. |

Options that identify the catalog - `endpoint`, `warehouse`, `endpoint_type`, and `read_only` - can't be altered, because changing them points the database at a different catalog: that's a different database, not a reconfigured one. To change one of them, drop the database and create it again.

## Notes

`ALTER DATABASE` requires write access. It fails with a permission error on connections made with read-only credentials, such as a [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/).

Use [`ALTER DATABASE SET SNAPSHOT`](/sql-reference/motherduck-sql-reference/alter-database-snapshot) to restore a native storage database to a snapshot.

Refer to the [snapshots guide](/concepts/snapshots) for snapshot behavior and to [Storage lifecycle](/concepts/storage-lifecycle#standard-databases) for plan limits on retention.

## Plan limits for native storage

For standard and transient databases, `SNAPSHOT_RETENTION_DAYS` is limited by plan:

- Business: 0-90 days
- Lite (paid): 1 day (min/max)
- Lite (free): 0 days (min/max)

## Examples

### Native storage

```sql
ALTER DATABASE my_db SET SNAPSHOT_RETENTION_DAYS = 7;
```

### DuckLake

Set a snapshot retention period for a DuckLake database:

```sql
ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = 7;
```

Revert to infinite snapshot retention:

```sql
ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = NULL;
```

### Iceberg

Point the catalog at a different default schema:

```sql
ALTER DATABASE my_datalake SET default_schema = 'analytics';
```

Rotate catalog credentials by swapping in another secret:

```sql
ALTER DATABASE my_datalake SET secret = 'my_new_iceberg_secret';
```

Use the secret's own credentials instead of vended ones, and stop deleting data files when a table is dropped:

```sql
ALTER DATABASE my_datalake SET
    access_delegation_mode = 'none',
    remove_files_on_delete = 'false';
```


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fmotherduck-sql-reference%2Falter-database%2F&page_title=ALTER%20DATABASE&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.