Using PowerShell to back up a Dataverse table

To back up a Dataverse table and save it as a CSV file in SharePoint using PowerShell, follow the steps below. This tutorial assumes you have the necessary permissions and have correctly set up the application credentials in Azure AD so it can access both Dataverse and SharePoint.

Set up the variables

First, define the variables that hold your application’s credentials and the identifiers needed for authentication.

# Set variables
$TenantId = 'xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' #Directory (tenant) ID
$AppId = 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' #Application (client) ID
$ClientSecret = Get-AutomationVariable -Name [Client Secret Variable Placeholder]  #ClientSecretValue - Encrypted
$PowerPlatformOrg = '[OrgID Placeholder]'   #OrgID
$PowerPlatformEnvironmentUrl = "https://$($PowerPlatformOrg).crm.dynamics.com"
$oAuthTokenEndpoint = "https://login.microsoftonline.com/$($TenantId)/oauth2/v2.0/token"

Request the Access Token

To work with the Dataverse API, you need an access token. Use the following block of code to get it.

$authBody = @{
    client_id = $AppId;
    client_secret = $ClientSecret;
    scope = "$($PowerPlatformEnvironmentUrl)/.default"
    grant_type = 'client_credentials'
}

$authParams = @{
    URI = $oAuthTokenEndpoint
    Method = 'POST'
    ContentType = 'application/x-www-form-urlencoded'
    Body = $authBody
}

$authResponseObject = Invoke-RestMethod @authParams -ErrorAction Stop

Extract data from a Dataverse table

Once you have the access token, you can send a GET request to the Dataverse API to extract the data from the table you want.

$getDataRequestUri = 'zero_db';

$getApiCallParams = @{
    URI = "$($PowerPlatformEnvironmentUrl)/api/data/v9.1/$($getDataRequestUri)"
    Headers = @{
        "Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
        "Accept" = "application/json"
        "OData-MaxVersion" = "4.0"
        "OData-Version" = "4.0"
    }
    Method = 'GET'
}

$getApiResponseObject = Invoke-RestMethod @getApiCallParams -ErrorAction Stop

$getApiResponseObject.value | export-csv testing.csv

Store the CSV file in SharePoint

Finally, connect to SharePoint and upload the newly generated CSV file. The script will also find and delete files older than 30 days in the specified library.

$tenant='zerogap.onmicrosoft.com'
$SiteUrl='https://zerogap.sharepoint.com/sites/zerosite'
$LibraryName="Shared Documents/Dataverse Backup"
$NewFileName=Get-Date -Format "dddd_MM_dd_yyyy_HH_mm"
$NewFileName=$NewFileName.toString() + ".csv"
$null=Connect-PnPOnline -Url $SiteUrl -ManagedIdentity

Add-PnPFile -Path 'testing.csv' -Folder $LibraryName -NewFileName $NewFileName | Out-Null

$Files = Get-PnPFolderItem -FolderSiteRelativeUrl $LibraryName

$today_date=Get-Date

ForEach($File in $Files)
{
    if (($today_date-$File.TimeLastModified).Days -ge 30) {
        $Listid=Get-PnPListItem -List $LibraryName  -UniqueId $File.UniqueId
        Move-PnPListItemToRecycleBin -List $LibraryName -Identity  $Listid.id  -Force
    }
}

Complete Code

Here is the complete code for the process described above:

# Set variables
$TenantId = 'xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' #Directory (tenant) ID
$AppId = 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' #Application (client) ID
$ClientSecret = Get-AutomationVariable -Name [Client Secret Variable Placeholder]  #ClientSecretValue - Encrypted
$PowerPlatformOrg = '[OrgID Placeholder]'   #OrgID
$PowerPlatformEnvironmentUrl = "https://$($PowerPlatformOrg).crm.dynamics.com"
$oAuthTokenEndpoint = "https://login.microsoftonline.com/$($TenantId)/oauth2/v2.0/token"

$authBody = @{
    client_id = $AppId;
    client_secret = $ClientSecret;
    scope = "$($PowerPlatformEnvironmentUrl)/.default"
    grant_type = 'client_credentials'
}

$authParams = @{
    URI = $oAuthTokenEndpoint
    Method = 'POST'
    ContentType = 'application/x-www-form-urlencoded'
    Body = $authBody
}

$authResponseObject = Invoke-RestMethod @authParams -ErrorAction Stop

$getDataRequestUri = 'zero_db';

$getApiCallParams = @{
    URI = "$($PowerPlatformEnvironmentUrl)/api/data/v9.1/$($getDataRequestUri)"
    Headers = @{
        "Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
        "Accept" = "application/json"
        "OData-MaxVersion" = "4.0"
        "OData-Version" = "4.0"
    }
    Method = 'GET'
}

$getApiResponseObject = Invoke-RestMethod @getApiCallParams -ErrorAction Stop

$getApiResponseObject.value | export-csv testing.csv

$tenant='zerogap.onmicrosoft.com'
$SiteUrl='https://zerogap.sharepoint.com/sites/zerosite'
$LibraryName="Shared Documents/Dataverse Backup"
$NewFileName=Get-Date -Format "dddd_MM_dd_yyyy_HH_mm"
$NewFileName=$NewFileName.toString() + ".csv"
$null=Connect-PnPOnline -Url $SiteUrl -ManagedIdentity

Add-PnPFile -Path 'testing.csv' -Folder $LibraryName -NewFileName $NewFileName | Out-Null

$Files = Get-PnPFolderItem -FolderSiteRelativeUrl $LibraryName

$today_date=Get-Date

ForEach($File in $Files)
{
    if (($today_date-$File.TimeLastModified).Days -ge 30) {
        $Listid=Get-PnPListItem -List $LibraryName  -UniqueId $File.UniqueId
        Move-PnPListItemToRecycleBin -List $LibraryName -Identity  $Listid.id  -Force
    }
}