Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?
sql