How to generate or set unique name (using current Date) for daily backup SSAS Cubes

Question

I have an ETL with an Analysis Services Execute DDL object running a daily backup of my cube the XML code below:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><Object><DatabaseID>MyCube</DatabaseID></Object><File>B:\Backups\Cubes\MyCube.abf</File><AllowOverwrite>true</AllowOverwrite></Backup>

So I'm getting every day a file like:

MyCube.abf

And using SSIS with Sql task object or Sql server agent, I would like to have an unique file for every day with the current date. Something Like:

MyCube_2018_05_09.abf
MyCube_2018_05_10.abf
MyCube_2018_05_11.abf

Also If can set something to delete the oldest backup leaving the recent 2 weeks that would be really helpful.

Note: I had referenced the website below, but I tried several time in different ways and nothing work.

http://dbatasks.blogspot.com/2012/08/taking-backup-of-ssas-database.html

Any assistance in this regard will be greatly appreciated :D

IN SSIS are you using a variable for the file name? If so you can use the Expressions to build out the date — Brad
In Expression Builder: "FileName_"+ (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".csv" — Brad
I posted answer for building your file name, deleting the backups would be a separate question/separate process. and thanks @billinkc — Brad

Brad Brad · Accepted Answer · 2018-05-09T16:12:45

Moved to answer:

In Expression Builder in SSIS for variable you can copy/paste this and tweak it a bit for your needs. This puts year as 4 digits, and months and days as 2 with leading 0's if its a single number day or month:

"MyCube_"+ (DT_WSTR,4)YEAR(GETDATE()) +  RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".abf"

How to generate or set unique name (using current Date) for daily backup SSAS Cubes

1 Answers