How to Restore a Backup in Snowflake

How do I restore a backup in Snowflake using Time Travel or UNDROP?

Snowflake restore lets you recover dropped or point-in-time objects using Time Travel, Fail-safe, and zero-copy clones.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the quickest way to restore a Snowflake table?

Use Time Travel’s UNDROP or CLONE to recover the table instantly. UNDROP rebuilds the original object, while CLONE creates a new copy at a chosen timestamp.

How does UNDROP work?

Snowflake keeps dropped objects for your account’s DATA_RETENTION_TIME.Running UNDROP TABLE restores the object exactly as it was at the drop moment, including data and permissions.

When should I choose a point-in-time clone?

Pick CLONE when you need to test fixes without overwriting production. A zero-copy clone shares storage with the source but presents an independent object.

Syntax essentials

Specify the object, timestamp, or offset. Wrap timestamps in quotes and set the time zone if needed.

Example recovery flow

1) Detect the bad DELETE on Orders.2) Identify the safe timestamp. 3) Run CREATE TABLE Orders_fix CLONE Orders AT (TIMESTAMP => '2023-10-01 12:00:00'). 4) Swap tables or merge rows.

Best practices for reliable restores

• Verify retention period is long enough for your RPO.
• Store timestamps of risky deployments.
• Use CLONE for hotfixes; UNDROP for simple rollbacks.
• Grant OWNERSHIP only to controlled roles.

What’s the cost of restoring?

UNDROP costs zero extra storage; CLONE adds storage only for new changes after creation.Compute charges apply while querying or copying restored data.

How do I restore an entire schema or database?

The same commands work at higher levels. Use UNDROP SCHEMA or CREATE DATABASE db_clone CLONE mydb AT (OFFSET => -3600) to roll back one hour.

Can I restore after retention expires?

After Time Travel, Fail-safe allows Snowflake support to recover data for seven additional days, but expect longer wait times and a support ticket.

Why How to Restore a Backup in Snowflake is important

How to Restore a Backup in Snowflake Example Usage


--Developer accidentally deleted high-value rows from Orders.
--Step 1: Find safe timestamp (15 minutes ago)
SELECT CURRENT_TIMESTAMP() AS now;

--Step 2: Restore into a new table
CREATE TABLE Orders_rollback CLONE Orders AT (OFFSET => -900);

--Step 3: Merge fixed data back or swap tables
ALTER TABLE Orders RENAME TO Orders_corrupted;
ALTER TABLE Orders_rollback RENAME TO Orders;

How to Restore a Backup in Snowflake Syntax


--Restore a dropped table within retention window
UNDROP TABLE Orders;

--Create point-in-time clone of Orders one hour ago
CREATE TABLE Orders_clone CLONE Orders AT (OFFSET => -3600);

--Clone entire database at exact timestamp
CREATE DATABASE Store_clone CLONE StoreDB AT (TIMESTAMP => '2023-10-01 00:00:00');

--Swap restored table into production
ALTER TABLE Orders RENAME TO Orders_bad;
ALTER TABLE Orders_clone RENAME TO Orders;

Common Mistakes

Relying solely on Fail-safe. Fail-safe requires a Snowflake support ticket and can take hours. Always try Time Travel first within DATA_RETENTION_TIME.
Overwriting production with a clone. Users often RENAME the clone over the live table without archiving the bad version. Always keep a backup by renaming or cloning first.