Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use UNDROP Command in Snowflake to Restore Dropped Objects

In Snowflake, you use the UNDROP command to restore a dropped table, schema, or database within the data retention period. The syntax is UNDROP OBJECT_TYPE OBJECT_NAME, where OBJECT_TYPE can be TABLE, SCHEMA, or DATABASE. This command recovers the object with its original data and structure.
📐

Syntax

The UNDROP command syntax in Snowflake is simple and specific to the object type you want to restore.

  • OBJECT_TYPE: The type of object to restore. It can be TABLE, SCHEMA, or DATABASE.
  • OBJECT_NAME: The exact name of the dropped object you want to recover.

Example syntax:

UNDROP TABLE my_table;
sql
UNDROP TABLE my_table;
💻

Example

This example shows how to drop a table and then restore it using UNDROP. It demonstrates recovering the table with all its data intact.

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

DROP TABLE my_table;

-- Now restore the dropped table
UNDROP TABLE my_table;

-- Check data after undrop
SELECT * FROM my_table;
Output
ID | NAME ---|------- 1 | Alice 2 | Bob
⚠️

Common Pitfalls

Common mistakes when using UNDROP include:

  • Trying to undrop an object after the data retention period has expired (default 1 day for tables).
  • Using incorrect object names or types, which causes errors.
  • Attempting to undrop objects that were never dropped or already restored.

Always verify the object name and ensure you are within the retention window.

sql
/* Wrong: Using wrong object type */
UNDROP TABLE my_schema; -- Error: my_schema is a schema, not a table

/* Right: Use correct object type */
UNDROP SCHEMA my_schema;
📊

Quick Reference

CommandDescription
UNDROP TABLE Restores a dropped table within retention period
UNDROP SCHEMA Restores a dropped schema within retention period
UNDROP DATABASE Restores a dropped database within retention period
✅

Key Takeaways

Use UNDROP to restore dropped tables, schemas, or databases within the retention period.
Specify the correct object type and exact name when using UNDROP.
Dropped objects can only be restored if they are still within Snowflake's data retention window.
UNDROP recovers the object with its original data and structure intact.
Verify object existence and retention before attempting to undrop.