Automating the backup of Power BI Premium datasets/databases – Part 1
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!
[…] Gilbert Quevauvilliers shares the first part of a two-part series: […]
[…] This follows on from my first blog post: Automating the backup of Power BI Premium datasets/databases – Part 1 […]
I have tried to use the same process to automating the backup of Powerbi datasets.
But its throwing an error. – Failed to resolve PBI workspace.
I have setup everything properly but not sure what’s going wrong here.
Could you please help asap? Would be really appreciated.
Many Thanks in advance
Hi Khushi,
Can you make sure that the account you are using to connect has got the right access to the App Workspace?
Yes, the service principal I have created is added in the workspace.
But Do I need to add request API permissions via “Add a permission“ in service principal?
If yes then what permission should I add in service principal- Power Bi Service- (Read.All) ?
Not working at all, I have added all the required permissions to my service principal using API permission and also my PBI workspace has access to the service principal.
But still giving error – Failed to resolve PBI workspace.
Please suggest, why this error is coming and how can I resolve this.
Thanks in advance
Hi Khushi,
As far as I know it should need the Read.All across the entire tenant.
Also make sure that you have enabled the settings in the Power BI Tenant Admin to allow Service Principals to access the data
https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-service-principal
Thanks for your reply.
Can we use Read.All across the entire tenant as a Delegated one in API permission or it should be added in Application only?
I would do it as a Delegated one in the API permissions!
I have added API permissions as well. Still giving same error – ” Failed to connect workspace “.
Not sure what step, I am missing or what access is still pending. I provided all the necessary access to service principal and as well as also enabled the setting in the Power Bi tenant admin to allow service principal to access the data. Still not working. Please suggest what’s going wrong at my side or in case if I have missed something.
Now, I am getting an error – ” The remote server returned an error: (400) Bad Request.”
I have assigned the correct URL. Could you please help me on this?
Thanks
Hi Kushi,
Can you make sure that you have got the right permissions and also got the valid URL (I would suggest copying it from the Settings page in the Power BI App Workspace)
Hi Gilbert,
Yes I am adding the url from setting page in the Power BI App workspace.
and these are the permission, I have added to the service principal.
Hi Gilbert,
Yes I am adding the url from setting page in the Power BI App workspace.
and these are the permission, I have added to the service principal.
Workspace.ReadWrite.All
Tenant.Read.All
Dataset.Read.All
Dataset.ReadWrite.All
StorageAccount.Read.All
StorageAccount.ReadWrite.All
Hi Gilbert,
It seems to me, I would also need to add Tenant.ReadWrite.All across the entire tenant as a Delegated one in API permission.
Right? Can you confirm?
Thanks
I have added Tenant.ReadAll , Tenant.ReadWrite.All across the entire tenant as a Delegated one in API permission but still I am getting this error.
Could you please help, whats going wrong here?
Error – The remote server returned an error: (400) Bad Request.
Technical Details:
RootActivityId:
Seems, Delegated one in API permission with this script will not work. It needs Application permissions. After reading this – https://learn.microsoft.com/en-us/graph/auth/auth-concepts
Hi Kushi,
Can you confirm that the account you are using to do the Power BI backup, also has got admin rights in the Power BI Premium per User or Premium App workspace?
Yes, I have assigned the admin rights to my workspace. When I am doing same with SQL server management studio application , then I am able to do it with this.
But it seems to me, the service principal require API permission Tenant.ReadWrite.All across the entire tenant as a Application one in API permission, not delegated one. Correct?
Please confirm.
Thanks
Hi Kushi,
Can you please make sure in your tenant settings in the Power BI Service that you have got the following settings enabled.
– Allow service principals to use Power BI APIs
– Allow service principals to use read-only admin APIs
– Allow service principals to create and use profiles
Hi Khushi,
Can you confirm the tenant settings have been applied?
Hi Gilbert,
Those are already applied. All featured that you have mentioned in enabled in tenant setting.
But can you confirm what permission is really required on my service principal because I have added Tenant.ReadWrite.All across the entire tenant as a Delegated one in API permission. Will it work. Still giving same error.
Hi Khushi
I Had a look and these are the permissions I have
Dashboard.Read.All
Group.Read
Workspace.Read.All
Capacity.Read.All
Dataset.ReadWrite.All
Dashboard.ReadWrite.All
Report.ReadWrite.All
Workspace.ReadWrite.All
Capacity.ReadWrite.All
Content.Create
I need to add these permission as an delegated one. Right?
Yes that is correct!
But I don’t see ” Group.Read” in delegated one.
Is this something else?
Still have error:
Invoke-ASCmd : Failed to resolve PBI workspace. Description:
Technical Details:
RootActivityId: e0678adc-8556-0003-5e27-6ce05685d901.
At line:41 char:9
+ Invoke-ASCmd -ServicePrincipal -Credential $Creds -Tenant $Te …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidResult: (:) [Invoke-ASCmd], ConnectionException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.AnalysisServices.PowerShell.Cmdlets.ExecuteScriptCommand
Can you confirm you have installed the module “SqlServer”?
Yes, I have installed SQL server. But still have same error.
Not able to figure it out what I have missed out here. As I have already completed all necessary steps.
This is the error when I am testing it on my local machine:
PS C:\WINDOWS\system32> Resolve-PowerBIError -Last
Message : Failed to resolve PBI workspace. Description:
Technical Details:
RootActivityId: e0678adc-8556-0003-4f98-6ce05685d901.
StackTrace : at Microsoft.AnalysisServices.Authentication.PbiPremiumAuthenticationHandle.TryResolveWorkspaceWithWorkspaceResolver(String pbiApiBaseUri, String workspaceName, AuthenticationHandle
handle, String requestId, Workspace201606& workspace, ResolvePbiWorkspaceErrorReason& errorReason, String& technicalDetails)
at Microsoft.AnalysisServices.Authentication.PbiPremiumAuthenticationHandle.TryResolvePbiWorkspace(String pbiApiBaseUri, String workspaceName, AuthenticationHandle handle, String
requestId, String& workspaceObjectId, String& pbiDedicatedRolloutFqdn, String& capacityObjectId, ResolvePbiWorkspaceErrorReason& errorReason, String& workspaceType,
WorkspaceCapacitySkuType201606& skuType, String& technicalDetails)
at Microsoft.AnalysisServices.ConnectionInfo.ResolveHTTPConnectionPropertiesForPaaSInfrastructure(IConnectivityOwner owner, Uri& dataSourceUri, Boolean acquireAADToken, Boolean
returnCloudConnectionAuthenticationProperties, String& paasCoreServerName, CloudConnectionAuthenticationProperties& cloudConnectionAuthenticationProperties)
at Microsoft.AnalysisServices.XmlaClient.OpenHttpConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.XmlaClient.OpenConnectionAndCheckIfSessionTokenNeeded(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Core.Server.Connect(String connectionString, String sessionId, ObjectExpansion expansionType)
at Microsoft.AnalysisServices.Core.Server.Connect(String connectionString)
at Microsoft.AnalysisServices.PowerShell.Cmdlets.ExecuteScriptCommand.ExecuteQuery()
at Microsoft.AnalysisServices.PowerShell.Cmdlets.ExecuteScriptCommand.ProcessRecord()
Exception : Microsoft.AnalysisServices.ConnectionException
InvocationInfo : {Invoke-ASCmd}
Line : Invoke-ASCmd -ServicePrincipal -TenantId $TenantId -Credential $Credential -Server $PowerBIPremiumWorkspaceConnection -Database $PowerBIDatasetName -Query $requestBody
Position : At line:39 char:9
+ Invoke-ASCmd -ServicePrincipal -TenantId $TenantId -Credentia …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId : 63
Message : The remote server returned an error: (401) Unauthorized.
StackTrace : at System.Net.HttpWebRequest.GetResponse()
at Microsoft.AnalysisServices.Authentication.PbiPremiumAuthenticationHandle.TryResolveWorkspaceWithWorkspaceResolver(String pbiApiBaseUri, String workspaceName, AuthenticationHandle
handle, String requestId, Workspace201606& workspace, ResolvePbiWorkspaceErrorReason& errorReason, String& technicalDetails)
Exception : System.Net.WebException
InvocationInfo : {Invoke-ASCmd}
Line : Invoke-ASCmd -ServicePrincipal -TenantId $TenantId -Credential $Credential -Server $PowerBIPremiumWorkspaceConnection -Database $PowerBIDatasetName -Query $requestBody
Position : At line:39 char:9
+ Invoke-ASCmd -ServicePrincipal -TenantId $TenantId -Credentia …
Error –
Connect-AzAccount : ClientSecretCredential authentication failed: Method not found: ‘Microsoft.Identity.Client.ConfidentialClientApplicationBuilder
Microsoft.Identity.Client.ConfidentialClientApplicationBuilder.WithAzureRegion(System.String)’.
At line:24 char:1
+ Connect-AzAccount -ServicePrincipal -TenantId $TenantId -Credential $ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Connect-AzAccount], AuthenticationFailedException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Profile.ConnectAzureRmAccountCommand
Invoke-ASCmd : Failed to resolve PBI workspace. Description:
Hi there my blog post does not use the Connect-AzAccount, if you can please follow the steps.
Also it appears that the PBI workspace you are using has a spelling error it appears.
Hi Gilbert,
I see in your blog that, you have used Connect-AzAccount.
This is your script that you have used in this blog: Please have a look 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
Hi Gilbert,
I see in your blog that, you have used Connect-AzAccount.
This is your script that you have used in this blog:
#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
Ahh apologies I do not think you need that line, you can comment it out.
Yeah I did so previously but I was getting this error:
Invoke-ASCmd : Unable to obtain authentication token using the credentials provided. If your Active Directory tenant administrator has configured Multi-Factor Authentication or if your account is a
Microsoft Account, please remove the user name and password from the connection string, and then retry. You should then be prompted to enter your credentials.
At line:39 char:9
+ Invoke-ASCmd -TenantId $TenantId -Credential $Credential -Ser …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidResult: (:) [Invoke-ASCmd], NonInteractiveLoginException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.AnalysisServices.PowerShell.Cmdlets.ExecuteScriptCommand
and i am using this script now, just by putting my tenant id, app id and secret value in below script.
#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 = “$My TenantID”
$user = “$App ID”
$password = ConvertTo-SecureString -String “$Secret Value” -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 -TenantId $TenantId -Credential $Credential -Server $PowerBIPremiumWorkspaceConnection -Database $PowerBIDatasetName -Query $requestBody
The only thing is, I am not able to do this with Service Principal.
Hi there,
Yeah in the Powershell script it does specify a service credential, which it is expecting to pass through and that is why it is failing.
How do I resolve this. I want to automate this using Service Principal.
Can you please help me out?
Thanks
Hi there
you can follow the steps in this blog post https://powerbi.tips/2021/10/using-the-power-bi-scanner-api-to-manage-tenants-entire-metadata/
And follow the section: Pre-Requisites Before Use
Ok, Thanks, as I can see in document in the prerequisites part: It is clearly mentioned that, To complete these tasks, we require Application.ReadWrite.All permission in our service principal. Delegated one will not work.
Hi!
Thanks for an easy to follow instruction.
I have done all steps and the Runbook executes and completes with no errors but the backup file is not showing in the azure storage container connected to my premium Power BI worksspace. Do you have any idé what can by going wrong?
Hi Fredrik,
Can you confirm that you can see the container “power-bi-backup” in your storage account?
You need to see this container and the folder (which should be your App Workspace Name)
Hi!
Containers and folders are in place. I got no error but under output I can see it seems like I got some permission problem. I’m using serviceprinciple and secret. This is the message under output:
“Environments
————
{[AzureChinaCloud, AzureChinaCloud], [AzureCloud, AzureCloud], [AzureGermanCloud, AzureGermanCloud], [AzureUSGovernme…
“
Hi Fredrik,
Could you add the Service Principal account as an Admin in the App workspace where you are trying to do the back up from?