1

I want to use snowflake Task scheduler to clone one or all of the DB's with dynamic clone DB name something like below,Is it possible to do it without creating Stored procedure.As I have multiple DB under my account I would prefer to clone all of the DB's in one task

create database xx_date clone xx

I appreciate your response

Thanks,

goodbadboys9
  • 11
  • 2

1 Answers1

2

Is it possible to do it without creating a Stored Procedure

The CREATE TASK statement syntax only allows for a single SQL statement to be specified, and the CREATE … CLONE statement syntax does not permit specifying more than one object at a time.

Given the above, this isn't possible currently. You will need to use an iteration of database names from within a stored procedure call. The same stored procedure can also be used to clean up older dated clones from previous task invocations.

For incorporating dates into a dynamically generated statement within the stored procedure, checkout this question.

P.s. If the underlying goal of the numerous clones is to maintain backups, also consider cross-account, cross-region (and/or) cross-cloud replication for better safety.

  • Thanks Harsh.I dont like tying backup SP in user Databases bit I guess we need to make exception or create one database for these king of tasks.I will look into cross cloud replication too.Thank you very much for your prompt response – goodbadboys9 Jun 04 '20 at 18:52