Restoring Dataverse Table Data using PowerShell

Restoring Dataverse table data is essential in cases of accidental deletions, data corruption, or migration. PowerShell provides an efficient way to import backed-up data and restore tables in Microsoft Dataverse.

This guide covers:

Connecting to Dataverse using PowerShell
Importing data from a backup (CSV/JSON)
Restoring records into Dataverse
Handling duplicate and existing records
Automating the restoration process

Step 1: Prerequisites

1. Install Required PowerShell Modules

Ensure you have the necessary PowerShell modules installed:

# Install Power Platform module if not installed
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import the module
Import-Module Microsoft.PowerPlatform.Cds.Client

2. Required Permissions

You must have the System Administrator or Dataverse Admin role to restore data.

Step 2: Connecting to Dataverse

Option 1: Interactive Login

For manual execution, use:

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

Option 2: Using Service Principal (For Automation)

For automated execution, use an Azure AD App Registration:

# Define credentials
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Convert secret to secure string
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Now connected to Dataverse!

Step 3: Preparing the Backup File for Restoration

Ensure the backup file (CSV/JSON) is available. Example backup:

Backup File (account-data.csv):

Name	Email	Phone
John Doe	john@example.com	1234567890
Jane Smith	jane@example.com	9876543210

Step 4: Importing Data from CSV File

To import data from a CSV backup, use:

# Define the table name and backup file path
$tableName = "account"
$backupFile = "C:\Backups\account-data.csv"

# Import data from CSV
$importData = Import-Csv -Path $backupFile

# Loop through each record and insert into Dataverse
foreach ($record in $importData) {
    New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields @{
        "name" = $record.Name
        "emailaddress1" = $record.Email
        "telephone1" = $record.Phone
    }
}

Write-Host "Data restoration completed for table: $tableName"

All records from CSV are now restored to Dataverse.

Step 5: Handling Duplicates and Existing Records

If the table contains existing records, we should update instead of duplicating records.

# Define table name
$tableName = "account"
$backupFile = "C:\Backups\account-data.csv"

# Import data
$importData = Import-Csv -Path $backupFile

# Restore records with duplicate handling
foreach ($record in $importData) {
    # Check if the record already exists
    $existingRecord = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName -FilterAttribute "emailaddress1" -FilterOperator "eq" -FilterValue $record.Email

    if ($existingRecord) {
        # Update the existing record
        Set-CdsRecord -Connection $connection -EntityLogicalName $tableName -Id $existingRecord.CdsRecordId -Fields @{
            "name" = $record.Name
            "telephone1" = $record.Phone
        }
        Write-Host "Updated record for: $($record.Name)"
    } else {
        # Create a new record
        New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields @{
            "name" = $record.Name
            "emailaddress1" = $record.Email
            "telephone1" = $record.Phone
        }
        Write-Host "Inserted new record for: $($record.Name)"
    }
}

Write-Host "Data restoration completed with duplicate handling."

Existing records are updated instead of duplicated.

Step 6: Restoring Data from JSON File

If the backup is in JSON format, use this method:

# Define table name and backup file
$tableName = "account"
$backupFile = "C:\Backups\account-data.json"

# Import data from JSON
$importData = Get-Content -Path $backupFile | ConvertFrom-Json

# Insert records into Dataverse
foreach ($record in $importData) {
    New-CdsRecord -Connection $connection -EntityLogicalName $tableName -Fields @{
        "name" = $record.name
        "emailaddress1" = $record.email
        "telephone1" = $record.phone
    }
}

Write-Host "Data restoration completed from JSON backup."

JSON backup restored successfully!

Step 7: Automating the Restoration Process

To automate the restoration, save the script as RestoreDataverse.ps1 and schedule it using Task Scheduler:

# Schedule the restoration script
$taskAction = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-ExecutionPolicy Bypass -File C:\Scripts\RestoreDataverse.ps1"
$taskTrigger = New-ScheduledTaskTrigger -Daily -At 2AM
Register-ScheduledTask -TaskName "Dataverse Restore" -Action $taskAction -Trigger $taskTrigger -User "NT AUTHORITY\SYSTEM" -RunLevel Highest -Force

The script now runs automatically every day at 2 AM.

Step 8: Verifying Restored Data

After restoration, verify the data using PowerShell:

# Check restored records
$tableName = "account"
$restoredRecords = Get-CdsRecord -Connection $connection -EntityLogicalName $tableName

# Display record count
Write-Host "Total records in table '$tableName': $($restoredRecords.Count)"

Ensures that all records were successfully restored.