SSAS database backup

Question

I tried to backup SSAS databases (2008 R2) by using query in SSMS, but it is overwriting previous day's backup. How to take backup without overwriting previous backup. I need to maintain at least 7 days of backup. Currently I schedule a job in SQL Server Agent which overwrites daily backup.

This is the job step definition

<Backup xmlns="schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>OLAP_Test</DatabaseID> </Object> <File>K:\OLAP Database Backups\SSAS\OLAP_test.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>

Answer 1

<File>K:\OLAP Database Backups\SSAS**OLAP_test.abf**</File> <AllowOverwrite>true</AllowOverwrite>

That <File> parameter is the backup file and it is a fix name in your XMLA command. Unfortunately you can't make it dynamically in a job step, you have to find an other backup solution, like:
https://sqlpowershell.blog/2013/06/05/ssas-cube-backup-strategies-and-step-by-step-approach-to-setup-and-configure-backup/
http://www.ssas-info.com/analysis-services-scripts/2679-script-to-backup-ssas-database-and-delete-old-backup-files

Answer 2

Hi @pradeep-9444,

Please do it with PS: 28761.ssas-discuss-various-backup-methods-automating-cube-database-s-backup-with-powershell

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Or you can use this PowerShell script in an Agent job step: Backup Ssas Databases with SessionTrace Output

Answer 4

Hi @pradeep-9444,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.