{"id":27,"date":"2007-07-26T20:01:16","date_gmt":"2007-07-27T01:01:16","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/27_script-to-backup-analysis-services-2005-databases"},"modified":"2009-06-15T18:23:25","modified_gmt":"2009-06-15T23:23:25","slug":"script-to-backup-analysis-services-2005-databases","status":"publish","type":"post","link":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/27_script-to-backup-analysis-services-2005-databases","title":{"rendered":"Script to backup Analysis Services 2005 databases"},"content":{"rendered":"

Recently I was asked for a generic script that does backup of all Analysis Services 2005 databases on one server.<\/p>\n

\u00a0I quickly wrote SSIS package that does just that. You can download this package from here.<\/a><\/p>\n

Imports System
\nImports System.Data
\nImports System.Math
\nImports Microsoft.SqlServer.Dts.Runtime<\/p>\n

Public Class ScriptMain
\n\u00a0\u00a0\u00a0 Public Sub Main()
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sSSASServerName As String = CStr(Dts.Variables(“SSASServerName”).Value)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sBackupLocation As String = CStr(Dts.Variables(“BackupLocation”).Value)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If Right(sBackupLocation, 1) <> “\\” Then sBackupLocation = sBackupLocation + “\\”<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim Locations() As Microsoft.AnalysisServices.BackupLocation
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oServer As New Microsoft.AnalysisServices.Server
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.Connect(sSSASServerName) ‘ connect to the server and start scanning down the object hierarchy
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oDB As Microsoft.AnalysisServices.Database
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oDB In oServer.Databases
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘If oDB.Name = “AdventureWords” Then ‘ here you can include or excluded databases
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(“yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘End If
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Success
\n\u00a0\u00a0\u00a0 End Sub
\n\u00a0
\nEnd Class<\/p><\/blockquote>\n

If you wish you can use CaptureXML technique (described in my previous blogs) for doing backups in parallel, but I\u00a0usually like to leave\u00a0some server power to users so they can query data while backups are\u00a0executed. Running multiple database backups in parallel could have\u00a0huge performance impact for end user querying.\u00a0<\/p>\n

You can execute this package from command line and pass parameters that specify server name and location:<\/p>\n

dtexec \/FILE “C:\\DTSX_Scripts\\DWBackupOLAPDBs.dtsx” \/Set \\package.variables[BackupLocation].Value;c:\\NewBackupLocation\\ \/Set \\package.variables[SSASServerName].Value;NewSSASServerName<\/p><\/blockquote>\n

When I have to write\u00a0a script to backup one or a few databases with a known name, then I usualy use XMLA command:<\/p>\n

\n

<Backup xmlns=”http:\/\/schemas.microsoft.com\/analysisservices\/2003\/engine”>
\n\u00a0 <Object>
\n\u00a0\u00a0\u00a0 <DatabaseID>MyDBName<\/DatabaseID>
\n\u00a0 <\/Object>
\n\u00a0 <File>c:\\BackupLocation\\MyDBName.abf<\/File>
\n<\/Backup><\/p><\/blockquote>\n

\u00a0This XMLA script could be executed using one of these methods:<\/p>\n

    \n
  • SSIS package using control flow item “Analysis Services Execute DDL task”<\/li>\n
  • SQL Server Job create step with type “SQL Server Analysis Services command”<\/li>\n
  • using ascmd.exe utility.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

    Recently I was asked for a generic script that does backup of all Analysis Services 2005 databases on one server. \u00a0I 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.Runtime Public Class ScriptMain \u00a0\u00a0\u00a0 Public Sub Main() \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sSSASServerName As […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/27"}],"collection":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=27"}],"version-history":[{"count":2,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/27\/revisions"}],"predecessor-version":[{"id":173,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/27\/revisions\/173"}],"wp:attachment":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=27"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=27"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=27"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}