SSAS Database Backup

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the item SSAS Database Backup




    My Blog: http://dineshasanka.spaces.live.com/

  • Joe Fuller

    Old Hand

    Points: 388

    Hi,

    I'm planning a scale-out implementation for Analysis and Reporting Services on one environment and database with sql agent on another.

    How can I automate backups in this situation?

    Thanks.

  • SQLshifu

    SSC Eights!

    Points: 953

    Got a similar question here. In my organization, we run sql server database engine and sql server analysis services in different boxes. How do I use sql server agent on a box where only analysis services is running?

  • Jonathan Butler

    Right there with Babe

    Points: 718

    Here's VB Net Script to do the same using AMO....in SSIS

    Variables are declared at Package level and are configured using Package configurations (XML files).

    This is the MAIN() body of code

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.AnalysisServices

    Public Class ScriptMain

    Public Sub Main()

    'Get TimeStamp in form YYYYMMDDhhmmss

    '------------------------------------------------------

    Dim MyTimestamp As String

    MyTimestamp = CStr(Year(Now()) * 10000 + Month(Now()) * 100 + Day(Now()))

    MyTimestamp += CStr(Hour(Now()) * 10000 + Minute(Now()) * 100 + Second(Now()))

    'Connect to SSAS Instance

    '------------------------------------------------------

    Dim amoServer As New Microsoft.AnalysisServices.Server

    Dim strServer As String

    Dim strBackupPath As String

    'Dispensed Variable Collection

    Dim vars As Variables

    ' Lock variables

    Try

    Dts.VariableDispenser.LockForRead("strOLAPServer")

    Dts.VariableDispenser.LockForRead("strOLAPBackupPath")

    Dts.VariableDispenser.GetVariables(vars)

    Catch ex As Exception

    vars.Unlock()

    Throw ex

    End Try

    Try

    strServer = CStr(vars("strOLAPServer").Value)

    strBackupPath = CStr(vars("strOLAPBackupPath").Value)

    Catch ex As Exception

    vars.Unlock()

    Throw ex

    End Try

    Try

    amoServer.Connect(strServer)

    Catch ex As AmoException

    vars.Unlock()

    Throw ex

    End Try

    'Backup Databases

    '------------------------------------------------------

    Dim BackupFileName As String

    Dim AllowOverwrite As Boolean = True

    Dim BackupRemotePartitions As Boolean = False

    Dim MyLocation() As BackupLocation

    Dim ApplyCompression As Boolean = True

    For Each ssasDatabase As Database In amoServer.Databases

    Try

    BackupFileName = strBackupPath + strServer + "\" + ssasDatabase.Name + "_" + MyTimestamp + ".abf"

    Try

    ssasDatabase.Backup(BackupFileName, AllowOverwrite, _

    BackupRemotePartitions, MyLocation, _

    ApplyCompression)

    Catch ex As AmoException

    vars.Unlock()

    Throw ex

    End Try

    Catch ex As Exception

    vars.Unlock()

    Throw ex

    End Try

    Next

    'Disconnect from SSAS Instance

    '------------------------------------------------------

    amoServer.Disconnect()

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

  • Anipaul

    SSC-Insane

    Points: 24681

    Good one ...

  • Ben Ahmed

    Valued Member

    Points: 67

    Hi Dinesh,

    how would you go about adding a timestamp to your existing xmla script for the SSAS backups?, so to individually timestamp each .abf file?

    thansk in advance!

    your script is below:

    Public Sub Main()

    '

    ' Add your code here

    '

    Dts.Variables.Item("ssas_backup_script").Value = _" "

    + _

    " " + _

    " " + _

    " " + _

    " " + _

    " " + _

    " " + _

    " "

    Dts.TaskResult = Dts.Results.Success

    End Sub

  • sunny.manghnani

    SSC Enthusiast

    Points: 100

    Hello Dinesh,

    Thanks for the post

    Can you please help to add datestamp along with backup file in your provided code below

    XMLS Script

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

    Object>

    DatabaseID>AdventureWorksDW

    /Object>

    File>AdventureWorksDW.abf

    AllowOverwrite>true

    Password>password

    /Backup>

  • Matthew Ward-489444

    SSC Journeyman

    Points: 97

    Nice one guys, works a charm. But one of my developers renamed a SSAS Databse now the name and ID don't match. The database has a new name which is beging read into the foreach loop and used to create the xmlns script as . This of cause throws an error.

    Source: Execute SSAS Backup Script Analysis Services Execute DDL Task

    Description: Errors in the metadata manager. Either the database with the ID of 'new database name' does not exist in the server with the ID of 'server name', or the user does not have permissions to access the object.

    End Error

    Any ideas on a workaround?

  • saxena.saurabh12

    SSC Journeyman

    Points: 78

    I am also getting the same error. Any workarounds on this?

  • Jonathan Butler

    Right there with Babe

    Points: 718

    If XMLA script isn't working you could try SSIS using AMO to backup the databases. We backup ours twice a day to catch any deployments........

    Sometimes you have to just use what works.

    Jonathan Butler (7/17/2008)


    Here's VB Net Script to do the same using AMO....in SSIS

    Variables are declared at Package level and are configured using Package configurations (XML files).

    ----------

    Pragmatism is a disease that gets things done.

  • syed.sherfudeen

    Grasshopper

    Points: 13

    Also you can change the Dir as per your wish and file name to without overwriting the DB for archive purpose.you can maintain xml config file for dynamic purpose

  • t_ciossu

    SSC Enthusiast

    Points: 166

    Hi,

    I tried to backup 3 SSAS dbs from the same job step, by creating consecutive XML tags in the same command and if failed. Does anyone know if @command is forced to a single backup command?

    This is what code I've used

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

    <Object>

    <DatabaseID>SSAS_DB_NAME</DatabaseID>

    </Object>

    <File>\\share_path\\SSAS_DB_NAME.abf</File>

    <AllowOverwrite>true</AllowOverwrite>

    </Backup>

    Question is if I can do like 3 backups from the same command in one job agent step.

    Thanks!

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply