Categories:

Database, Schema, & Share DDL

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.

See also:

CREATE DATABASE , DESCRIBE DATABASE , DROP DATABASE , SHOW DATABASES , UNDROP DATABASE

In this Topic:

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> ]
                                        [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
                                        [ 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     |
                                            DEFAULT_DDL_COLLATION               |
                                            COMMENT
                                          }
                                          [ , ... ]

Database Replication and Failover Syntax

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

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

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 details, 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.

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 details about the parameter, see DEFAULT_DDL_COLLATION.

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. The maximum number of unique tags that can be set on an object is 20. For more information, see Tag Quotas for Objects/Columns.

A single ALTER statement can set or unset a maximum of 5 tags on an object.

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

  • 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

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 details 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

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 details about using the account locator as an account identifier, see Database Replication and Failover Usage Notes.

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

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 details about using the account locator as an account identifier, see Database Replication and Failover Usage Notes.

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

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 details about using the account locator as an account identifier, see Database Replication and Failover Usage Notes.

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 privileges on the database.

  • To swap two databases, the role used to perform the operation must have OWNERSHIP privileges on both databases.

  • 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

  • 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, it may need to contain additional segments in order to uniquely identify the account. See the table below for reference:

    Account Identifier

    Location of the Remote Account

    organization_name.account_name

    Preferred account identifier that can be used regardless of the region or region group of the account that stores the primary database.

    account_locator

    Same region but a different account from the account that stores the primary database.

    snowflake_region.account_locator

    Same region group but a different region from the account that stores the primary database.

    region_group.snowflake_region.account_locator

    Different region group from the account that stores the primary database.

General Examples

Rename database db1 to db2:

ALTER DATABASE IF EXISTS db1 RENAME TO db2;

Database Replication Examples

Promote local database mydb1 (in account account1) to serve as a primary database and specify that accounts account2 and account3 in the myorg organization can each store a replica of this database:

ALTER DATABASE mydb1 ENABLE REPLICATION TO ACCOUNTS myorg.account2, myorg.account3;

Disable replication for the mydb1 primary database:

ALTER DATABASE mydb1 DISABLE REPLICATION;
Back to top