Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSAS Database Backup Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2008 11:32 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:38 PM
Points: 912, Visits: 209
Comments posted to this topic are about the item SSAS Database Backup






My Blog: http://dineshasanka.spaces.live.com/
Post #535679
Posted Wednesday, July 16, 2008 11:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:25 PM
Points: 25, Visits: 239
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.
Post #535683
Posted Thursday, July 17, 2008 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 5:55 PM
Points: 35, 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?
Post #535989
Posted Thursday, July 17, 2008 7:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:46 AM
Points: 50, 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
Post #535998
Posted Friday, July 18, 2008 3:07 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 5,342, Visits: 1,387
Good one ...


Post #536562
Posted Wednesday, September 24, 2008 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 5:38 PM
Points: 1, 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
Post #575050
Posted Monday, June 15, 2009 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 9, 2012 2:35 PM
Points: 6, Visits: 241
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>
Post #735193
Posted Monday, September 14, 2009 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 4:00 PM
Points: 1, 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?
Post #787896
Posted Friday, February 26, 2010 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 3:44 AM
Points: 2, Visits: 22
I am also getting the same error. Any workarounds on this?
Post #873314
Posted Tuesday, March 16, 2010 5:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:46 AM
Points: 50, 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.
Post #883653
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse