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
UNDROP TABLE,SCHEMAandDATABASEwork inside the retention period; same name clash blocks them.DATA_RETENTION_TIME_IN_DAYS = 0disables 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.
How it works
Restoring dropped objects
UNDROP TABLE sales.public.orders;
UNDROP SCHEMA sales.public;
UNDROP DATABASE sales;
Rules that appear in scenarios:
UNDROPworks 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,
UNDROPfails. 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,
UNDROPrestores the most recent drop first. SHOW TABLES HISTORY,SHOW SCHEMAS HISTORYandSHOW DATABASES HISTORYlist 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;
| Situation | Action |
|---|---|
| Table dropped an hour ago, retention 1 day | UNDROP TABLE |
| Table dropped, a new table has the same name | Rename the new one, then UNDROP TABLE |
| Database dropped, retention was 0 | Not recoverable by you; Fail-safe may exist for permanent objects, via Snowflake Support |
| Rows deleted, table still exists | Query or clone with AT or BEFORE; there is nothing to undrop |
| Transient table dropped after 1 day | Gone; transient tables have no Fail-safe |
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 andUNDROPis 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 disable | Reason to extend |
|---|---|
| Staging or scratch data that can be reloaded | Finance or audit tables that may need a late correction |
| Lower storage cost | Time to notice a bad change, such as a weekend |
| Very high change rate tables | Tables used as the source for clones or for a recovery plan |
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
| Edition | Maximum Time Travel (permanent objects) | Fail-safe |
|---|---|---|
| Standard | 1 day | 7 days |
| Enterprise | 90 days | 7 days |
| Business Critical | 90 days | 7 days |
| Virtual Private Snowflake | 90 days | 7 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.
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,SCHEMAandDATABASEwork inside the retention period; same name clash blocks them.DATA_RETENTION_TIME_IN_DAYS = 0disables 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, thenUNDROPthe 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 TABLEfor the old one.How is Time Travel disabled for a schema?
Set
DATA_RETENTION_TIME_IN_DAYSto 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.