SQLServerCentral / Discuss Content Posted by Dinesh Asanka / Article Discussions / Article Discussions by Author / SSAS Database Backup / Latest Posts

RE: SSAS Database Backup

t_ciossu — Wed, 18 Jan 2012 07:50:13 GMT

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<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>SSAS_DB_NAME</DatabaseID> </Object> <File>\\share_path\\SSAS_DB_NAME.abf</File> <AllowOverwrite>true</AllowOverwrite></Backup>Question is if I can do like 3 backups from the same command in one job agent step.Thanks!

RE: SSAS Database Backup

syed.sherfudeen — Sun, 04 Sep 2011 05:47:35 GMT

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

RE: SSAS Database Backup

Jonathan Butler — Tue, 16 Mar 2010 05:11:02 GMT

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.[quote][b]Jonathan Butler (7/17/2008)[/b][hr]Here's VB Net Script to do the same using AMO....in SSISVariables are declared at Package level and are configured using Package configurations (XML files).[/quote]----------Pragmatism is a disease that gets things done.

RE: SSAS Database Backup

saxena.saurabh12 — Fri, 26 Feb 2010 04:16:46 GMT

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

RE: SSAS Database Backup

Matthew Ward-489444 — Mon, 14 Sep 2009 23:16:20 GMT

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 '[i]new database name[/i]' does not exist in the server with the ID of '[i]server name[/i]', or the user does not have permissions to access the object. End Error Any ideas on a workaround?

RE: SSAS Database Backup

sunny.manghnani — Mon, 15 Jun 2009 11:28:08 GMT

Hello Dinesh, Thanks for the post Can you please help to add datestamp along with backup file in your provided code belowXMLS ScriptBackup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> Object> DatabaseID>AdventureWorksDW /Object> File>AdventureWorksDW.abf AllowOverwrite>true Password>password/Backup>

RE: SSAS Database Backup

Ben Ahmed — Wed, 24 Sep 2008 05:14:56 GMT

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.SuccessEnd Sub

RE: SSAS Database Backup

Anipaul — Fri, 18 Jul 2008 03:07:46 GMT

Good one ...

RE: SSAS Database Backup

Jonathan Butler — Thu, 17 Jul 2008 07:54:52 GMT

Here's VB Net Script to do the same using AMO....in SSISVariables are declared at Package level and are configured using Package configurations (XML files).This is the MAIN() body of codeImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.AnalysisServicesPublic 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 SubEnd Class

RE: SSAS Database Backup

SQLshifu — Thu, 17 Jul 2008 07:49:20 GMT

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?

RE: SSAS Database Backup

Joe Fuller — Wed, 16 Jul 2008 23:37:24 GMT

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.

SSAS Database Backup

Dinesh Asanka — Wed, 16 Jul 2008 23:32:31 GMT

Comments posted to this topic are about the item [B]SSAS Database Backup[/B]