none
Restore 2012 cube to 2016 on different server

    Question

  • Hi all,

    I've backed up a ssas database on a machine running SQL 2012. 

    When i to restore on a different machine, running sql 2016 - using the below:

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <File>C:\Data\OLAP\Backups\fmDem.abf</File>
      <DatabaseName>FMSCube</DatabaseName>
      <Locations>
        <Location>
          <Folders>
            <Folder>
              <Original>C:\Program Files\Microsoft SQL Server\MSAS11.SQL2012\OLAP\Data</Original>
              <New>C:\Data\OLAP\Logs</New>
            </Folder>
          </Folders>
        </Location>
      </Locations>
      <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Data\OLAP\Databases\</DbStorageLocation>
    </Restore>


    I receive the following error:

    The following system error occurred:  Access is denied. 

    File system error: Error occurred during the creation of directory: '\\?\C:\Program Files\Microsoft SQL Server\MSAS11.SQL2012\OLAP\Data\6B03BBE5C52541399730'.
    Server: The current operation was cancelled because another operation in the transaction failed.

     That directory doesn't exist on the new machine. Don't understand why it'd need to - aren't I asking for the partitions to be created in: 

    C:\Data\OLAP\Logs

    BTW, i've tried a few different directories for my partitions - including a sub folder of Databases folder.

    My server's DataDir = C:\Data\OLAP\Databases

    Appreciate your help.

    Clay


    Tuesday, June 12, 2018 6:39 AM

All replies

  • Hi Clay,

    Thanks for your question.

    Please copy the backup ABF file to the folder C:\Data\OLAP\Backups\ in the destination SSAS 2016 server, and try below XMLA script to restore the Analysis database.

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <File>C:\Data\OLAP\Backups\fmDem.abf</File>
      <DatabaseName>FMSCube</DatabaseName>
      <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Data\OLAP\Databases\</DbStorageLocation>
    </Restore>

    >>>That directory doesn't exist on the new machine. Don't understand why it'd need to - aren't I asking for the partitions to be created in:
    For Restore commands, the Location element provides information about identifying and connecting to a remote Analysis Services instance, as well as the remote backup file used to restore remote partitions on that remote instance. In your scenario, you just need to delete the Location element.

    Please refer to below blog about how to use XMLA script to restore the Analysis database:
    http://www.sqlcircuit.com/2013/08/ssas-xmla-script-to-restore-analysis.html


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 13, 2018 1:39 AM
    Moderator