SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS Database Backup


SSAS Database Backup

Author
Message
Dinesh Asanka
Dinesh Asanka
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3900 Visits: 223
Comments posted to this topic are about the item SSAS Database Backup






My Blog: http://dineshasanka.spaces.live.com/
Joe Fuller
Joe Fuller
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 256
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
SQLshifu
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 134
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
Jonathan Butler
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 128
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
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11881 Visits: 1407
Good one ...



Ben Ahmed
Ben Ahmed
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 26
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
sunny.manghnani
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 247
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
Matthew Ward-489444
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 81
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
saxena.saurabh12
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 22
I am also getting the same error. Any workarounds on this?
Jonathan Butler
Jonathan Butler
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 128
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search