Archiving AS 2000 Database to a network backup server

WE are currently using the following to archive our AS 2000 OLAP cubes to the local directory on the server:

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "c:\BackupFiles\DataMart.cab" It runs inside an agent job. It works fine.

I wanted to change it to archive to our standard backup server location. So I changed it to the following:

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab". The job now fails & outputs nothing to the log file. The only thing it says when I view history on the job is : Executed as user: SqlAdmin. The step did not generate any output. Process Exit Code 1. The step failed.

At first I thought maybe AS 2000 couldn't archive over the network so I open up AS Manager & manually did an archive to the same network location. It was successfull. So that blew my theory.

I don't know why the job won't do the archive to the network location. Any ideas?

Thanks,

John

If you can manually run the command

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"

then it's possible that the SqlAdmin user doesn't have write permissions to the share.

Adrian

|||

Adrian,

I forgot to mention that SQLAdmin does have privileges to the network backup location. We use this same login for all our SQL backups as well.

I wasn't ever able to get the command to run manually. I was able to manually run the archive process through Analysis manager. Don't know if that makes a difference or not.

Any other ideas?

Thanks,

JOhn

|||

Unless you have changed the default data directory, I think you have an incorrect "datamart" sub folder in your data directory parameter.

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"

try removing it and see if the command works then.

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"

Also, where are you running Analysis Manager from? Judging by the (x86) in the path I would say that your AS2k server is 64bit and all the management tools (including msmdarch.exe) are 32 bit. The last recommendation I saw said to try to run these tools from a 32bit machine and not on the 64bit server. I don't know if that means it would not work, but it might be another option to explore.

|||

Darren,

I removed the extra "datamart" from the command but same result.

Sorry I didn't mention this earlier. I am running W2K3 64 bit with AS 2000 32 bit. I was running Analysis Manager on the 64 bit server when I was able to do the archive manually. WIth this command, I am running it under SQL 2005 EE 64 bit agent job.

I tried running the following command from our 32 bit test server but it had the same result:

"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a 64BitServer"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\64BitServer\DataMart.cab"

Any other ideas?

Thanks,

John

|||

Hmm, running out of ideas.

Have you tried mapping a drive so that you are backing up to z:\... or something like that?

Have you tried specifying the other 2 optional parameter (the logfile and tempfolder)? The log file might give you some ideas, although it might be blank, I seem to remember having trouble like this once, but I can't remember how I resolved it.

Finally, if all else fails there was some documentation, I think in the operations guide whitepaper, on how to "manually" back up an AS2k server by taking a file back up of the data directory and the repository database.

|||

Darren,

A mapped drive seems to work. Although, I don't like using mapped drives on our servers but I may have to make an exception in this instance. Maybe I will have it archive locally & then execute a batch job that would xcopy the .cab file to the backup server.

I also tried adding the log & temp folder parameters but that did not make any difference. I still got the useless blank log file. It seems like this should work or somewhere it would be documented that you can't use UNC paths for the archive process.

Thanks for the help,

JOhn

|||

Hello,

Enjoyed reading the thread, I had a thought that might be helpful:

You might try enabling UNC support for your command prompt. By default, Microsoft ships this flag turned off, but I've yet to have any trouble with it turned on. YMMV.

Microsoft KB156276 instructions for doing this:

Under the registry path:

HKEY_CURRENT_USER
\Software
\Microsoft
\Command Processor

add the value DisableUNCCheck REG_DWORD and set the value to 0 x 1 (Hex).

http://support.microsoft.com/kb/156276

I hope this helps you. Good luck...!

-ed2

|||

Ed,

Thanks for the thought. I added this to the registry & then ran my job but same result. It may need to have a reboot to get the change into effect. However, the KB article lists this as a bug for Win NT 4 & states that it would be fixed in a SP. So I would hope that this has been fixed in Windows 2003 server. I will leave it until after a reboot to see if it works or not.

THe batch job is working great btw.

John

|||

I'm sorry to hear this wasn't a fix... WIth only 8 months of mainstream support remaining on SQL Server 2000, it's hard to imagine this will get much attention, but you might try opening a call if you can justify the expense...

Read the KB article I sent you over again, and you'll find out that the fix is in Win2K, XP, and Win2K3. The KB article said that NT4 originally had no check for UNC pathing in the command prompt, and that caused problems for users who would launch Windows apps from the command prompt session, then close the command prompt and the pipeline to the exe file would become broken. To remedy this Microsoft changed CMD.EXE such that whenever you use a UNC path at the command line it produces an error message UNLESS you put in the registry entry from the KB article. So basically the fix was to decide that it was inappropriate to use UNC pathing at the command prompt. However, I think that once you understand the limitations (and know what not to do), the benefits gained by using the UNC pathing outweigh the risks.

In one of the earlier posts it sounded as though you might be running the job from SQL Server Agent. I've seen some odd behavior from that in some ways -- for example, I wasn't able to call an osql command from SQL Server Agent using xp_cmdshell (pointless as that might seem). So, if you haven't already done so, try the Windows Job Scheduler instead.

Best wishes,

Ed