Categories:

Database, Schema, & Share DDL

ALTER DATABASE

Modifies the properties for an existing database, including 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 , SHOW DATABASES

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 = <num> ] [ COMMENT = '<string_literal>' ]

ALTER DATABASE [ IF EXISTS ] <name> UNSET { DATA_RETENTION_TIME_IN_DAYS | COMMENT } [ , ... ]

Database Replication and Failover Syntax

Database Replication

ALTER DATABASE <name> ENABLE REPLICATION TO ACCOUNTS <snowflake_region>.<account_name> [ , <snowflake_region>.<account_name> ... ]

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

ALTER DATABASE <name> SET AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }

ALTER DATABASE <name> REFRESH

Database Failover

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

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

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 Syntax.

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

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

  • 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 snowflake_region.account_name [ , snowflake_region.account_name ... ]

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. In addition to the account name, additional segments are required, depending on the region where your remote account is located:

Account reference

Qualified Account Name

Location of the Remote Account

account

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

snowflake_region.account

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

snowflake_region

Specify the Snowflake Region ID for your region where each Snowflake account is hosted. To view the list of accounts in your organization, query TOPIC MOVED/REMOVED… SHOW GLOBAL ACCOUNTS.

DISABLE REPLICATION [ TO ACCOUNTS snowflake_region.account_name [ , snowflake_region.account_name ... ]

Disables all replication for this primary database, meaning no replica of this database (i.e. secondary database) can be refreshed.

Optionally provide a comma-separated list of accounts in your organization to disable replication for this database only in the specified accounts. In addition to the account name, additional segments are required, depending on the region where your remote account is located:

Account reference

Qualified Account Name

Location of the Remote Account

account

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

snowflake_region.account

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

snowflake_region

Specify the Snowflake Region ID for your region where each Snowflake account is hosted. To view the list of accounts in your organization, query TOPIC MOVED/REMOVED… SHOW GLOBAL ACCOUNTS.

SET AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = TRUE | FALSE

Specifies whether to perform automatic background maintenance of materialized views in a secondary database.

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 snowflake_region.account_name [ , snowflake_region.account_name ... ]

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. In addition to the account name, additional segments are required, depending on the region where your remote account is located:

Account reference

Qualified Account Name

Location of the Remote Account

account

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

snowflake_region.account

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

snowflake_region

Specify the Snowflake Region ID for your region where each Snowflake account is hosted. To view the list of accounts in your organization, query TOPIC MOVED/REMOVED… SHOW GLOBAL ACCOUNTS.

DISABLE FAILOVER [ TO ACCOUNTS snowflake_region.account_name [ , snowflake_region.account_name ... ]

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. In addition to the account name, additional segments are required, depending on the region where your remote account is located:

Account reference

Qualified Account Name

Location of the Remote Account

account

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

snowflake_region.account

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

snowflake_region

Specify the Snowflake Region ID for your region where each Snowflake account is hosted. To view the list of accounts in your organization, query TOPIC MOVED/REMOVED… SHOW GLOBAL ACCOUNTS.

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.

Database Replication and Failover Usage Notes

  • Only account administrators (users with the ACCOUNTADMIN role) can enable and manage database replication and failover.

General Examples

Rename database db1 to db2:

ALTER DATABASE IF EXISTS db1 RENAME TO db2;

Database Replication Examples

Promote local database mydb1 (in region aws_us_west_2) to serve as a primary database and specify that accounts myaccount2 and myaccount3 (in regions aws_us_east_1 (AWS) and azure_westeurope (Azure), respectively) can each store a replica of this database:

ALTER DATABASE mydb1 ENABLE REPLICATION TO ACCOUNTS aws_us_east_1.myaccount2, azure_westeurope.myaccount3;

Disable replication for the mydb1 primary database:

ALTER DATABASE mydb1 DISABLE REPLICATION;