Automating Backups for SQL Server 2005 Analysis Services Databases

By:   |   Comments (5)   |   Related: > Analysis Services Administration


SQL Server 2005 Analysis Services does not offer any straight forward way to automate backups for its databases. There are no objects in the Maintenance Wizard or Integration Services for this task. Even the Analysis Services built-in (right-click) backup option has no automation or job scheduling options. So how can you setup an automated task to back up your analysis services databases?


The trick is to script the Analysis Services backup task and then create a SQL Server Agent Job to schedule the backup.

Here's how:

Right click on your Analysis Services database and choose the Backup option.


Choose the "Allow file overwrite" option. Set other options as desired. Check and/or change the default save location using the Browse button. Note that that you will not be able to retain multiple version backups. You should make other arrangements to save the backup file to tape for retention purposes.


Once your options are set, click the Script dropdown and select "Script Action to New Query Window".


Connect to the Database Engine of your server and create a new SQL Server Agent job. Create a new job step and select SQL Server Analysis Services Command as the Type. Type the server name in the Server box. Copy and paste the results of your Script Action to the Command window.


Here is a sample of the code that was pasted.

<Backup xmlns="">
<DatabaseID>Analysis Services Tutorial</DatabaseID>
<File>Analysis Services Tutorial.abf</File>

Schedule the job as you normally would with the SQL Server Agent and your backup is now all set to run on a scheduled basis.

Next Steps
  • Monitor the file size of the backup file (and your hard disk).
  • Once created, save the backup file to tape if you need a version history.
  • Read other Analysis Services tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, July 12, 2013 - 9:27:49 AM - Rob Fisch Back To Top (25814)

Hi Rahul,

I might suggest making a rotation scheme, much the way you might plan standard backup jobs (i.e. Mon, Tues, Wed, Thurs, Fri, Week 1, Week 2...Month 1, Month 2,....Quarter 1, Quarter 2...etc


Friday, July 12, 2013 - 3:44:14 AM - Rahul Back To Top (25808)

Hello, rob

I want multiple instance of the database backup file...

I actually do not want to overwrite the file instead want to create a new one ....

Is this possible or can You Suggest a work around? 

Friday, August 12, 2011 - 9:35:11 AM - Rob Fisch Back To Top (14381)

Hi Pete,

Unfortunately I have not had the opportunity to test this solution with SP4. Have you tried recreating the script and the job from scratch? It's not much work.


Friday, August 12, 2011 - 9:16:04 AM - pete Back To Top (14378)

This stopped working when I installed SQL2005 SP4. Do you have a fix?

Thursday, August 28, 2008 - 6:58:52 AM - bouzahme Back To Top (1713)


If I have to backup 3 or 4 analysis services databases, how I can do this.


get free sql tips
agree to terms