Automating Backups for SQL Server 2005 Analysis Services Databases

By: Rob Fisch   |   Updated: 2008-02-15   |   Comments (5)   |   Related: > 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
  
  

About the author

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