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, orDATABASE. - 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
| Command | Description |
|---|---|
| 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.
