[Idea] Add Support for Dataset Backup & Restore

[samaguire]

I'm guessing this is a little dependant on MS APIs but it would be awesome if I could execute a backup/restore from this tool as well.

[gbrueckl]

great idea! will check if thats doable with APIs or XMLA/TOM and keep you updated!

[samaguire]

Ah yes, using the XMLA endpoint! I have TE3 macros for this. I did originally execute against the XMLA endpoint in TE3, but moved across to using the AnalysisServices.Core library. I think I was originally manipulating the below XMLA elements (IIRC I could only use the sub-elements <File>, <ApplyCompression>, <AllowOverwrite>, and <Password> against PBI Service)

https://learn.microsoft.com/en-us/analysis-services/xmla/xml-elements-commands/backup-element-xmla?view=asallproducts-allversions

https://learn.microsoft.com/en-us/analysis-services/xmla/xml-elements-commands/restore-element-xmla?view=asallproducts-allversions

But this is the code I currently use in TE3 (if it's useful for you)

Backup

#r "Microsoft.AnalysisServices.Core"

using Microsoft.AnalysisServices.Core;

Model.Database.TOMDatabase.Backup(
    file: Model.Database.Name + ".abf",
    allowOverwrite: true,
    backupRemotePartitions: default,
    locations: default,
    applyCompression: true,
    password: default
);

Info("Script finished.");

Restore

#r "Microsoft.VisualBasic"
#r "Microsoft.AnalysisServices.Core"

using Microsoft.VisualBasic;
using Microsoft.AnalysisServices.Core;

var dbName = Interaction.InputBox(
    Prompt: "Provide the name of the restored database. (This will overwrite the database if it already exists.)",
    Title: "Set database name:",
    DefaultResponse: Model.Database.Name
);

if(dbName == "") { return; }

Model.Database.TOMDatabase.Server.Restore(
    file: Model.Database.Name + ".abf",
    databaseName: dbName,
    allowOverwrite: true
);

Info("Script finished.");

[gbrueckl]

ok, then its definitely doable also from VSCode using our TMDL Proxy (which is actually a TOM/XMLA proxy :D ) will put it on the list!

[gbrueckl]

do you need all the options for backup/restore?

[gbrueckl]

can you please give it a try: https://github.com/gbrueckl/PowerBI-VSCode/releases/tag/v1.1.0

its not yet released to the VSCode Gallery so you need to install the .vsix manually

[samaguire]

Okay. So I tried that, and it didn't work. It pops up and asks for the file name and to allow overrides, and then nothing.

I ran SQL profiler, and can see the app connecting to the dataset and pulling details, but nothing logs when I initiate the backup.

I traced TE3 when i run a backup and get this for the command begin:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <Object>
            <DatabaseID>c7160fa1-f385-48ee-ae0e-06471c43cea9</DatabaseID>
          </Object>
          <File>International Trading - Gross Profit.abf</File>
          <AllowOverwrite>true</AllowOverwrite>
        </Backup>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
          <SspropInitAppName>TabularEditor-99b3f0c0-67cc-4e7b-bdd2-f1bb6fdd4f85</SspropInitAppName>
          <Catalog>International Trading - Gross Profit</Catalog>
          <LocaleIdentifier>5129</LocaleIdentifier>
          <ClientProcessID>14504</ClientProcessID>
          <DbpropMsmdActivityID>905fcf12-5a17-4376-85ea-556b51ba19d3</DbpropMsmdActivityID>
          <DbpropMsmdRequestID>905fcf12-5a17-4376-85ea-556b51ba19d3</DbpropMsmdRequestID>
          <DbpropMsmdCurrentActivityID>905fcf12-5a17-4376-85ea-556b51ba19d3</DbpropMsmdCurrentActivityID>
        </PropertyList>

interestingly, i don't get the commands logged for the restore, but tracing against desktop (which errors) i get this for the command begin

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <File>a206b513-a75f-4998-89d7-94ea534a9501.abf</File>
          <DatabaseName>dbName</DatabaseName>
          <AllowOverwrite>true</AllowOverwrite>
        </Restore>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
          <SspropInitAppName>TabularEditor-8f1a6d78-c993-4d1a-9de6-1422d1f6f49c</SspropInitAppName>
          <Catalog>a206b513-a75f-4998-89d7-94ea534a9501</Catalog>
          <LocaleIdentifier>5129</LocaleIdentifier>
          <ClientProcessID>15128</ClientProcessID>
          <DbpropMsmdActivityID>ce025d49-9b70-4529-a574-c45faab49f26</DbpropMsmdActivityID>
          <DbpropMsmdRequestID>c3a25502-5c20-485f-91b7-376d247bc649</DbpropMsmdRequestID>
          <DbpropMsmdCurrentActivityID>ce025d49-9b70-4529-a574-c45faab49f26</DbpropMsmdCurrentActivityID>
        </PropertyList>

[gbrueckl]

did you check if the .abf file was created in your connected ADLS Gen2 account? I tested both, backup and restore successfully - you can even restore a backup under a different name if you use "Restore" from the context menu of an workspace item

what do you mean by "tracing against desktop"?

[samaguire]

Yeah I checked the connected storage and nothing appeared. I can try again tomorrow, but I was able to see other trace items, ie the calls to get parameters etc and running it from TE3 worked.

Regards Stephen

Get Outlook for Androidhttps://aka.ms/ghei36

---

From: Gerhard Brueckl @.> Sent: Thursday, November 9, 2023 9:06:38 PM To: gbrueckl/PowerBI-VSCode @.> Cc: Stephen Maguire @.>; Author @.> Subject: Re: [gbrueckl/PowerBI-VSCode] [Idea] Add Support for Dataset Backup & Restore (Issue #19)

did you check whether the .abf file was created in your connected ADLS Gen2 account? I tested both, backup and restore successfully - you can even restore a backup under a different name if you use "Restore" from the context menu of an workspace item

— Reply to this email directly, view it on GitHubhttps://github.com/gbrueckl/PowerBI-VSCode/issues/19#issuecomment-1803335994, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AO3PBAW323NBQQTIJYOMOF3YDSFI5AVCNFSM6AAAAAA6Z24OP6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMBTGMZTKOJZGQ. You are receiving this because you authored the thread.Message ID: @.***>

[gbrueckl]

I just ran a very basic test with a small model - could be that there are some timeouts with larger models 🤔

[samaguire]

I tried this again and I'm still getting the same result. I used a really simple dataset, i.e. just load some manually input data and is super small. Works in TE3, but not with the extension. It's almost as if it never sends the command to the server.

Could it be something with the credentials? I'm using default settings though. 🤔

[gbrueckl]

ok, I found the issue - you need to make sure the TMDL Proxy is running upfront (at least in v1.1.0) this will be done automatically with the next release

but for now, please either run PowerBI.TMDL.ensureProxy manually from the command palette

or use any other TMDL feature upfront (e.g. Edit TMDL on any dataaset)

[samaguire]

Works perfectly after the latest update! 🧨🎉✨

[gbrueckl]

closing as fixed