Automating SSAS script only backups with C# for Tabular and Multi-Dimension Models

I was recently asked to create a SQL job to automate the backing up of our SSAS cubes from several servers but unlike the standard SSAS backups, the business didn’t want the data backed up, they only wanted the schema/code as they needed daily backups. The full backup with data would have used up too much space.

Initially, I thought it’d be a piece of cake, just had to Google it, right (while I’ve backup SSAS many times before, they were all with data). As it turns out, a bit more complicated than I’d expected as there’s little documented with regards to this.

We were using SQL2016/17 Tabular and what we needed is the output below (shown here the manual way) but it had to be automated.

ScriptOutSSAS

The output for Tabular is JSON, and that’s where most of the confusion seems to be as most of the code on StackOverflow and other forums are for Multi-Dimension (as at the time of this blog). What’s more, the version of DLL used is not explicitly stated or even mentioned. The result is either your code won’t compile or as others have discovered, it’ll spit out a subset of the output and in the wrong format.

JSON

Multi Dimension

I won’t go into all the nuances but this is what you should use. For DLLs, these are:

  • Microsoft.AnalysisServices.Core.DLL
  • Microsoft.AnalysisServices.DLL

Depending on your installation, these can be found roughly around here: C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies    (120,130,140 for different versions of SQL) or on Nuget.

For the code, it should resemble something like this-note the use of XmlWriterSettings for the AnalysisServices.Scripter.

MD2

The output is XML and should look something like this below. Just comment out the first line before running in SSMS.

While I haven’t tested it, other have pointed out the same code above (with XML) also works for Tabular models before SQL2016.

xml

Tabular (SQL2016 and beyond)

DLLs needed for Tabular are these:

  • Microsoft.AnalysisServices.Core.dll
  • Microsoft.AnalysisServices.Tabular.dll

Depending on your installation, these can be found either in a path similar to the one for Multi-Dimension (C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies), Nuget or as like mines, in the GAC (C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.Tabular)

The code should resemble something like this-note the use of the JSON scripter this time (Microsoft.AnalysisServices.Tabular.JsonScripter).

TabularCode

Unlike Multi-Dimension, output for Tabular is JSON as seen below.

SSAS Create Script JSON Tabular

IF you want to script out all the database on the server, just use the foreach loop

foreach (var item in svr.Databases)   {
}

Once your code is ready and tested, just import that into SSIS or something similar like CLR Proc and have SQL Agent call it on schedule.

Sam


Leave a Reply