Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automating Backups for SQL Server 2005 Analysis Services Databases


By:   |   Last Updated: 2008-02-15   |   Comments (5)   |   Related Tips: > Analysis Services Administration

Problem

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?

Solution

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="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Analysis Services Tutorial</DatabaseID>
</Object>
<File>Analysis Services Tutorial.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

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



Last Updated: 2008-02-15


next webcast button


next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

Rob


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

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

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.

Rob


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

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

Hi,

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

Thanks


Learn more about SQL Server tools