Automated Cube Restore

• LAW1143

  SSCrazy

  Points: 2743

  More actions

  October 1, 2013 at 1:21 pm

  #299308

  I'm a bit of an SSAS Noobert so if I'm working at this in the wrong direction, I'd appreciate some direction... please bear with me...

  anyways, I have a training site for a customer. For various reasons, we need to restore a copy of the most recent back up of their _BI database to the server/system on which this training site runs daily.

  The first run following an SSAS restart works. any subsequent runs hang and run forever (or until SSAS is cycled)

  If I run mdx, select * from @System.discover_sessions, I can see my session, spid etc.

  in the System.discover_connections view, there is not a corresponding result.

  running

  <Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <SPID>xxxx</SPID>

  <CancelAssociated>1</CancelAssociated>

  </Cancel>

  with the correct spid doesnt change the results of the System.discover_sessions view, nor does it return an error indicating that i didnt pass an invalid session ID or some such.

  after a successful restore, there will still be a lock on the databases, with a session showing the 'restore' command has been run.

  the command the restore runs is:

  <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <File>B:\path\filename.abf</File>

  <DatabaseName>DBName_BI</DatabaseName>

  <AllowOverwrite>true</AllowOverwrite>

  <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">P:\fileStructure\Data\</DbStorageLocation>

  </Restore>

  Is this a known bug my GoogleFu hasnt uncovered? Am I doing something blatantly wrong?

  FWIW, a VERY similar set of logic works on another server without issue.

  this is a sql 2008R2 system on SP1