Backups for disaster recovery and immutable storage¶

Backup¶

A backup represents a point-in-time snapshot of an object.

• The object can be a single table, a schema, or an entire database.

• A specific backup can be identified by a unique ID generated by Snowflake.

• A backup can’t be modified. It can, however, be deleted, and the backup expiration period can be modified (unless a retention lock is applied).

During day-to-day operations, you rarely interact with individual backups. Instead, you manage the backup sets that contain them. For example, you get a list of backups by running the SHOW BACKUPS IN BACKUP SET command. You create a new backup by running an ALTER BACKUP SET command.

Backup set¶

A backup set is a schema-level object that contains a set of backups for a specific database, schema, or table. Snowflake has SQL commands to CREATE, ALTER, DROP, SHOW, and DESCRIBE backup sets.

You can have multiple backup sets for the same object.

The life cycle of the backups within a set is determined by an optional backup policy that you can attach to the backup set. You can also add or delete backups manually in a backup set. Your ability to delete backups is affected by other factors, in particular retention lock and legal hold.

Backup policy¶

A backup policy is a schema-level object that contains the settings that define the life cycle of the backups within a backup set. These settings include schedule, expiration, and retention lock.

• The schedule determines when backups are created. The schedule can be defined as an interval in minutes, or as a cron expression. For example, if the schedule is set to one hour, a backup of the object is taken every 60 minutes.

• The expiration period is the length of time the backup is valid. After a backup expires, Snowflake deletes it automatically, unless a legal hold is applied to that particular backup.

  Tip

  If the backup set doesn’t have a retention lock and the particular backup doesn’t have a legal hold applied, you can delete the backup manually before the end of the expiration period. You can manually delete backups one at a time, always starting with the oldest backup that doesn’t have a legal hold.

Each backup policy must have one or both of the schedule and expiration period properties. For example, you can create a policy with a schedule and an expiration period, and let Snowflake handle all creation and removal of the backups in all backup sets where that policy is applied. Alternatively, you might create a policy with a schedule and no expiration period if you want to manage removing older backups yourself. Or, you can create a policy with an expiration period but without a schedule, and then manage backup creation yourself. You can’t create a policy with no schedule and no expiration period.

If you associate a backup policy with a backup set, you can do so when you create the backup set, or you can apply the policy later. Or, you can have a backup set that doesn’t have an associated backup policy. In that case, you manually control when to take new backups and expire old ones.

You can apply a backup policy to multiple backup sets. If you modify a backup policy, Snowflake applies the changes to all backup sets that the policy is attached to.

Retention lock¶

A retention lock protects a backup from deletion for the defined expiration period. You can use a backup with a retention lock for backups for regulatory compliance and cyber resilience. The following restrictions apply for a backup set with retention lock:

• Backups can’t be deleted by any role, including the ACCOUNTADMIN role.

• You can’t decrease the backup expiration period, although you can increase the expiration period.

• You can’t drop a backup set if there are any unexpired backups in the set.

• You can’t drop a schema that contains a backup set with any unexpired backups.

• You can’t drop a database that contains a backup set with any unexpired backups.

• You can’t drop an account that contains a database with a backup set that has any unexpired backups.

Legal hold¶

The legal hold feature of Snowflake backups prevents backups from being overwritten or deleted. That way, you can preserve Snowflake databases, schemas, or tables based on your own legal requirements.

Snowflake lets you place a legal hold on specific backups. When a Snowflake backup is under legal hold, the following conditions apply:

• Nobody can modify the backup.

• Nobody can delete the backup. That’s true even if the backup has passed its EXPIRE_AFTER_DAYS period.

• Access to the backup is logged and auditable.

• The legal hold can be removed by a privileged user, unlike a retention lock.

Overview of the backup lifecycle¶

The following diagram shows how the Snowflake objects, backups, backup sets, and backup policies relate to each other. The diagram involves the simplest kind of backup: one for a single table. Each backup operation produces a new backup. All the backups for that particular object are grouped together in a backup set. The automatic addition and removal of backups in the backup set is governed by the backup policy. To recover the information from a backup, you use a CREATE command to create a new object from a specific backup.

Limitations of backups¶

Currently, you can create a maximum of two database backup sets for a specific database. Likewise, you can create a maximum of two schema backup sets for a specific schema, and two table backup sets for a specific table. An object might still appear in more than two backup sets. For example, a table might have one or two associated table backup sets. The same table might also be included in one or two schema backup sets, and one or two database backup sets.

References from tables to other objects¶

Objects, such as views or functions, can refer to objects outside the schema or database in the backup. To ensure that such references continue functioning after you restore from a backup, use one of the following strategies:

• If the tables and the other objects that they refer to are all in the same schema or the same database, create a backup set for the entire schema or database. That way, Snowflake restores all the interconnected objects at once when you restore from the backup.

• If objects in a backup set refer to objects that aren’t included in the backup set, be aware that when a backup is restored, the references from the restored objects point to the original objects from the other database or schema. If you dropped those other objects or changed their properties after taking the backup, you might encounter errors when you access the restored objects.

• For account-level objects, any references from restored objects always point to the original account-level object. That’s because the account-level objects aren’t part of any backup. For example, a schema backup might contain a secret that refers to a security integration. The security integration is an account-level object and can’t be included in any backup.

Types of objects in database and schema backups¶

The following table lists the objects that are included in a database or schema backup:

How Snowflake associates objects with their backup sets¶

When you create a backup set for a database, schema, or table, Snowflake associates the backup set with the internal ID of that database, schema, or table. If you delete the original object, you can’t add any more backups to that backup set. This behavior applies even if you recreate an object with the same name, or replace it with an object that was restored from a backup.

If you instead rename the original object, then you can continue making more backups of it by adding more backups to the same backup set. In that case, the output of SHOW BACKUP SETS changes to reflect the OBJECT_NAME value of the renamed object.

If you want to make backups of a table but you frequently drop and recreate that table, perhaps through CREATE OR REPLACE statements, include it in a backup set for the schema or database that contains the table. That way, you can keep using the same backup set regardless of changes to the table.

When you restore a table from a backup, the restored table starts with a different name than the original. Suppose that you want to completely replace the contents of the original table with the backup data, and continue to use the same backup set for more backups of that same table. In that case, use a TRUNCATE or DELETE statement to remove the contents of the original table, and an INSERT … SELECT statement to copy the data from the restored table. Don’t drop the original table and rename the restored table to the name of the original table.

Backups and encryption¶

The data within backup sets is protected by the same end-to-end encryption as other Snowflake objects and table data. For more information about Snowflake encryption, see Understanding end-to-end encryption in Snowflake.

Key rotation also applies to the data within backups.

Backups and data lineage¶

Snowflake doesn’t preserve data lineage metadata with database, schema, and table backups. After you restore an object from a backup, you can’t use Snowsight to view lineage information for the restored data.

Grant privileges required to create backup policies and sets¶

To enable the role myrole to create a backup policy in schema myschema, execute the following statement:

GRANT CREATE BACKUP POLICY ON SCHEMA policy_schema TO ROLE myrole;

To enable the role myrole to create a backup set in schema myschema, execute the following statement:

GRANT CREATE BACKUP SET ON SCHEMA policy_schema TO ROLE myrole;

Grant the APPLY privilege on a backup policy to a role¶

To enable the role myrole to apply the backup policy hourly_backup_policy to a backup set, execute the following statement:

GRANT APPLY ON BACKUP POLICY hourly_backup_policy TO ROLE myrole;

Grant the APPLY BACKUP RETENTION LOCK privilege to a role¶

You can grant a role the privilege to apply backup policies with retention lock on backup sets.

Only a user with the ACCOUNTADMIN role can grant this privilege.

To enable the role retention_lock_admin_role to apply a backup policy with retention lock on a backup set, execute the following statement:

GRANT APPLY BACKUP RETENTION LOCK ON ACCOUNT TO ROLE retention_lock_admin_role;

Create scheduled backups with retention lock¶

Create a backup set that automatically creates backups with a retention lock on a schedule. The retention lock prevents anyone, even privileged users, from deleting or modifying backups in any backup set that the policy is attached to.

Only a role that has the APPLY BACKUP RETENTION LOCK privilege on the account can create a backup policy with a retention lock.

1. Create a policy with a retention lock that creates a daily backup with an expiration period of 90 days:

   CREATE BACKUP POLICY daily_backup_policy_with_lock
     WITH RETENTION LOCK
     SCHEDULE = '1440 MINUTE'
     EXPIRE_AFTER_DAYS = 90
     COMMENT = 'regulatory backups: they have a retention lock and expire after 90 days';
   

   Copy

2. Create a backup set for table t2 with the backup policy daily_backup_policy_with_lock:

   CREATE BACKUP SET t2_backups
     FOR TABLE t2
     WITH BACKUP POLICY daily_backup_policy_with_lock;
   

   Copy

3. Create a backup set for schema s2 with the backup policy daily_backup_policy_with_lock:

   CREATE BACKUP SET s2_backups
     FOR SCHEMA s2
     WITH BACKUP POLICY daily_backup_policy_with_lock;
   

   Copy

4. Create a backup set for database d2 with the backup policy daily_backup_policy_with_lock:

   CREATE BACKUP SET d2_backups
     FOR DATABASE d2
     WITH BACKUP POLICY daily_backup_policy_with_lock;
   

   Copy

Create backups manually¶

You can manually add a backup to a backup set at any time. Doing so makes a backup of the database, schema, or table that’s associated with the backup set. You can create backups manually whether or not the backup set also has backups that are scheduled by a backup policy. If there’s a backup policy associated with the backup set, and the policy defines an expiration period, that expiration period also applies to the manual backup.

The following example creates a table backup set t1_backups and then adds the first backup to it:

CREATE BACKUP SET t1_backups FOR TABLE t1;
ALTER BACKUP SET t1_backups ADD BACKUP;

The following example creates a backup policy with hourly backups, a table backup set t2_backups that uses the policy, and then adds a manual backup to the backup set:

CREATE BACKUP POLICY hourly_backup_policy
  SCHEDULE = '60 MINUTE'
  EXPIRE_AFTER_DAYS = 7;

CREATE BACKUP SET t2_backups FOR TABLE t2 WITH BACKUP POLICY hourly_backup_policy;
-- Wait several hours. Then the backup set already contains several scheduled backups.
-- You can manually add a backup at any time, in addition to the scheduled backups.
ALTER BACKUP SET t2_backups ADD BACKUP;

You can run similar commands to add a backup to a schema or database backup set. Substitute the name of the schema or database backup set in the ALTER BACKUP SET command.

Suspend a backup policy on a backup set¶

When you suspend a backup policy on a backup set, you prevent the backup policy from being used to create new scheduled backups in that backup set. You also suspend the expiration of existing backups in that backup set that use the backup policy. Other backup sets that use the same policy aren’t affected.

The following example suspends a backup policy on the backup set t2_backups:

ALTER BACKUP SET t2_backups SUSPEND BACKUP POLICY;

Copy

You can also selectively suspend just the creation or just the expiration processes of the backup set. The following example suspends the creation of new backups in the backup set t3_backups, and suspends expiration of old backups from the backup set t4_backups:

ALTER BACKUP SET t3_backups SUSPEND BACKUP CREATION POLICY;
ALTER BACKUP SET t4_backups SUSPEND BACKUP EXPIRATION POLICY;

Copy

For more information about the ALTER BACKUP SET command, see ALTER BACKUP SET.

Resume a backup policy on a backup set¶

You can resume suspended backup policies. Doing so resumes the creation and expiration of backups according to the backup policy. If any backups reached their expiration time while the policy was suspended, Snowflake deletes those backups as soon as the policy is resumed.

The following example resumes a backup policy on the backup set t1_backup:

ALTER BACKUP SET t1_backups
  RESUME BACKUP POLICY;

You can also selectively resume just the creation or just the expiration processes of the backup set. The following example resumes the creation of new backups in the backup set t3_backups, and resumes expiration of old backups from the backup set t4_backups:

ALTER BACKUP SET t3_backups SUSPEND BACKUP CREATION POLICY;
ALTER BACKUP SET t4_backups SUSPEND BACKUP EXPIRATION POLICY;

For more information about the ALTER BACKUP SET command, see ALTER BACKUP SET.

Restore a backup¶

You can restore an object from a backup set by using the ID of the specific backup. For example, to restore table t1 from backup set t1_backups in the current schema, execute the following statements:

1. Find the ID of the table backup to restore in the backup_id column:

   SHOW BACKUPS IN BACKUP SET t1_backups ->> SELECT "created_on", "backup_id", "expire_on" FROM $1;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+------------------------------------------+---------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 983e0b66-91eb-41cb-8a0b-037abfec1914 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | b5624ef0-1f35-452f-b132-09d8f0592e52 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | eca1a94a-fd40-46db-a2bc-4afba6a38c0a | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | d38caf14-f8a5-4ba8-a248-8287e0cdcf40 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-----------+-------------------+
   

2. Find the ID of the schema backup to restore in the backup_id column:

   SHOW BACKUPS IN BACKUP SET s1_backups;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 0a0382e1-d265-46e9-b152-4c3b2b859e65 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 8dbcf919-3393-4590-928f-5481d7f2502f | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | bd729a79-01bc-444d-a550-adaaa31ab62f | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 9a8802c5-5fbd-4200-a09d-43e046103939 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

3. Find the ID of the database backup to restore in the backup_id column:

   SHOW BACKUPS IN BACKUP SET d1_backups;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 42435925-4e77-4b01-ba89-8163ac03e12f | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 29c2c1b9-6599-4f0b-87b8-d43377fd7c77 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | a4283984-a063-4415-acc4-0e3c19259fad | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | ffe25397-64b9-4c5f-b061-23a1885dc2dc | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

4. Restore the backup for table t1 taken on 2024-08-19 18:12:33:

   CREATE TABLE restored_t1 FROM BACKUP SET t1_backups IDENTIFIER 'b5624ef0-1f35-452f-b132-09d8f0592e52';
   

   Copy

5. Restore the backup for schema s1 taken on 2024-08-19 18:12:33:

   CREATE SCHEMA restored_s1 FROM BACKUP SET s1_backups IDENTIFIER '8dbcf919-3393-4590-928f-5481d7f2502f';
   

   Copy

6. Restore the backup for database d1 taken on 2024-08-19 18:12:33:

   CREATE DATABASE restored_d1 FROM BACKUP SET d1_backups IDENTIFIER '29c2c1b9-6599-4f0b-87b8-d43377fd7c77';
   

   Copy

Delete a backup from a backup set¶

For any backup set, you can only delete the oldest backup that doesn’t have a legal hold. You do so by specifying the backup ID. You can find the backups that don’t have a legal hold by examining the is_under_legal_hold property. You can find the oldest backup by examining the created_on property.

Note

You can’t delete any backup from a backup set if a backup policy with retention lock is attached to that backup set, or if that particular backup has a legal hold applied.

The backup that you delete from the backup set must be the earliest backup in the set.

1. Find the ID of the table backup to delete in the backup_id column in the following output. Sorting in ascending order by the created_on column puts the oldest backup first. You could add LIMIT 1 to the SELECT command to return only the row with the details of the oldest backup.

   SHOW BACKUPS IN BACKUP SET t1_backups ->>
     SELECT "created_on", "backup_id", "expire_on" FROM $1
       WHERE "is_under_legal_hold" = 'N'
       ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 983e0b66-91eb-41cb-8a0b-037abfec1914 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | b5624ef0-1f35-452f-b132-09d8f0592e52 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | eca1a94a-fd40-46db-a2bc-4afba6a38c0a | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | d38caf14-f8a5-4ba8-a248-8287e0cdcf40 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

2. Delete the t1_backups backup created on 2024-08-19 17:12:28 using the backup_id:

   ALTER BACKUP SET t1_backups DELETE BACKUP IDENTIFIER '983e0b66-91eb-41cb-8a0b-037abfec1914';
   

   Copy

3. Find the ID of the schema backup to delete in the backup_id column in the following output:

   SHOW BACKUPS IN BACKUP SET s1_backups ->>
     SELECT "created_on", "backup_id", "expire_on" FROM $1 ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 46a1e22a-8557-432f-a14c-1261a4ca2b34 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 3e42fef6-b895-4055-a59f-179744d015d3 | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 7807d24e-285e-4741-b332-87c32bad5cb6 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | e022e619-ee83-45a0-b2b7-9007e284bdb3 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

4. Delete the s1_backups backup created on 2024-08-19 17:12:28 using the backup_id:

   ALTER BACKUP SET s1_backups DELETE BACKUP IDENTIFIER '28e12b8a-aab8-40a8-ae39-9a5a5f654d66';
   

   Copy

5. Find the ID of the database backup to delete in the backup_id column in the following output:

   SHOW BACKUPS IN BACKUP SET d1_backups ->>
     SELECT "created_on", "backup_id", "expire_on" FROM $1 ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | backup_id                            | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | d3a77432-c98d-4969-91a9-fffae5dd655c | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 0a0382e1-d265-46e9-b152-4c3b2b859e65 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 25e01ee0-ea9d-4bb7-af7f-f3fe87f9409e | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | a12294f5-fc63-49cf-84f1-c7b72f7664af | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

6. Delete the d1_backups backup created on 2024-08-19 17:12:28 using the backup_id:

   ALTER BACKUP SET d1_backups DELETE BACKUP IDENTIFIER 'd3a77432-c98d-4969-91a9-fffae5dd655c';
   

   Copy

7. Attempt to delete a more recent d1_backups backup created on 2024-08-19 21:12:55. Notice how Snowflake prevents you from deleting a backup other than the oldest one in the backup set.

   ALTER BACKUP SET d1_backups DELETE BACKUP IDENTIFIER '28e12b8a-aab8-40a8-ae39-9a5a5f654d66';
   

   Copy

   Backup '28e12b8a-aab8-40a8-ae39-9a5a5f654d66' cannot be deleted as it is not the oldest active backup in the backup set D1_BACKUPS.
   

Delete a backup set¶

You can delete a backup set using the DROP BACKUP SET command.

Delete the t1_backups backup set:

DROP BACKUP SET t1_backups;

Delete the s1_backups backup set:

DROP BACKUP SET s1_backups;

Delete the d1_backups backup set:

DROP BACKUP SET d1_backups;

Find all the backup sets that contain backups of a specific table¶

The following example shows how to find all the backup sets that contain a specific table inside a specific schema and database. The SHOW TABLES command uses a pipe operator to retrieve the names of the database, schema, and table and store them in variables. The SHOW BACKUP SETS output is filtered to show the backup sets that back up the database containing the table, or the schema containing the table, or that contain that single table.

The filtered output from SHOW BACKUP SETS shows that there are two database backup sets for the database my_big_important_database, one schema backup set for the schema my_big_important_database.public, and one table backup set for the table my_big_important_database.public.my_small_secondary_table.

SHOW TABLES IN SCHEMA public ->>
  SET (dname, sname, tname) =
    (SELECT "database_name", "schema_name", "name" FROM $1
      WHERE "name" = 'MY_SMALL_SECONDARY_TABLE' AND "kind" = 'TABLE');

SHOW BACKUP SETS ->> SELECT "object_kind", "name", "database_name", "schema_name", "object_name" FROM $1
  WHERE ("object_kind" = 'TABLE' AND "database_name" = $dname AND "schema_name" = $sname AND "object_name" = $tname)
    OR ("object_kind" = 'SCHEMA' AND "database_name" = $dname AND "object_name" = $sname)
    OR ("object_kind" = 'DATABASE' AND "object_name" = $dname);

+-------------+------------------+---------------------------+-------------+---------------------------+
| object_kind | name             | database_name             | schema_name | object_name               |
|-------------+------------------+---------------------------+-------------+---------------------------|
| DATABASE    | DATABASE_BACKUP  | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_BIG_IMPORTANT_DATABASE |
| DATABASE    | DATABASE_BACKUP2 | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_BIG_IMPORTANT_DATABASE |
| SCHEMA      | SCHEMA_BACKUP3   | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | PUBLIC                    |
| TABLE       | TABLE_BACKUP2    | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_SMALL_SECONDARY_TABLE  |
+-------------+------------------+---------------------------+-------------+---------------------------+

Create a backup for a table with dependencies¶

The following examples show how you might create a table backup for a table that refers to a sequence and a foreign key in a different schema. To prepare, we create the schema other_schema containing a sequence and a table. Then we create the main table in the public schema, referring to the sequence and the other table.

USE DATABASE my_big_important_database;

CREATE SCHEMA other_schema;
USE SCHEMA other_schema;

CREATE SEQUENCE my_sequence;
CREATE TABLE my_dimension_table (id INT AUTOINCREMENT PRIMARY KEY);

USE SCHEMA public;
CREATE TABLE dependent_table
(
   id INT DEFAULT my_big_important_database.other_schema.my_sequence.NEXTVAL PRIMARY KEY,
   foreign_id INT,
   FOREIGN KEY (foreign_id) REFERENCES my_big_important_database.other_schema.my_dimension_table(id)
 );

SELECT GET_DDL('TABLE','dependent_table');

The GET_DDL() output shows the references that point to the other schema:

+-------------------------------------------+
| GET_DDL('TABLE','DEPENDENT_TABLE')        |
|-------------------------------------------|
| create or replace TABLE DEPENDENT_TABLE ( |
|     ID NUMBER(38,0) NOT NULL DEFAULT MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_SEQUENCE.NEXTVAL,
|     FOREIGN_ID NUMBER(38,0),                |
|     primary key (ID),                       |
|     foreign key (FOREIGN_ID) references MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_DIMENSION_TABLE(ID)
| );                                        |
+-------------------------------------------+

Next, we create the backup set for the table and add a backup to it:

CREATE BACKUP SET dependency_experiments FOR TABLE dependent_table;
ALTER BACKUP SET dependency_experiments ADD BACKUP;
SHOW BACKUPS IN BACKUP SET dependency_experiments;

The SHOW BACKUPS output contains the backup_id value to use for the restore operation:

+-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------+
| created_on                    | backup_id                            | backup_set_name        | database_name             | schema_name  | expire_on |
|-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------|
| 2025-07-01 11:53:27.860 -0700 | 0fd44138-b571-449b-be0a-72779501f80e | DEPENDENCY_EXPERIMENTS | MY_BIG_IMPORTANT_DATABASE | OTHER_SCHEMA | NULL      |
+-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------+

We restore that table under a new name, and confirm that the restored table refers to the objects in the other schema:

CREATE TABLE restored_dependent_table FROM BACKUP SET dependency_experiments
  IDENTIFIER '0fd44138-b571-449b-be0a-72779501f80e';

SELECT GET_DDL('TABLE','restored_dependent_table');

+----------------------------------------------------+
| GET_DDL('TABLE','RESTORED_DEPENDENT_TABLE')        |
|----------------------------------------------------|
| create or replace TABLE RESTORED_DEPENDENT_TABLE ( |
|     ID NUMBER(38,0) NOT NULL DEFAULT MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_SEQUENCE.NEXTVAL,
|     FOREIGN_ID NUMBER(38,0),                         |
|     foreign key (FOREIGN_ID) references MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_DIMENSION_TABLE(ID),
|     primary key (ID)                                 |
| );                                                 |
+----------------------------------------------------+

To illustrate what happens if the referred-to object no longer exists, we drop the sequence and then restore the table again from the same backup:

DROP SEQUENCE my_big_important_database.other_schema.my_sequence;
CREATE TABLE OR REPLACE restored_dependent_table FROM BACKUP SET dependency_experiments
  IDENTIFIER '0fd44138-b571-449b-be0a-72779501f80e';

SELECT * FROM restored_dependent_table;

Querying the table still works:

+----+------------+
| ID | FOREIGN_ID |
|----+------------|
+----+------------+
0 Row(s) produced. Time Elapsed: 0.129s

However, operations such as GET_DDL(), DESCRIBE, and INSERT all fail because they depend on a sequence that no longer exists:

SELECT GET_DDL('TABLE','restored_dependent_table');

002073 (02000): SQL compilation error:
Sequence used as a default value in table 'MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.RESTORED_DEPENDENT_TABLE'
  column 'ID' was not found or could not be accessed.

DESC TABLE restored_dependent_table;

+------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------+
| name       | type         | kind   | null? | default                                | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------|
| ID         | NUMBER(38,0) | COLUMN | N     | [sequence cannot be found or accessed] | Y           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FOREIGN_ID | NUMBER(38,0) | COLUMN | Y     | NULL                                   | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------+

INSERT INTO restored_dependent_table (foreign_id) VALUES (2);

002073 (02000): SQL compilation error:
Sequence used as a default value in table 'MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.RESTORED_DEPENDENT_TABLE'
  column 'ID' was not found or could not be accessed.

Create a backup for a dynamic table¶

A dynamic table always involves a reference to some other table. For that reason, you might prefer to use schema backups or database backups for dynamic tables, so that the original table and the dynamic table can be included in the same backup.

If you make a table backup for a dynamic table, you include the keyword DYNAMIC in the CREATE BACKUP SET command, and in the CREATE TABLE command when you restore from a backup. The following example sets up the dynamic table, a table backup set for that table, and creates the first backup:

CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '1 minute'
  WAREHOUSE = my_wh
  AS SELECT * FROM my_base_table WHERE col1 IS NOT NULL;

CREATE BACKUP SET dynamic_table_backups
  FOR DYNAMIC TABLE my_dynamic_table;

ALTER BACKUP SET dynamic_table_backups ADD BACKUP;

The following example shows how to determine the backup IDs for backups created at various times. In this case, the newest backup is the first row in the result set. Then you use the ID of the backup in the CREATE DYNAMIC TABLE command.

SHOW BACKUPS IN BACKUP SET dynamic_table_backups
  ->> SELECT "created_on", "backup_id" FROM $1
        ORDER BY "created_on" DESC;

CREATE DYNAMIC TABLE restored_dynamic_table
  FROM BACKUP SET dynamic_table_backups
    IDENTIFIER '<backup_id_from_SHOW_BACKUPS_output>';

Add and remove legal holds¶

Before you work with legal holds for Snowflake backups, learn their purpose and requirements. For more information, see Legal hold.

Suppose that your organization’s legal or compliance team sends a litigation hold request, specifying what types of data need to be preserved. In that case, you might follow a process as follows:

• You work with the legal team to identify where the relevant data is stored, and which backup sets contain the associated objects.

• You put a legal hold on a backup from the applicable timeframe within a backup set. Doing so disables any automatic expiration for that backup. You can put a legal hold on a backup that Snowflake created automatically based on a schedule, or that you created manually. The legal hold applies whether or not the backup set has an associated backup policy, or an expiration period, or a retention lock.

• You perform refresh operations for any secondary accounts where the database containing the backup set is replicated. That way, the legal hold and associated backup are preserved across any failover and failback operations.

• You use the Snowflake access controls and logs to audit access to the data that’s under the legal hold.

• Once the legal case concludes and the legal team approves removing the legal hold, a user with the APPLY LEGAL HOLD privilege releases the legal hold. Then the normal automation for expiry resumes.

This example shows the sequence of SQL commands you might use during the lifecycle of a legal hold for a backup within a particular backup set. You find the identifier of the relevant backup by using the SHOW BACKUPS IN BACKUP SET command, and checking the "is_under_legal_hold" column to see if a legal hold is already in place. Then you add or remove the legal hold from the specific backup.

USE ROLE <role_name>; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW BACKUPS IN BACKUP SET <backup_set_name>
  ->> SELECT * FROM $1 WHERE "is_under_legal_hold" = 'N';
ALTER BACKUP SET <backup_set_name>
  MODIFY BACKUP IDENTIFIER '<backup_identifier>'
  ADD LEGAL HOLD;

USE ROLE <role_name>; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW BACKUPS IN BACKUP SET <backup_set_name>
  ->> SELECT * FROM $1 WHERE "is_under_legal_hold" = 'Y';
ALTER BACKUP SET <backup_set_name>
  MODIFY BACKUP IDENTIFIER '<backup_identifier>'
  REMOVE LEGAL HOLD;

Replicate backup-related objects¶

When you use replication in combination with database, schema, and table backups, you specify the databases that contain the backup sets and backup policies in your replication groups and failover groups. You can control how your backup-related objects are replicated by organizing your replication groups and failover groups, and choosing which databases and schemas contain your backup sets and backup policies. For more information, see Backup replication for database, schema, and table backups.

The backup sets and backup policies are database objects. Snowflake replicates the backup sets and backup policies along with the databases and schemas that contain them.

Snowflake minimizes the time and storage usage for backups by using a mechanism similar to cloning, so that each backup doesn’t require a complete new copy of all the table data. If the backup set is part of a different failover group than the database, schema, or table that the backup set applies to, there’s a one-time full transfer of the data for the first refresh of that replication group or failover group.

When a replication group or failover group includes a backup set, the increase in refresh latency is proportional to the number of backups created since the last refresh.

If you define an expiry period for older backups, the automatic deletion happens on the primary account. Those expired backups are removed from the secondary account when you perform a refresh operation.

Therefore, you can fine-tune the replication for backup-related objects by following these practices:

• To minimize the chance of refresh failures, put the backup set and the optional backup policy within the same database and schema. If that’s not practical, put those things in the same replication group or failover group. That way, all these related objects are replicated at the same time.

• To maximize flexibility about which backup-related objects are replicated and the replication schedule, put the backup set and optional backup policy in a different database than the associated database, schema, or table. That way, you can specify whether the backup-related objects are replicated, and how often.

• If you apply a policy with an expiry period for older backups, make the expiry period longer than the interval between replication refresh operations. That way, every new backup is replicated to the secondary account at least once before it expires.

• Immediately after adding a legal hold, perform refresh operations on all secondary accounts where the database that contains the backup set is replicated. That way, the legal hold and associated backup are preserved across any failover and failback operations.

• Suppose that you put a backup policy and an associated backup set into databases that are part of different replication groups or failover groups. In that case, make sure to do the initial refresh of the group containing the backup policy first. Otherwise, the refresh operation fails because you can’t create a backup set that’s missing its associated backup policy in the secondary account.

  Note

  Putting the backup set into a different replication group or failover group than the object of the backup set does require a full transfer of all the data, during the first refresh of the group that contains the backup set.

  If you restore a schema backup or database backup on a secondary account, references to objects within the restored schema or database might not resolve properly unless the referenced objects are part of the same failover group as the backup.

Monitor backups and backup operations¶

You can determine which backup-related objects exist, their properties, and how much storage they use by querying the following views.

Information schema:

• BACKUP_POLICIES view

• BACKUP_SETS view

• BACKUPS view

Account usage:

• BACKUP_OPERATION_HISTORY view

• BACKUP_POLICIES view

• BACKUP_SETS view

• BACKUP_STORAGE_USAGE view

• BACKUPS view

Organization usage:

• BACKUP_OPERATION_HISTORY view

• BACKUP_POLICIES view

• BACKUP_SETS view

• BACKUPS view

Backup policy¶

• CREATE BACKUP POLICY

• ALTER BACKUP POLICY

• DROP BACKUP POLICY

• SHOW BACKUP POLICIES

Backup set¶

• CREATE BACKUP SET

• ALTER BACKUP SET

• DROP BACKUP SET

• SHOW BACKUP SETS

Backups¶

You don’t run an actual CREATE BACKUP command. To create a new backup, you run ALTER BACKUP SET … ADD BACKUP. Or when you associate the backup set with a backup policy that has a schedule, Snowflake automatically creates backups in the backup set based on the specified schedule. To delete an older backup, you run ALTER BACKUP SET … DELETE BACKUP. Such operations require you to specify the identifier for a specific backup. You can find the backup identifiers, along with other information such as when each backup was created, by using the following command.

• SHOW BACKUPS IN BACKUP SET

Restoring objects from backups¶

You use the syntax CREATE object_kind FROM BACKUP SET to restore each kind of object from the appropriate kind of backup set.

Further backups in the backup set use the original object, not the restored one. That’s true even if you rename the restored object to the same name as the original object. If you want to continue using the same backup set after doing a restore, you restore the object under a new name and then transfer data back to the original object.

• CREATE DATABASE FROM BACKUP SET

• CREATE SCHEMA FROM BACKUP SET

• CREATE TABLE FROM BACKUP SET

Views¶

The following system views contain metadata related to backups, backup sets, and backup policies.