<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Dinesh Asanka
»
SSAS Database Backup
12 posts, Page 1 of 2
1
2
»»
SSAS Database Backup
Rate Topic
Display Mode
Topic Options
Author
Message
Dinesh Asanka
Dinesh Asanka
Posted Wednesday, July 16, 2008 11:32 PM
SSC Eights!
Group: General Forum Members
Last Login: Friday, February 22, 2013 9:30 PM
Points: 912,
Visits: 198
Comments posted to this topic are about the item
SSAS Database Backup
My Blog:
http://dineshasanka.spaces.live.com/
Post #535679
Joe Fuller
Joe Fuller
Posted Wednesday, July 16, 2008 11:37 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:26 PM
Points: 20,
Visits: 218
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
SQLshifu
SQLshifu
Posted Thursday, July 17, 2008 7:49 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, June 03, 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
Jonathan Butler
Jonathan Butler
Posted Thursday, July 17, 2008 7:54 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 31, 2013 8:21 AM
Points: 50,
Visits: 127
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
Anipaul
Anipaul
Posted Friday, July 18, 2008 3:07 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,815,
Visits: 1,343
Good one ...
Post #536562
Ben Ahmed
Ben Ahmed
Posted Wednesday, September 24, 2008 5:14 AM
Forum 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
sunny.manghnani
sunny.manghnani
Posted Monday, June 15, 2009 11:28 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, July 09, 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
Matthew Ward-489444
Matthew Ward-489444
Posted Monday, September 14, 2009 11:16 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 02, 2012 5:05 PM
Points: 1,
Visits: 79
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
saxena.saurabh12
saxena.saurabh12
Posted Friday, February 26, 2010 4:16 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 01, 2011 12:56 AM
Points: 2,
Visits: 17
I am also getting the same error. Any workarounds on this?
Post #873314
Jonathan Butler
Jonathan Butler
Posted Tuesday, March 16, 2010 5:11 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 31, 2013 8:21 AM
Points: 50,
Visits: 127
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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
