SQL Server Performance Forum – Threads Archive
Backing up analysis services cube to network driveGreetings. We have a 400+ gb cube that we need to back up to a network drive. I created a 10 gb mini-cube to test it out. When I couldn’t put my backup destination in the interface, I saw a message about the AllowedBrowsingFolders property, so I put the drive in the server property. When I still could not "see" the drive in the destination window I tried just typing in the drive. That gave me an error of "File system could not find the path specified." Even though server properties stated that update did not require a restart of AS I did so anyway (I have had that experience previously with server properties). Though the drive is clearly in the property, the backup still could not see it. I finally came across a similar post somewhere in this forum that said to try the network path if a connection had been lost. So once again I went to my backup and this time used \server$Nfolder. The backup actually began executing and I saw it allocate the .abf file on the N: drive. However, after 5 minutes it failed with an error stating "The specified network name is no longer available" and the .abf was deleted. Could it be a timeout on the destination server? Any idea how to get the propoerty to work? Additionally, just to benchmark runtime I backed my mini-cube up to it’s own drive and it ran for 10 minutes. I was actually quite shocked and concerned with how the big cube backup would scale, especially over a network connection. I have been working on this for a week, done much research (ad nauseum on Microsoft), and chronic frustration is setting in. Does anyone have an ideas or experience working on backing up a cube of this size? Any help at all would be GREATLY appreciated… -Helen
I wanted to give more information on this in case anyone else encounters these issues. I had a discussion with my local MVP. He indicated that the native analysis services backup was intended to run on a local drive (and not over the network). He thought the reason I couldn’t create visibility for the drive in the properties (that I attached to the server) was because it was attached under my ID and not SQL Server. We did talk about the time it took to create a local backup of my 10 gb mini-cube (10 minutes) and we agreed it wasn’t a scalable option for the 400+ gb, even if I had the space. His suggestion for a backup was to copy the files over to another drive which I’m going to benchmark tonight. If anyone has experience with a cube this big I’d still like to hear from you. Thanks!
In 2005 these is an option to compress the backup file and you can script the schema without the data also.. In 2000 it was possible to backup to network but it was using different command not XMLA code… Check ASCMD utility if it has any options…
I have tested the backup to network drive using UNC name it worked fine… Here is the code… <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
I will look into these suggestions. Thanks very much MohammedU!!! -Helen