Backup table automation

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

1. Linked Services

   • Configure connections to:

     • Azure Synapse Analytics (SQL Data Warehouse)

     • Azure Storage (for logging, optional)

2. Parameters
   Add pipeline parameters:

   - tableName: Your source table name
   - backupSuffix: Timestamp (dynamic)

3. 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

1. Trigger
   Create a scheduled trigger:

   • Daily/weekly backups

   • Time zone aware

2. Retention Policy
   Add a cleanup step to delete backups older than N days: 

   DELETE FROM [dbo].[TableBackupLog]
   WHERE BackupDate < DATEADD(day, -30, GETDATE())

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')"
      }
    }
  }
}

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:

1. Create a Data Factory: If you haven't already, create an Azure Data Factory instance in the Azure portal.

2. 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).

3. Create a Pipeline: In ADF, create a new pipeline. Use the "Copy Data" activity to copy data from the source table to the destination.

4. 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).

5. 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.