Usare PowerShell per fare il backup di una tabella Dataverse

Per fare una copia di backup di una tabella Dataverse e salvarla come file CSV in SharePoint usando PowerShell, segui i passaggi seguenti. Questo tutorial presuppone che tu abbia i permessi necessari e che abbia configurato correttamente le credenziali dell’applicazione in Azure AD, in modo che abbia accesso sia a Dataverse sia a SharePoint.

Configurare le variabili

Per prima cosa, definisci le variabili che contengono le credenziali della tua applicazione e gli identificatori necessari per l’autenticazione.

# 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"

Richiedere il token di accesso

Per interagire con l’API di Dataverse ti serve un token di accesso. Usa il blocco di codice seguente per ottenerlo.

$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

Estrarre i dati da una tabella Dataverse

Una volta ottenuto il token di accesso, puoi inviare una richiesta GET all’API di Dataverse per estrarre i dati della tabella desiderata.

$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

Salvare il file CSV in SharePoint

Infine, collegati a SharePoint e carica il file CSV appena generato. Lo script cercherà ed eliminerà anche i file più vecchi di 30 giorni nella libreria indicata.

$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
    }
}

Codice completo

Ecco il codice completo del processo descritto sopra:

# 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
    }
}