Automating Backups for SQL Server 2005 Analysis Services Databases

By: Rob Fisch | Read Comments (3) | Print Rob has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer. Related Tips: More

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
  
  

Related Tips: More | Become a paid author

Last Update: 2/15/2008

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Thursday, August 28, 2008 - 6:58:52 AM - bouzahme	Read The Tip
Hi, If I have to backup 3 or 4 analysis services databases, how I can do this. Thanks

Friday, August 12, 2011 - 9:16:04 AM - pete	Read The Tip
This stopped working when I installed SQL2005 SP4. Do you have a fix?

Friday, August 12, 2011 - 9:35:11 AM - Rob Fisch	Read The Tip
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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured." The 10 tools in the SQL Developer Bundle cut the time spent in dull and tedious tasks. Learn more. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think