Backup table automation

Question

How to automate in taking backup of a required table in sql datawarehouse by using ADF?

Tuhin Paul · Answer

To automate table backups in Azure SQL Data Warehouse (now Azure Synapse Analytics ) using Azure Data Factory (ADF) we need to follow few things: 1. Backup Strategy Use stored procedures to create timestamped backup tables, then orchestrate them via ADF. 2. Implementation Steps A. Create Backup Stored Procedure CREATE PROCEDURE [dbo].[sp_BackupTable] @SourceTableName NVARCHAR(128), @BackupSuffix NVARCHAR(50) AS BEGIN DECLARE @BackupTableName NVARCHAR(128) = @SourceTableName + '_Backup_' + @BackupSuffix; DECLARE @Sql NVARCHAR(MAX) = ' IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = ''' + @BackupTableName + ''') CREATE TABLE ' + QUOTENAME(@BackupTableName) + ' WITH (DISTRIBUTION = ROUND_ROBIN) AS SELECT * FROM ' + QUOTENAME(@SourceTableName) + ' WHERE 1 = 0; -- Create empty table INSERT INTO ' + QUOTENAME(@BackupTableName) + ' SELECT * FROM ' + QUOTENAME(@SourceTableName) + '; '; EXEC sp_executesql @Sql; END B. ADF Pipeline Setup Linked Services Configure connections to: Azure Synapse Analytics (SQL Data Warehouse) Azure Storage (for logging, optional) Parameters Add pipeline parameters: - tableName: Your source table name - backupSuffix: Timestamp (dynamic) Pipeline Activities Use this sequence: Activity Configuration Execute Stored Proc - Linked Service: Synapse - Stored Procedure: [dbo].[sp_BackupTable] - Parameters: @SourceTableName = @pipeline().parameters.tableName , @BackupSuffix = @pipeline().parameters.backupSuffix Logging (Optional) Use Azure Storage to log backup success/failure with timestamps. C. Dynamic Timestamp Handling Use ADF expressions for dynamic naming: @pipeline().parameters.backupSuffix = formatDateTime(utcnow(), 'yyyyMMdd_HHmmss') D. Automation & Scheduling Trigger Create a scheduled trigger: Daily/weekly backups Time zone aware Retention Policy Add a cleanup step to delete backups older than N days: DELETE FROM [dbo].[TableBackupLog] WHERE BackupDate 3. Key Considerations Component Details Incremental Backups Use WHERE clauses with watermark columns for delta loads. Permissions Grant ADF Managed Identity db_ddladmin and db_datawriter roles. Error Handling Use ADF's Failure path + retry policies. Cost Optimization Schedule backups during off-peak hours. Monitoring Use ADF Monitoring Hub + Azure Monitor alerts. 4. Sample Pipeline JSON { "name": "BackupPipeline", "properties": { "activities": [ { "name": "ExecuteBackupSP", "type": "SqlServerStoredProcedure", "linkedServiceName": { "referenceName": "AzureSynapseLS", "type": "LinkedServiceReference" }, "typeProperties": { "storedProcedureName": "[dbo].[sp_BackupTable]", "storedProcedureParameters": { "SourceTableName": { "value": "@pipeline().parameters.tableName", "type": "String" }, "BackupSuffix": { "value": "@pipeline().parameters.backupSuffix", "type": "String" } } } } ], "parameters": { "tableName": { "type": "String" }, "backupSuffix": { "type": "String", "defaultValue": "@formatDateTime(utcnow(), 'yyyyMMdd_HHmmss')" } } } }

Jayraj Chhaya · Answer

Hi @Tharunkumar Magudeeswaran, To automate the backup of a required table in Azure SQL Data Warehouse using Azure Data Factory (ADF), you can follow these steps: Create a Data Factory : If you haven't already, create an Azure Data Factory instance in the Azure portal. Set Up Linked Services : Configure linked services for both your source SQL Data Warehouse and the destination where you want to store the backup (e.g., Azure Blob Storage). Create a Pipeline : In ADF, create a new pipeline. Use the "Copy Data" activity to copy data from the source table to the destination. Configure the Copy Activity : Source : Select the SQL Data Warehouse linked service and specify the table you want to back up. Sink : Choose the destination linked service (e.g., Blob Storage) and specify the file format (e.g., CSV, Parquet). Schedule the Pipeline : Use the ADF trigger feature to schedule the pipeline to run at your desired frequency (e.g., daily, weekly). Here’s a simple example of a copy activity configuration in JSON: { "name": "CopyTableBackup", "type": "Copy", "inputs": [ { "referenceName": "SourceTable", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "BackupBlob", "type": "DatasetReference" } ], "source": { "type": "SqlSource" }, "sink": { "type": "BlobSink" } } By following these steps, you can effectively automate the backup process of your SQL Data Warehouse table using Azure Data Factory.

Backup table automation

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)