ALTER DATABASE

The ALTER DATABASE statement updates storage-related settings for an existing MotherDuck database.

Syntax

ALTER DATABASE <database_name> SET <option> = <value>;

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. 0 disables historical snapshots. Named snapshots are retained until unnamed.

DuckLake databases

These options apply to DuckLake databases (fully managed and BYOB).

Name	Data type	Description
AUTO_MAINTENANCE	BOOLEAN	Enables or disables auto maintenance (file optimization and snapshot lifecycle management). Fully managed DuckLake databases have this enabled by default; BYOB databases have it disabled by default.
SNAPSHOT_RETENTION_DAYS	INTEGER or NULL	Number of days to retain DuckLake snapshots. Defaults to NULL (infinite retention). Set a positive integer to enable automatic snapshot expiration. Requires AUTO_MAINTENANCE to be enabled.

Notes

Use ALTER DATABASE SET SNAPSHOT to restore a native storage database to a snapshot.

Refer to the snapshots guide for snapshot behavior and to Storage lifecycle 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

ALTER DATABASE my_db SET SNAPSHOT_RETENTION_DAYS = 7;

DuckLake

Enable auto maintenance for a BYOB DuckLake database:

ALTER DATABASE my_ducklake SET AUTO_MAINTENANCE = TRUE;

Set a snapshot retention period for a DuckLake database:

ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = 7;

Revert to infinite snapshot retention:

ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = NULL;

Disable auto maintenance:

ALTER DATABASE my_ducklake SET AUTO_MAINTENANCE = FALSE;