Backup SSAS via SSMS Job with Date Appended

Question

I'd like to backup my SSAS database via a SQL Server Agent job in SSMS – and I’d also like to append the day number to the end of the file and allow overwrites. This will ensure that I only ever have a month of backups (i.e. Jan 1st backup will be called backup_01.abf, Jan 2nd = backup_02.abf etc.)

I connected to the SSAS DB via SSMS and scripted out the backup procedure, which is as follows:

    {
      "backup": {
        "database": "ExampleDB",
        "file": "Backup.abf",
        "allowOverwrite": true,
        "applyCompression": false
      }
    }

I believe I can simply add this as a Step in a server agent job as a SQL Server Analysis Services Command.

But how can I then append the day of the month to the file?

Can’t seem to find much about this online

[EDIT]

Using a combination of what Vaishali responded with below, and another article I've found online about this, I've performed the following:

Created a linked server (Link_SSAS) Generated the following script:

    Declare @XMLA nvarchar(1000),
        @DateSerial nvarchar(35)

    -- Get Day Number from GETDATE()
    Set @DateSerial =   RIGHT('0' + RTRIM(DAY(GETDATE())),2)

    -- Create the XMLA string
    Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Object>
        <DatabaseID>ExampleDB</DatabaseID>
      </Object>
      <File>C:\bak\Backup_' + @DateSerial + '.abf</File>
    </Backup>'

    -- Execute the string across the linked server
    Exec (@XMLA) AT Link_SSAS;

The above xmla runs perfectly if I just execute it through a query window in SSMS However, when I try to put the XMLA into a job-step, i get an error

With Job Type set to T-SQL I get:

The Specified '@server' is invalid (valid values are returned by sp_helpserver)

If I run sp_helpserver, it does show my linked server as being there. Tried disconnected / reconnecting to the server.

Vaishali Vaishali · Accepted Answer · 2017-06-30T10:19:24

You can with 'dynamic xmla script' execute via sql. below link will be useful:

https://www.mssqltips.com/sqlservertip/2790/dynamic-xmla-using-tsql-for-sql-server-analysis-services/

Backup SSAS via SSMS Job with Date Appended

1 Answers