Automate Backup of Analysis Services Database Using SQL Server Agent Job

By: Editor
March 12, 2010

Introduction

In this article we will take a look at how to create an SQL Server Agent Job to backup an analysis services database. However, read the following article to know the steps you need to follow to restore a database in SQL Server.

Backup Analysis Services Database Using SQL Server Agent Job

1. Start SQL Server Management Studio and connect to Database Engine.

2. Expand SQL Server Agent, right click Jobs and choose New Job… from the popup menu as shown in the snippet below.

3. In the New Job dialog box, provide a name for the job and then click on Steps page to create a new step.

4. In New Job Step, specify an appropriate step name and choose SQL Server Analysis Services Command as the type from the drop down list. Next you need to mention the Analysis Server Name and copy paste the below mentioned XMLA script as shown in the snippet below.

XMLA Script to Backup the Analysis Services Database

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008 SE</DatabaseID>
  </Object>
  <File>D:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\Adventure Works DW 2008 SE.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

5. Click OK to save the New Job Step and return to the parent window. Click OK once again to create the SQL Server Agent Job to automate the backup of analysis services database.

Conclusion

In this article you have seen how to create an SQL Server Agent Job to backup an analysis services database.

Continue Free Learning...

Please leave below your valuable feedback for this article. Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option. Do subscriber to our News Letter to continue your free learning. Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…

• How to Backup an Analysis Services Database Using SQL Server Management Studio
• Database Backup Compression Feature In SQL Server 2008
• Configuring Database Instant File Initialization Feature of SQL Server
• Microsoft SQL Server Build Versions
• How to Cut Costs with the Help of Technology?
• Uninstalling SQL Server 2008
• Read More SQL Server Articles…

Newsletter Signup

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect you privacy and you can unsubscribe at any time."