Disaster Recovery, Backup and Data Replication · Topic 9 of 9

Restore Dropped Objects, Disable Time Travel and Edition Implications

Restore dropped tables, schemas and databases with UNDROP, enable or disable Time Travel at each level, and see how editions change retention limits.

  • 5 min
In a nutshell
  • UNDROP TABLE, SCHEMA and DATABASE work inside the retention period; same name clash blocks them.
  • DATA_RETENTION_TIME_IN_DAYS = 0 disables Time Travel for the object; the lower level overrides the higher one.
  • Standard allows 1 day; Enterprise and higher allow 90 days; Fail-safe is 7 days for permanent objects.

What it is

When someone drops a table, schema or database, Snowflake does not remove it at once. The object is kept for its Time Travel period, and during that period UNDROP brings it back with its data and metadata.

An administrator must also know how Time Travel is switched on and off and how the edition limits what is possible. These three topics are the daily tools for "someone dropped it, what now" scenarios.

DROP TABLEkept forRetention periodTime TravelUNDROPRestored tablesame dataelseFail safeSupport only

How it works

Restoring dropped objects

UNDROP TABLE sales.public.orders;
UNDROP SCHEMA sales.public;
UNDROP DATABASE sales;

Rules that appear in scenarios:

  • UNDROP works only while the object is still inside its Time Travel period. After that the object is in Fail-safe (for permanent objects), and only Snowflake Support can help.
  • The retention that counts is the one set at the time of the drop.
  • If an object with the same name now exists, UNDROP fails. Rename the new object, or rename the dropped one after restoring.
  • Dropping and undropping a schema or database brings back the objects in it as they were.
  • If the same name was dropped several times, UNDROP restores the most recent drop first.
  • SHOW TABLES HISTORY, SHOW SCHEMAS HISTORY and SHOW DATABASES HISTORY list dropped objects that are still recoverable.
SHOW TABLES HISTORY LIKE 'ORDERS' IN SCHEMA sales.public;
ALTER TABLE sales.public.orders RENAME TO orders_new;
UNDROP TABLE sales.public.orders;
SituationAction
Table dropped an hour ago, retention 1 dayUNDROP TABLE
Table dropped, a new table has the same nameRename the new one, then UNDROP TABLE
Database dropped, retention was 0Not recoverable by you; Fail-safe may exist for permanent objects, via Snowflake Support
Rows deleted, table still existsQuery or clone with AT or BEFORE; there is nothing to undrop
Transient table dropped after 1 dayGone; transient tables have no Fail-safe
Watch out

CREATE OR REPLACE TABLE drops the old table and creates a new one with the same name. The old table is kept in Time Travel and shows in SHOW TABLES HISTORY; rename the new table, then UNDROP the old one.

Enabling and disabling Time Travel

Time Travel is always part of the platform, so you do not install it. What you control is the retention period.

  • Disable for an object by setting DATA_RETENTION_TIME_IN_DAYS = 0. After that, nothing can be queried at an earlier point and UNDROP is not possible for that object.
  • Enable or extend by setting a value above 0, within the limit your edition and object type allow.
  • Set at a level. The setting inherits down: account, then database, schema and table. A lower level overrides the one above.
ALTER SCHEMA staging SET DATA_RETENTION_TIME_IN_DAYS = 0;
ALTER TABLE sales.public.orders SET DATA_RETENTION_TIME_IN_DAYS = 14;
Reason to disableReason to extend
Staging or scratch data that can be reloadedFinance or audit tables that may need a late correction
Lower storage costTime to notice a bad change, such as a weekend
Very high change rate tablesTables used as the source for clones or for a recovery plan
Exam tip

In a multi level scenario, remember the lowest level wins. A table set to 90 days inside a database set to 1 day keeps 90 days, if the edition allows it.

Tables that feed streams may keep data longer. A stream extends the retention of its source table up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14 days) so that it does not go stale, and this extra storage is billed.

Edition implications

EditionMaximum Time Travel (permanent objects)Fail-safe
Standard1 day7 days
Enterprise90 days7 days
Business Critical90 days7 days
Virtual Private Snowflake90 days7 days

For transient and temporary objects the limit is 1 day on every edition, with no Fail-safe.

Consequences:

  • A requirement of "30 days of recovery" cannot be met on Standard Edition with Time Travel. Choose an edition upgrade, or use backups or replication for the long retention.
  • Values above 1 are not allowed on Standard Edition.
  • Higher retention is not free. Storage grows with the change rate and the number of days, so a design needs a cost check as well as an edition check.
RequirementN days recoveryN is 1 or lessStandard is enough0 or 1 dayN above 1Enterprise or higherup to 90 dayslonger or lockedBackupspolicy and retention lock

Common mistakes

Expecting UNDROP after the retention ended. Past that point only Support can recover permanent objects, and transient ones are lost.

Ignoring a name clash. The undrop fails until the name is free.

Setting retention to 0 to save money on a critical table. It also removes the undrop safety net.

Quoting 90 days for Standard Edition. The limit is 1 day.

Using Time Travel as a long term archive. Use backups for retention that must last.

What to remember for the exam

  • UNDROP TABLE, SCHEMA and DATABASE work inside the retention period; same name clash blocks them.
  • DATA_RETENTION_TIME_IN_DAYS = 0 disables Time Travel for the object; the lower level overrides the higher one.
  • Standard allows 1 day; Enterprise and higher allow 90 days; Fail-safe is 7 days for permanent objects.
  • Transient and temporary objects: 0 or 1 day, no Fail-safe.
  • After CREATE OR REPLACE, the old table is a dropped table: rename the new one, then UNDROP the old one.

Check yourself

  • A table was dropped and a new table with the same name was created. How is the old one restored?

    Rename the new table, then run UNDROP TABLE for the old one.

  • How is Time Travel disabled for a schema?

    Set DATA_RETENTION_TIME_IN_DAYS to 0 on that schema.

  • A Standard Edition account needs 14 days of recovery. What are the options?

    Upgrade to Enterprise or higher for longer Time Travel, or add backups for the long retention.

Topic 9 of 9 in Disaster Recovery, Backup and Data Replication

Test yourself on Disaster Recovery, Backup and Data Replication

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.