I want to copy objects and data from one schema in Production azure sql database into UAT in regular intervals

Question

I have data base with multiple schemas in Azure sql . I want to copy data and objects from only one schema to UAT and schedule this process weekly.

Answer 1

@Nishar Rehman
You can use the copy activity in Azure Data factory. https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory You can use scheduled triggers to run the pipeline weekly.  https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers This article provides information about how to execute a pipeline in Azure Data Factory or Azure Synapse Analytics, either on-demand or by creating a trigger. Regards, Oury

Answer 2

Hey,

You can use Azure Data factory with the below flow:

1. Use a lookup activity with the source database to get list of all tables within the source database schema
2. use foreach activity to iterate over the number of source database tables
3. use copy activity with source database with dynamic dataset and sink as sink database with auto create table option and per script activity as drop table if it already exists.

Bulk copy :

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-bulk-copy-portal

Auto Create:

https://learn.microsoft.com/en-us/answers/questions/773632/data-factory-copy-auto-create-sink-table

the auto create option would automatically create the schema at run time across source and sink