Freigeben über

Analysis Services Backup

  • Artikel

Backing up a cube is not an ideal experience in SQL Server 2005 for two reasons, it’s a manual process to schedule a backup and as the size of the cube grows the backup time increases exponentially i.e double the size of the cube and the backup time and size will increase by a factor of four.

Curiously although there is excellent management tools support for managing SQL Server as I mentioned yesterday, I am not aware of similar support for analysis services, despite the many third party tool vendors who make front ends for it. so here’s a few things to help..

To automate  a cube backup use the SQL Server Management Console to  generate a script, by selecting the analysis services database to be backed up  right click select backup and then click on the script icon as shown below:

image

The generated script will look like this:

<Backup xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>Adventure Works DW 2008</DatabaseID>

  </Object>

  <File>Adventure Works DW 2008.abf</File>

  <AllowOverwrite>true</AllowOverwrite>

</Backup>

.. depending on the options you set  (like checking allow file overwrite).  Anyway copy the script to the clipboard and then create a SQL Agent job to run that script.  the job step should then look like this once you have pasted the script.

image

You will of course want to test the job and verify the cube can be restored.  You can get more info on all of this here.

For really large cubes you may need to go for the raw backup solution mentioned in this article, but the other cure for the backup explosion problem is to upgrade Analysis Services 2008 as the size and time taken to do backups is a linear progression i.e. doubling the size of the cube requires double the space and double the time as you’d expect. 

Moving a cube from analysis services 2005 to 2008 is about the most painless upgrade you can do and you can do this with a backup/restore or it will just get upgraded if you are doing an in place migration. 

Technorati Tags: Analysis Services,backup

Comments