SQL Server Performance Forum – Threads Archive

Backup/restore question

During my years working with SQL Server I have done very little work with Analysis Services. Now we soon have it running in a cluster and need to catch up. Does anyone have a good procedure for backups of the data, some scripts with error handling etc? Looking at the microsoft docs I have two options; Msmdarch.exe and standard file backup of the data folder. 1.
Msmdarch saves any error information in a log file and I don’t want to check this manually each time. I need some automatic way of doing it if choosing this procedure. Also the files can be max 2GB. Is it common to have bigger files than that? 2.
When backing up the data folder it’s recommended to shut down analysis services first. I don’t know yet if this will be an option or not. Most likely not if you want to do a backup every hour. Since the cubes are based on data in OLTP databases I guess they can always be rebuilt. Not having worked much with analysis services what kind of timeframe are we talking about here. Could it take hours to build a cube based on 1GB of data? Where can I find some info on this (I dont have any live data to test with yet).

---

You could look into Meta Data Scriptor Utility from the SQL Server Resource Kit which will script your cube to a vb file. The cube can be rebuilt like you mentioned, processing time is determined by the size of your fact and dimension tables and storage mode selected, Analysis Services also has built in functions to minimize this processing time such as setting partitions and incremental processing.
Here is cool link.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapunisys.asp Archiving and restore Analysis Serviceshttp://doc.ddart.net/mssql/sql2000/html/olapdmad/agarchive_2p4j.htm Good luck!

---

Thx, I’ll have a look at the links.

---

]]>