ALTER DATABASE¶

Modifies the properties for an existing database.

Database modifications include the following:

Changing the name of the database or changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).
Enabling and managing database replication and failover.

Tip

You can also use the Snowflake REST APIs to perform this operation. For information, see the following endpoints:

See also:: CREATE DATABASE , DESCRIBE DATABASE , DROP DATABASE , SHOW DATABASES , UNDROP DATABASE

Syntax¶

ALTER DATABASE [ IF EXISTS ] <name> RENAME TO <new_db_name>

ALTER DATABASE [ IF EXISTS ] <name> SWAP WITH <target_db_name>

ALTER DATABASE [ IF EXISTS ] <name> SET [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
                                        [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
                                        [ EXTERNAL_VOLUME = <external_volume_name> ]
                                        [ CATALOG = <catalog_integration_name> ]
                                        [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
                                        [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
                                        [ LOG_LEVEL = '<log_level>' ]
                                        [ TRACE_LEVEL = '<trace_level>' ]
                                        [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ]
                                        [ COMMENT = '<string_literal>' ]

ALTER DATABASE <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER DATABASE <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER DATABASE [ IF EXISTS ] <name> UNSET { DATA_RETENTION_TIME_IN_DAYS         |
                                            MAX_DATA_EXTENSION_TIME_IN_DAYS     |
                                            EXTERNAL_VOLUME                     |
                                            CATALOG                             |
                                            DEFAULT_DDL_COLLATION               |
                                            STORAGE_SERIALIZATION_POLICY        |
                                            COMMENT
                                          }
                                          [ , ... ]

Copy

Database replication and failover syntax¶

Attention

Snowflake recommends using the account replication feature to replicate and failover databases.

Database Replication

ALTER DATABASE <name> ENABLE REPLICATION TO ACCOUNTS <account_identifier> [ , <account_identifier> ... ] [ IGNORE EDITION CHECK ]

ALTER DATABASE <name> DISABLE REPLICATION [ TO ACCOUNTS <account_identifier> [ , <account_identifier> ... ] ]

ALTER DATABASE <name> REFRESH

Copy

Database Failover

ALTER DATABASE <name> ENABLE FAILOVER TO ACCOUNTS <account_identifier> [ , <account_identifier> ... ]

ALTER DATABASE <name> DISABLE FAILOVER [ TO ACCOUNTS <account_identifier> [ , <account_identifier> ... ] ]

ALTER DATABASE <name> PRIMARY

Copy

Parameters¶

name

Specifies the identifier for the database to alter. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

RENAME TO new_db_name

Specifies the new identifier for the database; must be unique for your account.

For more information, see Identifier requirements.

When an object is renamed, other objects that reference it must be updated with the new name.

SWAP WITH target_db_name

Swaps all objects (schemas, tables, views, etc.) and metadata, including identifiers, between the two specified databases. Also swaps all access control privileges granted on the databases and objects they contain. SWAP WITH essentially performs a rename of both databases as a single operation.

SET ...

Specifies one (or more) properties to set for the database (separated by blank spaces, commas, or new lines):

DATA_RETENTION_TIME_IN_DAYS = num

Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, as well as specifying the default Time Travel retention time for all schemas created in the database.

The value you can specify depends on the Snowflake Edition you are using:

Standard Edition: 0 or 1
Enterprise Edition (or higher): 0 to 90

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for tables in the database to prevent streams on the tables from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

EXTERNAL_VOLUME = external_volume_name

Object parameter that specifies the default external volume to use for Apache Iceberg™ tables.

For more information about this parameter, see EXTERNAL_VOLUME.

CATALOG = catalog_integration_name

Object parameter that specifies the default catalog integration to use for Apache Iceberg™ tables.

For more information about this parameter, see CATALOG.

REPLACE_INVALID_CHARACTERS = { TRUE | FALSE }

Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�) in query results for an Iceberg table. You can only set this parameter for tables that use an external Iceberg catalog.

TRUE replaces invalid UTF-8 characters with the Unicode replacement character.
FALSE leaves invalid UTF-8 characters unchanged. Snowflake returns a user error message when it encounters invalid UTF-8 characters in a Parquet data file.

Default: FALSE

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for:

Any new columns added to existing tables in the database.
All columns in new tables added to the database.

Setting the parameter does not change the collation specification for any existing columns.

For more information about the parameter, see DEFAULT_DDL_COLLATION.

LOG_LEVEL = 'log_level'

Specifies the severity level of messages that should be ingested and made available in the active event table. Messages at the specified level (and at more severe levels) are ingested.

For more information about levels, see LOG_LEVEL. For information about setting the log level, see Setting levels for logging, metrics, and tracing.

TRACE_LEVEL = 'trace_level'

Controls how trace events are ingested into the event table.

For information about levels, see TRACE_LEVEL. For information about setting the trace level, see Setting levels for logging, metrics, and tracing.

STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED }

Specifies the storage serialization policy for Apache Iceberg™ tables that use Snowflake as the catalog.

COMPATIBLE: Snowflake performs encoding and compression of data files that ensures interoperability with third-party compute engines.
OPTIMIZED: Snowflake performs encoding and compression of data files that ensures the best table performance within Snowflake.

Default: OPTIMIZED

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

COMMENT = 'string_literal': Adds a comment or overwrites an existing comment for the database.

UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

DATA_RETENTION_TIME_IN_DAYS
MAX_DATA_EXTENSION_TIME_IN_DAYS
EXTERNAL_VOLUME
CATALOG
DEFAULT_DDL_COLLATION
TAG tag_name [ , tag_name ... ]
COMMENT

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property will return an error.

Database replication and failover parameters¶

Attention

Snowflake recommends using the account replication feature to replicate and failover databases.

ENABLE REPLICATION TO ACCOUNTS account_identifier [ , account_identifier ... ]

Promotes a local database to serve as a primary database for replication. A primary database can be replicated in one or more accounts, allowing users in those accounts to query objects in each secondary (i.e. replica) database.

Alternatively, modify an existing primary database to add to or remove from the list of accounts that can store a replica of the database.

Provide a comma-separated list of accounts in your organization that can store a replica of this database.

account_identifier

Unique identifier of the account. The preferred identifier is organization_name.account_name. To view the list of accounts enabled for replication in your organization, query SHOW REPLICATION ACCOUNTS.

Though the legacy account locator can also be used as the account identifier, its use is discouraged as it may not work in the future. For more information about using the account locator as an account identifier, see Database Replication and Failover Usage Notes.

IGNORE EDITION CHECK

Allows replicating data to accounts on lower editions in either of the following scenarios:

The primary database is in a Business Critical (or higher) account but one or more of the accounts approved for replication are on lower editions. Business Critical Edition is intended for Snowflake accounts with extremely sensitive data.
The primary database is in a Business Critical (or higher) account and a signed business associate agreement is in place to store PHI data in the account per HIPAA and HITRUST regulations, but no such agreement is in place for one or more of the accounts approved for replication, regardless if they are Business Critical (or higher) accounts.

Both scenarios are prohibited by default in an effort to help prevent account administrators for Business Critical (or higher) accounts from inadvertently replicating sensitive data to accounts on lower editions.

DISABLE REPLICATION [ TO ACCOUNTS account_identifier [ , account_identifier ... ] ]

Disables replication for this primary database, meaning no replica of this database (i.e. secondary database) in another account can be refreshed. Any secondary databases remain linked to the primary database, but requests to refresh a secondary database are denied.

Note that disabling replication for a primary database does not prevent it from being replicated to the same account; therefore, the database continues to be listed in the SHOW REPLICATION DATABASES output.

Optionally provide a comma-separated list of accounts in your organization to disable replication for this database only in the specified accounts.

account_identifier

REFRESH

Refreshes a secondary database from a snapshot of its primary database. A snapshot includes changes to the objects and data.

ENABLE FAILOVER TO ACCOUNTS account_identifier [ , account_identifier ... ]

Specifies a comma-separated list of accounts in your organization where a replica of this primary database can be promoted to serve as the primary database.

account_identifier

DISABLE FAILOVER [ TO ACCOUNTS account_identifier [ , account_identifier ... ] ]

Disables failover for this primary database, meaning no replica of this database (i.e. secondary database) can be promoted to serve as the primary database.

Optionally provide a comma-separated list of accounts in your organization to disable failover for this database only in the specified accounts.

account_identifier

PRIMARY

Promotes the specified secondary (replica) database to serve as the primary database. When promoted, the database becomes writeable. At the same time, the previous primary database becomes a read-only secondary database.

Usage notes¶

To rename a database, the role used to perform the operation must have the CREATE DATABASE global privilege and OWNERSHIP privilege on the database.
To swap two databases, the role used to perform the operation must have OWNERSHIP privileges on both databases.
To update a comment, the role used to perform the operation must be granted or inherit the MODIFY privilege on the database.
Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Database replication and failover usage notes¶

Attention

Snowflake recommends using the account replication feature to replicate and failover databases.

Only account administrators (users with the ACCOUNTADMIN role) can enable and manage database replication and failover.
A default 10 TB size limit is applied when a primary database is initially replicated to a secondary database. To change or remove the size limit, set the INITIAL_REPLICATION_SIZE_LIMIT_IN_TB parameter at the account level.

Note that there is currently no default size limit applied to subsequent refreshes of a secondary database.
The preferred method of identifying an account uses the organization name and account name as the account identifier. If you decide to use the legacy account locator instead, see Account identifiers for replication and failover.

General examples¶

Rename database db1 to db2:

ALTER DATABASE IF EXISTS db1 RENAME TO db2;

Copy

Database replication examples¶

Attention

Snowflake recommends using the account replication feature to replicate and failover databases.

Use a replication or failover group to replicate and failover a single database. For examples, see one of the following: