Undrop a Table, Database or Schema

Hopefully you had a chance to read our previous posts #10, Query Results Sets available in history and #9 Ability to connect with JDBC, As promised in the original top 10 post, we continue the series with a deeper dive into another of the Top 10 cool features from Snowflake:

Have you ever accidentally dropped a table? Maybe even a table with hundreds of millions of rows? Ouch. Or maybe someone ran the wrong script with a DROP in Production. And then you have to rush to the backup only to discover that the backup is 24 hours old! And then when you restore, it takes 3 times as long as it did to do the original backup, and even then the restored copy is 24 hours old.

UNDROP is a really cool feature in Snowflake that makes this so much easier to retrieve your data, because we store encrypted versions of the data and objects for 24 hours by default (with an option to purchase even longer retention periods if you need it).

With the UNDROP feature in Snowflake you can recover that lost data instantaneously with a single command:

UNDROP TABLE <tablename>

No need to reload last night's backup to do the restore. No need to wait while all that data is pulled back in. No need to find the DBA who knows how to do it and has done it before.

It just happens!

In addition to UNDROP TABLE, we also have UNDROP SCHEMA and UNDROP DATABASE for when someone makes an even bigger goof (it really does happens - “really I thought I was logged into the dev database!”).

Again these are simple commands:

UNDROP SCHEMA <schema name>

UNDROP DATABASE <db name>

Both of these instantaneously restore all the objects that were previously dropped.  UNDROP SCHEMA recovers all the tables, views, and sequences that were in that schema.  While UNDROP DATABASE recovers all the schemas, tables, views, stages areas,  and sequences in that database.

For example: here is a sample  database history display (the result of running SHOW DATABASES HISTORY) that shows database SALES_DEV was dropped on 2/23/2016 at 8:49 AM. Oops.

Here is the history again after an we execute the command UNDROP DATABASE SALES_DEV using the Snowflake Web UI:

In the Worksheet log you can see that the UNDROP was executed at 10:58 AM and took all of 591 milliseconds to execute. In the database history readout you can see that the “dropped_on” column now shows NULL for SALES_DEV indicating it is available for use again.

Very few databases allow you to UNDROP a table, schema or a database, this easily and quickly, using a simple SQL command prompt.

Now that is a huge time (and life) saver. Ask anyone who has dropped a production database or table by accident. And that is why it made the Top 10 list of really cool features in the Snowflake Elastic Data Warehouse.

As always, keep an eye on this blog site, our Snowflake Twitter feed (@SnowflakeDB), (@kentgraziano), and (@cloudsommelier), and our LinkedIn page for more Top 10 Cool Things About Snowflake and for updates on all the action and activities here at Snowflake Computing.

Kent Graziano and Saqib Mustafa