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>

IF I choose "Allow file overwrite option" to False , the job throwing the error

backup file already exists. Provide a different file name, delete the existing backup file, or set the AllowOverwrite setting to True, then repeat the operation.

  • Have you tried the suggestion in that error message? Ie a new filename – Tom V Jan 15 at 20:38
  • Why do you want multiple backups for an SSAS database? Having one to restore in case processing fails makes sense. But for most operational issues, you would just reprocess the cube. – David Browne - Microsoft Jan 16 at 2:39

The solution to this is fairly straightforward. Using SQL Agent Job Tokens, you can include the date and time of the backup in the filename to ensure a new file is created each tiem the job runs preventing the overwrite.

Change the command in the SSAS job step to this:

<Backup xmlns="schemas.microsoft.com/analysisservices/2003/engine">
   <File>K:\OLAP Database Backups\SSAS\OLAP_test_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).abf</File>

When you run the backup job after changing the step, it will produce a file with the date and time in the format OLAP_Test_YYYYMMDD_HHMMSS.abf.

| improve this answer | |
  • I tried to take backup using above query. Its throwing syntax error <Backup xmlns="schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Cube_Test_Env1</DatabaseID> </Object> <File>G:\OLAP Backup\Cube_Test_Env1_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup> Output: ----------- Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '<'. – pradeep Sep 16 at 15:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.