RSS Feed
Script to backup Analysis Services 2005 databases
July 26th, 2007 by Vidas MatelisRecently I was asked for a generic script that does backup of all Analysis Services 2005 databases on one server.
I quickly wrote SSIS package that does just that. You can download this package from here.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain
Public Sub Main()
Dim sSSASServerName As String = CStr(Dts.Variables(“SSASServerName”).Value)
Dim sBackupLocation As String = CStr(Dts.Variables(“BackupLocation”).Value)
If Right(sBackupLocation, 1) <> “\” Then sBackupLocation = sBackupLocation + “\”Dim Locations() As Microsoft.AnalysisServices.BackupLocation
Dim oServer As New Microsoft.AnalysisServices.Server
oServer.Connect(sSSASServerName) ‘ connect to the server and start scanning down the object hierarchy
Dim oDB As Microsoft.AnalysisServices.Database
For Each oDB In oServer.Databases
‘If oDB.Name = “AdventureWords” Then ‘ here you can include or excluded databases
oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(“yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
‘End If
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
If you wish you can use CaptureXML technique (described in my previous blogs) for doing backups in parallel, but I usually like to leave some server power to users so they can query data while backups are executed. Running multiple database backups in parallel could have huge performance impact for end user querying.
You can execute this package from command line and pass parameters that specify server name and location:
dtexec /FILE “C:\DTSX_Scripts\DWBackupOLAPDBs.dtsx” /Set \package.variables[BackupLocation].Value;c:\NewBackupLocation\ /Set \package.variables[SSASServerName].Value;NewSSASServerName
When I have to write a script to backup one or a few databases with a known name, then I usualy use XMLA command:
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>MyDBName</DatabaseID>
</Object>
<File>c:\BackupLocation\MyDBName.abf</File>
</Backup>
This XMLA script could be executed using one of these methods:
- SSIS package using control flow item “Analysis Services Execute DDL task”
- SQL Server Job create step with type “SQL Server Analysis Services command”
- using ascmd.exe utility.
Posted in SSAS | 17 Comments »
August 13th, 2007 at 10:14 am
Excellent article. I like the ssis approach much better than the AMO csharp examples that MS provides.
April 18th, 2008 at 1:22 pm
Vidas, thanks! This is exactly what I was looking for.
November 11th, 2008 at 6:01 am
Please send me the project on my email id its very urgetn, I am not able to download the same..
my mail id is mohd…
May 22nd, 2009 at 1:14 am
Please send me the project on my email id its very urgetn, I am not able to download the same..
my mail id is santosh.patna.net@gmail.com
June 11th, 2009 at 8:10 am
Hi,
The Microsoft.AnalysisServices namespace does not seem to be registered on the server I am using, although it has a full installation of SQL/AS 2005 Enterprise on it. Any ideas where I can find the component?
R.
June 15th, 2009 at 6:04 pm
Hi,
Can you please send this dtsx package on my email id sunnysqldba@gmail.com?
Thanks in advance.
Sunny
June 16th, 2009 at 6:07 pm
Hi Vidas,
Thanks for your help.
I have one more query,
How can we schedule to remove 10 days old these Analysis Services backup files?
Thanks
Sunny
January 6th, 2010 at 10:43 am
does this script work with SQL 2008?
January 6th, 2010 at 10:47 am
Yes, this script will work with SSAS 2008.
January 7th, 2010 at 10:38 am
Hi there – can you advise me how I would incorporate a new variable into the script for the day of the week.
I’d like the backup path to make the backup path something like:
\\server\share\backups\dayofweek\
What language is the script also written in as well.
January 7th, 2010 at 3:32 pm
Hi BravehearT1326,
This code is written inside SSIS script task and it is using VB.
You should be looking to update string:
oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(“yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
Just find in VB how to get name or day of week and then you would write something like this:
oDB.Backup(sBackupLocation & dayofweek & “\” & oDB.Name & “.abf”, True, False, Locations, True)
January 8th, 2010 at 4:35 am
Many thanks for your speedy reply and great script. I will make full use of this.
January 20th, 2010 at 12:42 am
Hi, I use a SQL job to backup the data as per below, but I am receiving an error “Allocation failure : Not enough storage is available to process this command.” some times, we have 24 GB of RAM. Any idea of how to fix it? – thanks.
DW Analysis Services
\\Smelpinfxxx\SQLBackups\SMELPSQLxxx\AnalysisServices\DW Analysis Services.abf
true
false
January 20th, 2010 at 8:52 am
EJ,
I have not seen case when not enough RAM would affect backup. It is much much more likely that you do not have permission to write to that \\smelpinfxxx share or if you have quote setup on that share that prevents you to write more than x MB of data there. Make sure that SQL Server Agent service account has permission to write to that share as it is used when you run SQL job.
March 16th, 2010 at 9:49 am
I have the same problem that EJ. I try to backup in the same server (no shared directory). Day before Backup size 1GB but in one day this size increments in a 20 percent (Historical Load has been realized). The Process fail but leave a .abf file with a size similar size to before backup.
the specific error in SSIS 2008 is
[Analysis Services Execute DDL Task] Error: Memory error: Allocation failure. I try in SSAS and the same error. Please Notice me, and excuse my poor english.
Gsus
June 1st, 2011 at 5:43 am
Thanks, very useful
August 25th, 2011 at 12:46 pm
Hello.. I have this script in my SQL Scheduler but it is not parsing. I get when I execute via SQL Agent Job but not when I run in query window where I generated the script. It is complaining on the ‘>’ syntax.any ideas? Valerie