SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Hi

has anyone came across a script that will allow you to automatically script out Analysis Services databases to a file. Rather than backing up each of the analysis services database each night - I'd like to be able to script out the database metadata to a file so that it could be used if we needed to re-create the database.

I know you can script out each database individually from management studio - however I'd like to run the script so that if a new database is created it will automatically get scripted out as well.

Thanks in advance

Hi,

I have not seen such script and I do collect all available script and add link to them on this website:
ssas-info.com/analysis-services-scripts

I think the reason for that - if you have database on the server, then you must have it somewere offline project that you re-deploy when you need to. I usually do have such structure on my development machine and in Visual Source Safe, so I can recover it any time I need to. Database structure changes quite infrequently, so there is no real reason to backup structure (metadata) every night. I understand that you might have different environment so your requirements might be different from everybody else, but as majority of people do not need that - nobody wrote such script so far.

Hi - Thank you for taking the time to reply.

I understand what you posted and agree with what is said. My requirement is I believe company specific in that they would like the the analysis DB's scripted and stored - not each night but after a certain time frame just in case there are issues as the data contained within them can easily be re-created from the underlying databases.

Just wondered if there was such a script out there. Might have to try and write one myself....

Regards
Ronnie

Ronnie:
I have a similar need as yourself ... regular back-ups of the meta-data ... the problem with using an SSAS project, TFS (sourcesafe) and a redeploy is you have to rebuild the data when you redeploy ... in our case that is terrabytes of data and weeks of processing .... we make small changes to our live DB (formats changes etc) and we want to captures those for furture rebuilds.

I will share anything I find

D

You could do this with Powershell. I just blogged a small sample script here geekswithblogs.net/darrengosbell/archive...n-ssas-database.aspx

However if you just want to change parts of your MDX Script without re-deploying the whole project you should also look at the Deploy MDX Script feature in BIDS Helper as then you could still keep everything in your project and in TFS.