![\"An](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2020\/03\/datafactoryheader.png\")
<\/p>\n<\/p>\n
Most organisations leverage the disaster recovery options available to them on Azure to ensure they are properly protected in case of a data center outage. Depending on their requirements, they can choose among ZRS, GZRS, or GRS & RA-GRS. More details are available here<\/a>.<\/p>\n However, none of these options protect against data deletion. Indeed, if records are accidentally deleted, that deletion is replicated. To protect against such events, customers should consider backing up their data lake. As of today, Azure Data Lake Gen 2 does not have native support for this kind of backup.<\/p>\n Azure Data Factory (ADF) is one of many technical options for a custom backup solution. In this blog post, we\u2019ll walk you through how to leverage ADF pipelines for full and incremental backups of your data lake on Azure. Keeping in mind the size of most enterprise data lakes, we\u2019ll leverage the Metadata-driven<\/strong> copy task in Azure Data Factory (currently in preview), to build large-scale data loading pipelines. More details on the Metadata-driven<\/strong> copy task are available here<\/a>.<\/p>\n Let\u2019s proceed with a scenario that requires an incremental backup every day and a full back up on the last day of every month. We assume you are familiar with ADF. If not, you can find some learning resources here<\/a>.<\/p>\n <\/p>\n <\/p>\n In the ADF Author hub, launch the Copy Data Tool as shown below.<\/p>\n 1. In the properties page, select the Metadata-driven copy task type. You will be prompted to select a linked service to host the control table. The control table will be used to maintain metadata for the copy activity – the source storage account, the source container, the target storage account, etc. \u2013 and can be created in a Azure SQL DB or Synapse Analytics instance. Note that during the preview, you might need to apply minor changes to the script generated to create the table in Synapse. For this walkthrough, we\u2019ll use an Azure SQL Database to host the meta data properties.<\/p>\n 2. Now we\u2019ll provide the connection details of our data source.<\/p>\n 3. In the Destination data store page, specify the properties of the target storage account.<\/p>\n 4. Provide the Task Prefix name on the Settings step. Under Advanced Settings, you can select options like Data Consistency Verification, Fault Tolerance, Preserving the ACLs and many more.<\/p>\n 5. Review and validate your set up in the summary page and make sure it all looks good, then proceed by clicking Next.<\/p>\n 6. In the Deployment step, the following are created:<\/p>\n Copy the SQL script and run against the Azure SQL database (the same database we used as the control table store) using your favourite client tool – Management Studio, Azure Data Studio or even the Query Editor option in the Azure portal. You also have the option to download the script, if needed.<\/p>\n Go back to the authoring hub in ADF to view the generated pipelines and datasets.<\/p>\n As you can see, three pipelines are generated:<\/p>\n This pipeline counts the total number of objects (tables etc.) to be copied in this run, determines the number of sequential batches based on the maximum allowed concurrent copy task instances, and then executes another pipeline to copy different batches sequentially.<\/p>\n This pipeline copies one batch of objects. The objects belonging to this batch will be copied in parallel.<\/p>\n This pipeline copies objects from one group. The objects belonging to this group will be copied in parallel.<\/p>\n <\/p>\n The incremental load for an ADLS data source is not yet supported as part of the Metadata Driven Copy Task. To make this work, we will tweak the ADF pipelines a little and create a stored procedure in the Azure SQL Database.<\/p>\n The stored procedure will update the modifiedDatetimeStart attribute of the CopySourceSettings column with the pipeline trigger time, after each run. That way, the next time you execute the ADF pipeline, it will only copy files created or modified since the last run.<\/p>\n If, for any reason, you want to rerun the pipeline from a specific date, you can manually update the modifiedDatetimeStart in the control table.<\/p>\n Open the xxx_ BottomLevel<\/strong> pipeline. Drill down on the ForEach activities. A Switch activity decides when to do a full load and default (incremental load).<\/p>\n Select the Default Activity as we need to make changes to the incremental load.<\/p>\n Add a new Stored procedure activity after the copy activity. Keep the default link between the activity (OnSuccess).<\/p>\n Select the \u2018Stored Procedure\u2019 activity.<\/p>\n Next, select the \u2018Copy data\u2019 activity. In the source tab, update the start date and the end date of the \u2018Filter by last modified\u2019 property as follows:<\/p>\n In the sink tab, update the folderPath property as follows:<\/p>\n The tweaks for the incremental load are now complete.<\/p>\n Now go back and edit the FullLoad \u2018Copy data\u2019 activity. The purpose of the FullLoad is to ensure the entire storage account and the account in the source is backed up and to specify the target folder hierarchy:<\/p>\n In the source tab, make sure both the start date and the end date of the \u2018Filter by last modified\u2019 are blank.<\/p>\n In the sink tab, update the folderPath property as follows:<\/p>\n We now need to create 2 triggers, one each for the full and incremental backups.<\/p>\n To create a trigger, click on Add trigger > New\/Edit<\/p>\n In the Add triggers panel that opens up, select New in the Choose trigger drop down.<\/p>\n <\/p>\n This will initiate the full backup of the source container(s) on the last day of the month, irrespective of the number of days in each month. Create the trigger as shown below.<\/p>\n <\/p>\n This will copy only the new and edited files from the source since the previous run. The incremental backup in this example will run once a day to copy relevant files from the source container(s) to destination container(s). Create this trigger as shown below.<\/p>\n Remember to set the Activated status to \u2018Yes\u2019 for both Triggers created.<\/p>\n <\/p>\n Using your favourite client tool, connect to the Azure SQL database and run a select statement on the control table. As mentioned above, only one record was created as part of the generated script. That record contains the properties of the incremental load.<\/p>\n When you execute the ADF pipeline (), the lookup activity filters on the trigger name:<\/p>\n Perform the following updates:<\/p>\n After completing these steps, the content of the new row in the metadata table should look similar to:<\/p>\n Finally, in the newly inserted row, look up the column \u201cCopySourceSettings\u201d and change the entries in the column as follows:<\/p>\n After completing the updates, the column should look like below:<\/p>\n This completes the setup for the backups.<\/p>\n <\/p>\n Issagha Ba<\/strong> is a Principal Cloud Solution Architect with the Microsoft Customer Success Unit and drives large scale and deep technical engagements with customers. Issagha has a wealth of experience on the Azure data platform and analytics.<\/p>\n Athulya Ramamoorthy<\/strong> is a Cloud Solution Architect with Microsoft, focusing on Data and Analytics, and enjoys helping customers innovate on Azure. Athulya also has a keen interest in using technology to drive sustainability.<\/p>\n Volkan Civelek<\/strong> has extensive experience on Data, AI and Cloud systems. He’s currently helping high priority global customers at Microsoft. Data architecture for Scalability and High Availability are his passion.<\/p>\n Abiodun Olatunji<\/strong> is a Senior Cloud Solution Architect with the Microsoft Customer Success Unit. He focuses on Big Data & Analytics workloads and drives large scale technical engagements with various global customers. He has deep experience in various Azure Data Services including Azure Synapse, SQL, Purview, Stream Analytics, Data Factory, and Data Lake.<\/p>\n <\/p>\n In this blog post, we\u2019ll walk you through how to leverage ADF pipelines for full and incremental backups of your data lake on Azure.<\/p>\n","protected":false},"author":430,"featured_media":30924,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"categories":[594],"post_tag":[128,636,519],"content-type":[],"coauthors":[1761],"class_list":["post-51555","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technetuk","tag-azure","tag-data","tag-technet-uk"],"yoast_head":"\nPrerequisites<\/h3>\n
\n
Generate the ADF pipelines<\/h3>\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image001.png\")
<\/p>\n\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image002.png\")
<\/p>\n\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image003.png\")
<\/p>\n\n
![\"graphical](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image004.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image026.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image027.png\")
<\/p>\n\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image028.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image029.png\")
<\/p>\n\n
\n
\n
Updates on the generated artifacts<\/h3>\n
Stored procedure<\/h4>\n
CREATE PROCEDURE<\/span> [dbo].[Update_modifiedDatetimeStart]\n@modifiedDatetimeStartDate nvarchar<\/span>(max<\/span>),\n@Id int<\/span>\n AS<\/span>\nUPDATE<\/span> <Your Control table>\n SET<\/span> CopySourceSettings=JSON_MODIFY<\/span>(CopySourceSettings,'$.modifiedDatetimeStart<\/span>', @modifiedDatetimeStartDate) WHERE Id = @Id<\/pre>\nADF pipelines<\/h4>\n
Incremental load<\/h4>\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image030.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image031.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image032.png\")
<\/p>\n\n
\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image033.png\")
<\/p>\n\n
\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image034.png\")
<\/p>\n\n
\n
![\"graphical](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image035.png\")
<\/p>\nFull load<\/h4>\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image036.png\")
<\/p>\n![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image037.png\")
<\/p>\n\n
\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image038.png\")
<\/p>\nCreate and schedule the triggers.<\/h3>\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image039.png\")
<\/p>\nTrigger for the Full Backup<\/h4>\n
![\"A](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image040.png\")
<\/p>\nTrigger for the Incremental Backup<\/h4>\n
![\"graphical](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image041.png\")
<\/p>\nControl table – Incremental load record<\/h4>\n
SELECT<\/span> count<\/span>(*) as<\/span> count<\/span> FROM<\/span> @{pipeline().parameters<\/span>.MainControlTableName}\nwhere <\/span>TopLevelPipelineName='@{pipeline().Pipeline<\/a>}' and TriggerName like '%@{pipeline().TriggerName<\/a>}%'<\/pre>\n\n
Column \u201cTriggerName\u201d:\n[ \"Sandbox\", \"< Name of your Incremental Trigger >\"<\/em> ]\nColumn \u201cDataLoadingBehaviorSettings\u201d:\n\n{ \"dataLoadingBehavior\": \"Default\" }<\/pre>\n![\"The](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image042.png\")
<\/p>\n\n
![\"The](\"https:\/\/www.microsoft.com\/en-us\/industry\/blog\/wp-content\/uploads\/sites\/22\/2021\/08\/image043.png\")
<\/p>\nAbout the authors<\/h3>\n
Learn more<\/h3>\n
\n