ALTER SNAPSHOT SET¶
Modifies the properties for a snapshot set. This operation can be one of the following:
Taking a new backup that becomes part of the snapshot set.
Removing an old backup from the snapshot set.
Suspending or resuming the scheduled backups and scheduled snapshot deletion that are specified by the snapshot policy.
Applying a snapshot policy to a snapshot set that doesn’t already have a policy.
Adding or removing a legal hold for a specific snapshot within the snapshot set.
Specifying or removing a comment for the snapshot set.
Syntax¶
ALTER SNAPSHOT SET <name> ADD SNAPSHOT
ALTER SNAPSHOT SET <name> APPLY SNAPSHOT POLICY <policy_name>
ALTER SNAPSHOT SET <name> SUSPEND SNAPSHOT POLICY
ALTER SNAPSHOT SET <name> RESUME SNAPSHOT POLICY
ALTER SNAPSHOT SET <name> DELETE SNAPSHOT IDENTIFIER '<snapshot_id>'
ALTER SNAPSHOT SET <name> MODIFY SNAPSHOT IDENTIFIER '<snapshot_id>' { ADD | REMOVE } LEGAL HOLD
ALTER SNAPSHOT SET <name> SET COMMENT = '<string_literal>'
ALTER SNAPSHOT SET <name> UNSET COMMENT
Parameters¶
name
Specifies the identifier for the snapshot set.
If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
For more information, see Identifier requirements.
ADD SNAPSHOT
Manually create a snapshot in the set. If the snapshot policy doesn’t include a schedule for taking new backups, this is how you make a new backup of the table, schema, or database that’s included in the snapshot set. You can also make new backups in the snapshot set at any time even when backups happen on a regular schedule.
APPLY SNAPSHOT POLICY policy_name
Specifies the snapshot policy to attach to the snapshot set.
Important
Applying a snapshot policy with a retention lock to a snapshot set is irreversible. Due to the strong guarantees that are needed for regulatory compliance, after you put a retention lock on a snapshot set, you can’t revoke the lock. Snowflake support also can’t revoke such a retention lock. Plan carefully before you set a retention lock on a snapshot set with a long expiration period, to avoid unexpected storage charges for undeletable snapshot sets, and the schemas and databases that contain them.
If a Snowflake organization is deleted, the organization is no longer a Snowflake customer. In this case, Snowflake deletes all snapshots, including those with retention locks. Deleting a Snowflake organization requires the involvement of Snowflake support. It isn’t something that an administrator can do by accident.
SUSPEND SNAPSHOT POLICY
Suspend a snapshot policy in the snapshot set. For more information, see Suspend a snapshot policy on a snapshot set.
RESUME SNAPSHOT POLICY
Resume a suspended snapshot policy in the set. For more information, see Resume a snapshot policy on a snapshot set.
DELETE SNAPSHOT IDENTIFIER 'snapshot_id'
Delete a snapshot in the snapshot set by ID. The snapshot ID is a UUID value, in the format returned by the UUID_STRING function. For more information, see Delete a snapshot from a snapshot set.
ALTER SNAPSHOT SET name MODIFY SNAPSHOT IDENTIFIER 'snapshot_id' ADD | REMOVE LEGAL HOLD
Adds or removes a legal hold from a specified snapshot within the snapshot set. For more information about legal holds for WORM snapshots, see Legal hold. For examples of using this clause, see Add and remove legal holds.
SET COMMENT = 'string_literal'
Associate a comment with the snapshot set.
UNSET COMMENT
Remove the comment from the snapshot set.
Access control requirements¶
A role used to execute this operation must have the following privileges at a minimum:
Privilege |
Description |
---|---|
OWNERSHIP |
The role used to modify a snapshot set must have the OWNERSHIP privilege on the snapshot set. |
APPLY SNAPSHOT RETENTION LOCK |
If the snapshot policy applied to a snapshot set includes a retention lock, the role used to apply the policy must have this privilege on the account. |
APPLY LEGAL HOLD |
This account privilege grants the ability to add or remove a legal hold from a snapshot. This privilege is only needed for the ADD LEGAL HOLD and REMOVE LEGAL HOLD clauses. By default, the ACCOUNTADMIN role has this privilege. |
APPLY |
Only a user with this privilege on the snapshot policy can use the ALTER SNAPSHOT SET command with the APPLY SNAPSHOT POLICY clause to add the snapshot policy to a snapshot set that already exists. |
These privileges are required on the currently active primary role, not a secondary role.
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
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.
Important
If the snapshot policy has a retention lock applied to it, and there are any unexpired snapshots in the snapshot set, then you can’t delete the snapshot set. In that case, you must wait for all the snapshots in the set to expire. This restriction applies even to privileged roles such as ACCOUNTADMIN, and to Snowflake support. For that reason, be careful when specifying retention lock and a long expiration period in a snapshot policy.
Examples¶
Manually add a snapshot to snapshot set t1_snapshots
:
ALTER SNAPSHOT SET t1_snapshots
ADD SNAPSHOT;
Update the snapshot policy for snapshot set t1_snapshots
:
ALTER SNAPSHOT SET t1_snapshots
APPLY SNAPSHOT POLICY daily_snapshot_policy;
Suspend a snapshot policy on the snapshot set t1_snapshot
:
ALTER SNAPSHOT SET t1_snapshots
SUSPEND SNAPSHOT POLICY;
Resume a snapshot policy on the snapshot set t1_snapshots
:
ALTER SNAPSHOT SET t1_snapshots
RESUME SNAPSHOT POLICY;
To find the snapshot identifier to use with the ADD LEGAL HOLD and REMOVE LEGAL HOLD clauses, you typically use the SHOW SNAPSHOTS command to list the eligible snapshots and their creation times. The following example shows how you might list the appropriate snapshots, add a legal hold to one specific snapshot, and later remove that legal hold. Substitute your own role name, snapshot set name, and snapshot identifier.
USE ROLE my_legal_hold_role; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW SNAPSHOTS IN SNAPSHOT SET my_db_snapshot_set
->> SELECT "created_on", "snapshot_id" FROM $1 WHERE "is_under_legal_hold" = 'N';
ALTER SNAPSHOT SET my_db_snapshot_set
MODIFY SNAPSHOT IDENTIFIER '790d1ee4-88b2-451f-9ccc-eacd1e93a134'
ADD LEGAL HOLD;
USE ROLE my_legal_hold_role; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW SNAPSHOTS IN SNAPSHOT SET my_db_snapshot_set
->> SELECT "created_on", "snapshot_id" FROM $1 WHERE "is_under_legal_hold" = 'Y';
ALTER SNAPSHOT SET my_db_snapshot_set
MODIFY SNAPSHOT IDENTIFIER '790d1ee4-88b2-451f-9ccc-eacd1e93a134'
REMOVE LEGAL HOLD;