Use the script from Azure Runbook

Can use this as reference: Steps on how I backed up my Power BI Premium Per User / Premium Database – FourMoo | Power BI | Data Analytics

The first part in this 2-part series I am going to explain how configure the Azure Runbook so that you can then re-use it for multiple different Power BI datasets.

I am confident that most people have more than one dataset that needs to be backed up.

Before starting, please make sure that you have connected your Power Per User or Premium App Workspace to Azure Storage

Configuring dataflow storage to use Azure Data Lake Gen 2 – Power BI | Microsoft Learn

You will first need to create an Azure Automate account. I followed the steps in the link above and it worked well.

Create a standalone Azure Automation account | Microsoft Learn

Installing the required PowerShell Module

I then clicked on Modules, which is under the section called “Shared Resources”

I then clicked on Add a Module

Next, I then clicked on Browse from gallery and clicked on “Click here to browse from gallery”

Then I searched for the module called “SqlServer” as shown below.

I then clicked on SqlServer which took me to the Module page

I then clicked on Select at the bottom of the page.

This then brought me back to the Add a Module page where I could see the SqlServer module I wanted to install, as well as selecting the Runtime version.

I then clicked Import.

NOTE: The PowerShell Module can take a bit of time to install so please be patient.

I could then see the module being installed.

I did click on the Refresh after a few minutes to make sure it was successfully installed.

Creating the Runbook

Now that I had the required PowerShell module installed, next was for me to create the Runbook which can then be used to backup the Power BI Premium datasets.

I then clicked on Runbooks under the Process Automation section.

I then clicked on Create a Runbook

I then created my runbook with the following below:

Name: This is a meaningful name for my runbook,

Runbook Type: Because we are going to be using PowerShell script I selected PowerShell

Runtime version: Here I selected 7.1 because this was the PowerShell module that I installed (Please make sure that both runtime versions match)

Description: I gave it a meaningful description if others come and view this runbook.

I then clicked on Create.

Creating and testing the backup script

The next steps are to then create and test the backup script.

I then clicked on Edit to Edit the Runbook

This then opened the Edit PowerShell Runbook, and I pasted in the following script below.

NOTE: That I am using my Power BI Service Credential to back up the Premium databases.

You will have to get the AppId and Secret key to put in the user and password section below.

#PowerShell Script to backup Power BI Premium Datasets

## Parameters

Param
   (
       [Parameter(Mandatory = $true)]
       [String]$PowerBIPremiumWorkspaceConnection,
       
       [Parameter(Mandatory = $true)]
       [String]$PowerBIDatasetName
       
   )
#Replace Spaces with underscore so that it can be saved to Azure Blob Storage
$PowerBIDatasetName_Updated = $PowerBIDatasetName.replace(' ','_')

# Tenant ID
$TenantID = "d70b4d55-a592-45f2-99f3-299ae5ee7ade"

$user = "92017de1-733b-4b29-9661-e09b7366a109"
$password = ConvertTo-SecureString -String "QcVTpg6Fo.EZ[1IndNeJB8@m-kBSR5dS" -AsPlainText -Force

$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $password
Connect-AzAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential

#Actual Backup script for the database 
$requestBody = @"
         {
 "backup": {
   "database": "$PowerBIDatasetName_Updated",
   "file": "$PowerBIDatasetName_Updated.abf",
   "allowOverwrite": true,
   "applyCompression": true
 }
}
"@

#PowerShell command to process the Partitions              
        Invoke-ASCmd -ServicePrincipal -TenantId $TenantId -Credential $Credential -Server $PowerBIPremiumWorkspaceConnection -Database $PowerBIDatasetName -Query $requestBody

I then clicked on Save.

I got the successful confirmation in the Azure Portal.

Now before I click on Test pane I needed to go to my Power BI Premium App workspace and get the following details:

  • Workspace/Analysis Services Server Connection
  • Database/Dataset Name

In my working example it was the following below.

  • Workspace/Analysis Services Server Connection: powerbi://api.powerbi.com/v1.0/myorg/PPU%20Spaces%20Testing
  • Database/Dataset Name: A-HC

Now that I had my details I then went and clicked on Test pane.

Based on my PowerShell script I have to put in the

I then put in the Workspace Connection and Power BI Dataset Name

Once that has run successfully in the test it will be shown as below.

The last step for the Runbook is to then publish the runbook which will then enable for it to be used.

I then had a look at the storage account, where I could see the ABF file to confirm 100% that it was successfully backed up.

Summary

Thanks for reading this blog post, I hope that you now have the details on how to setup and configure the backup of a Power BI Premium or Premium Per user dataset.

In the next blog post I will detail how to automate this process as well as store the data in Azure Blob Storage where you can save costs.

Any questions or comments are most welcome!