Snowflake database has a lot of features and benefits over the
traditional database, one of the features is undrop database, assume you
changed your mind after dropping a database, you can undo it using undrop
database command,
let's see how
exactly it works. this particular feature is based on the time travel feature
of the snowflake, a schema, database or table can be restored within the
parameter value of "data retention period", the default value of the
same is 24 hours or 1 day, and it can set up to 90 days for the enterprise edition.
Undrop feature can be applied
to the table, schema or database, here we will discuss database example
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
|
TECHNODB | ACCOUNTADMIN
| 1 |
+-----------------------+----------------+----------------+
These are the
current databases in the snowflake instance, now I will drop the technodb
snowflake database
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>drop database TECHNODB;
+--------------------------------+
|
status
|
|--------------------------------|
|
TECHNODB successfully dropped. |
+--------------------------------+
1
Row(s) produced. Time Elapsed: 0.610s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select
database_name , database_owner, retention_time from databases;
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
+-----------------------+----------------+----------------+
2
Row(s) produced. Time Elapsed: 0.639s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
Here I have
dropped a database technodb, you can from the above logs, now we have only two databases
instead of three
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>undrop database TECHNODB;
+------------------------------------------+
|
status
|
|------------------------------------------|
|
Database TECHNODB successfully restored. |
+------------------------------------------+
1
Row(s) produced. Time Elapsed: 0.491s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
select
database_name , database_owner, retention_time from databases;
+-----------------------+----------------+----------------+
|
DATABASE_NAME | DATABASE_OWNER |
RETENTION_TIME |
|-----------------------+----------------+----------------|
|
EXERCISE_DB | ACCOUNTADMIN
| 1 |
|
SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN |
1 |
|
TECHNODB | ACCOUNTADMIN
| 1 |
+-----------------------+----------------+----------------+
3
Row(s) produced. Time Elapsed: 0.783s
technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>
Here we see, that the database has been restored back with the undrop database command, In this way, we
can do it for table and schema as well.
0 comments:
Post a Comment