Undropping Managed Tables
INVALID_STATE.RESTORATION_PERIOD_EXPIRED
Summary
Error Overview: Insights into why the
INVALID_STATE.RESTORATION_PERIOD_EXPIREDerror occurs within Databricks and Unity Catalog environments.Troubleshooting Steps: Step-by-step troubleshooting process to identify and resolve Databricks table restoration issues, using commands like
SHOW TABLES DROPPED.Restoration with Alternative Names: Strategies to avoid naming conflicts during the restoration of Databricks tables by employing alternative names.
Understanding the Error
[RequestId=<GUID> ErrorClass=INVALID_STATE.RESTORATION_PERIOD_EXPIRED] Cannot undrop table because the table was deleted before the maximum supported restorable period of 7 days.
When working with data in Databricks, particularly with the Unity Catalog managed tables, users may encounter the INVALID_STATE.RESTORATION_PERIOD_EXPIRED error. This error suggests an attempt to recover a table that was deleted more than 7 days ago, exceeding the maximum restorable period supported by Databricks. However, may not always be the case.
This post won’t help you address tables that were dropped after the 7 day restoration period. It will help with errors within that 7 day window
The INVALID_STATE.RESTORATION_PERIOD_EXPIRED error occurs under the following conditions:
Time Limit Exceeded: The primary reason is attempting to undrop a table after the 7-day restoration window has closed.
Recreated Table: If a table with the same name has been recreated after being dropped, it can complicate the restoration process.
Table Name Issues: Mistakes in the table name during the restoration command might lead to failure in identifying the correct dropped table.
Solution
Troubleshooting Steps
To effectively resolve the INVALID_STATE.RESTORATION_PERIOD_EXPIRED error, follow these detailed steps:
- Verify the Restoration Window
First, ensure that the table was dropped within the last 7 days. If the table was dropped earlier, it cannot be recovered using the UNDROP command.
- Use SHOW TABLES DROPPED
To identify the correct table and verify the drop date, use the SHOW TABLES DROPPED command:
SHOW TABLES DROPPED IN my_schema;This command will list all the dropped tables in the specified schema along with their metadata including the drop time and table ID.
- Check for Recreated Tables
If a table with the intended name has been recreated, it may not be immediately visible that the original table has been dropped. Confirm the status of the table names and consider using different names for newly created tables to avoid confusion.
- Restore with Alternative Names
If restoration is required but the original name is taken or to avoid confusion, use the ALTER TABLE RENAME TO command before executing the UNDROP command to assign a new name to the existing table:
ALTER TABLE existing_table RENAME TO new_table_name; -- Rename the existing table
UNDROP TABLE WITH ID 'your_table_id_here'; -- Restore the dropped table- Correct Usage of Table IDs
When attempting to recover a specific table, use the table ID retrieved from the SHOW TABLES DROPPED command. This approach minimizes the risk of errors related to table names:
UNDROP TABLE WITH ID 'your_table_id_here';