locked
How to move SSAS Database from one server to another RRS feed

  • Question

  • HI All,

    I have a 2005 server and i can open ANALYSIS SERVICES in VISUAL STUDIO (ABC_DATABASE). This database has DATA SOURCES and DSV's , dimensions and CUBES. How can i move this whole database to the new server which is 200R2? Please need help.

    Thanks


    SV

    Monday, September 10, 2012 7:08 PM

Answers

All replies

  • Hello,

    You can either backup the database as an ABF file and restore to the new server using SQL Server Management Studio 2008 R2, or use BIDS 2008 to import from analysis services database on the SQL 2005 server and deploy to SQL 2008 R2 server and process the database.

    Open in Visual Studio just opens the live database.  You should try import.

    Regards,

    Andrew


    Andrew Sears, T4G Limited, http://www.performancepointing.com

    Monday, September 10, 2012 7:44 PM
  • Thanks Andrew but i'm little bit confused here. I backed up the ANALSIS services database on the 2005 SERVER, then i logged onto the new server (2008 R2) and opened the ANALYSIS SERVICES using MS. Then what should i do here as when i right click on the databses and new database there is no option for restore. Please let me know where i'm going wrong.


    SV

    Monday, September 10, 2012 7:50 PM
  • Hi SaiVenkat,

    Just right-click the Databases(the root folder) folder and select restore and you should be able to restore your back files. Please see the screen shot below.


    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Monday, September 10, 2012 9:49 PM
  • Hello,

    Just had a quick suggestion, how big is your Analysis server db ? if its not much big and doesn't takes much time to process you can just script out the Create DB statement and run it on 2008 R2 instance. This will create a DB exactly as same as the other one you had with all dimensions and other objects.

    Then do a full process to populate the cubes with data. If you think the restore option may work faster than processing then go for it.


    Vinish Viswanathan

    Tuesday, September 11, 2012 11:16 AM
  • Hi,

    How about creating XMLA file and execute it in another environment.

    Thanks,

    Munna

    • Marked as answer by saivenkat77 Tuesday, September 11, 2012 3:57 PM
    Tuesday, September 11, 2012 11:48 AM
  • Thanks Muhammed ,

    But when i did back up on the old server it was saves as .abf file and i copied that file onto the new server and followed the procedure as illustarted in your answer, but the file i see is .abf extension and not a .db database.Please need help

    Thanks


    SV

    Tuesday, September 11, 2012 1:32 PM
  • .abf is the extension for an Analysis Services database, don't worry about it, just restore the database.

    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    • Marked as answer by saivenkat77 Tuesday, September 11, 2012 3:57 PM
    Tuesday, September 11, 2012 1:47 PM
    • Marked as answer by saivenkat77 Tuesday, September 11, 2012 3:57 PM
    Tuesday, September 11, 2012 1:49 PM
  • Muhammed i'm getting the following error when i'm trying to restore the database:


    SV

    Tuesday, September 11, 2012 2:18 PM
  • You can try the option i told, just script out the create script and run it on the new server and then process your cube. Also as Vijju mentioned u can create xmla file to create and process your cubes and call one after the other via a batch file, so when u run a batch file it will first create your db and then full process it.


    Vinish Viswanathan

    • Marked as answer by saivenkat77 Tuesday, September 11, 2012 3:56 PM
    Tuesday, September 11, 2012 3:29 PM
  • Thanks Muhammed,Vini,Aniruddha and Vijju. I tried Muhammed's option and now its working great. Also i tried the other methods just for learning purposes. Glad i learnt something new. Thanks again to all of you.

    SV

    Tuesday, September 11, 2012 3:56 PM