SSAS Database Backup

Question

SSAS Database Backup

Dinesh Asanka

SSChampion

Points: 11058
More actions
July 16, 2008 at 11:32 pm

#103534

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

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

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

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

Joe Fuller Old Hand Points: 388 More actions · Answer 1

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 More actions · Answer 2

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: 768 More actions · Answer 3

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

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

amoServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

July 18, 2008 at 3:07 am

#844861

Good one ...

Ben Ahmed Valued Member Points: 67 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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

Jonathan Butler Right there with Babe Points: 768 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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

</Object>

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

</Backup>

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

Thanks!