Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Recover a Dropped Table in Snowflake Quickly

To recover a dropped table in Snowflake, use the UNDROP TABLE <table_name> command within the Time Travel retention period. This command restores the table to its state before it was dropped.
📐

Syntax

The UNDROP TABLE command restores a dropped table if it is within the Time Travel retention period. You need to specify the exact table name that was dropped.

  • UNDROP TABLE <table_name>: Recovers the dropped table.
sql
UNDROP TABLE my_table;
💻

Example

This example shows how to drop a table and then recover it using UNDROP TABLE. It demonstrates the recovery of the table within the allowed Time Travel period.

sql
CREATE OR REPLACE TABLE my_table (id INT, name STRING);
INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob');

-- Drop the table
DROP TABLE my_table;

-- Recover the dropped table
UNDROP TABLE my_table;

-- Query the recovered table
SELECT * FROM my_table;
Output
ID | NAME ---|------- 1 | Alice 2 | Bob
⚠️

Common Pitfalls

Common mistakes when recovering dropped tables include:

  • Trying to undrop a table after the Time Travel retention period has expired (default 1 day).
  • Using incorrect table names or schema references.
  • Not having sufficient privileges to undrop the table.

Always check your Time Travel retention settings and confirm the table name before running UNDROP TABLE.

sql
/* Wrong: Trying to undrop a table after retention period */
UNDROP TABLE old_table; -- Fails if retention expired

/* Right: Undrop within retention period */
UNDROP TABLE recent_table;
📊

Quick Reference

CommandDescription
UNDROP TABLE Restores a dropped table within Time Travel period
DROP TABLE Deletes a table but allows recovery within retention
SHOW TABLESLists tables to verify existence or recovery
SELECT * FROM Query data from the recovered table
✅

Key Takeaways

Use UNDROP TABLE to recover dropped tables within Snowflake's Time Travel retention period.
The default Time Travel retention period is 1 day but can be configured per table or account.
Ensure you have the correct table name and privileges before running UNDROP TABLE.
Dropped tables cannot be recovered after the Time Travel retention period expires.
Verify table recovery by querying the table after undropping.