Backup Script Completing But Not Working

I have a SQL Server Agent job that executes the script listed below (using a step of type "Analysis Services Command") to do a backup of an SSAS database on a nightly basis. The job takes a while (say 15 mins or so) and appears to be busily doing something (meaning the CPU's on the server have activity). However, after the script completes, there is no backup file anywhere to be found. Anyone have any idea what might be happening?

BTW, if I execute this command manually as an XMLA command while in a query window in SSMS, it seems to complete successfully (producing the .abf file) every time...

Dave Fackler

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

There is a problem with SQL Agent not reporting propertly case of failed XMLA command.

It should have been fixed in SP2. Try installing CTP of SP2 and see if you are geting failure of SQL Agent Job.

Another suggestion. Make sure your SQL Agent job has server name defined correctly.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I'll take a look at SP2. However, even if that resolves the issue of SQL Agent not reporting when an XMLA command fails, it doesn't really help with WHY the XMLA command would be failing in the first place. The server name is definitely defined correctly and the server seems to be doing something while the command executes. But no .abf file is generated. Is there some way I can troubleshoot this issue to determine why the XMLA command might be failing?

Dave F.

|||

Once you get a fix in SQL Agent you'll be able to get a useful error message telling you the nature of the problem. And that should help troubleshooting SQL Agent job.

For now you can try installing CTP of SP2.

Another idea for you is to use ascmd utility that allows you to execute XMLA commands. It has great functionality allows for having parametric XMLA command, where you can simply plug name of your DB and that would take care backing up your database. It also allows logging output of XMLA command's execution. Once you get it working, you can use SQL Agent to schedule backup using ascmd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am running SQL 2005 with SP2 and have the same issue.

When I use a SQL Agent job with a Sql Server Analysis Services Command it does not report any error or failure in that command.

An example command is:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>PGPDailySales</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If the cube definition or data has errors so this command fails, the Agent Job step still reports success.

How can this issue be reported to Microsoft so it may be fixed in the next version?