Isso pode de fato ser feito. Provavelmente existem algumas maneiras de fazer isso, e aqui está um exemplo bastante direto. Para esta solução, você usará uma combinação de:

1. Um trabalho do SQL Agent com uma etapa para cada instância que precisa de backup (ou seja, uma etapa para o servidor de desenvolvimento, o servidor qa e para produção).
2. Um pacote SSIS dinâmico que é chamado em cada etapa do trabalho.
3. Um Execute Script Taskque usa o Analysis Management Objects (AMO) .

Criar o pacote SSIS

Variáveis

VariableName|Scope|Type

• BackupDir | Nível do pacote ou nível Foreach | Corda

• ID do banco de dados | Nível do pacote ou nível Foreach | Corda

• DatabaseName | Nível do pacote ou nível Foreach | Corda

• InstanceForDatasource | Nível do pacote | Corda

  • Destinado ao uso na guia Definir valores do trabalho do SQL Agent se for feito backup de mais de uma instância. Esta variável será usada para construir o ConnectionString.

• Cadeia de Conexão | Nível do pacote | Corda

  • Defina a propriedade EvaluateAsExpression como true.

  • Defina a propriedade Expression da seguinte maneira:Data Source="+ @[User::InstanceForDatasource] +";Provider=MSOLAP.4;Integrated Security=SSPI;

  • Observe que essa abordagem pode ser expandida para que toda a ConnectionString seja dinâmica e ditada pela etapa de trabalho do SQL Agent de chamada, se necessário.

• XMLAScript | Nível Package ou Foreach | Corda

Gerenciadores de conexão

Use uma conexão real em tempo de design para que os metadados funcionem bem.

A criação dos gerenciadores de conexões agora não é necessária, mas facilita mais tarde. Para cada tarefa no processo, você terá o gerenciador de conexões apropriado disponível no menu suspenso, sem a necessidade de criar nenhum em tempo real.

ADO.NET

1. Crie um novo gerenciador de conexões ADO.NET que use o Microsoft OLE DB Provider for Analysis Service
2. Usando o Editor de expressões de propriedade, defina a propriedade ConnectionString da seguinte maneira:[User::ConnectionString]

OLEDB

1. Crie um novo gerenciador de conexões OLEDB que use o Microsoft OLE DB Provider for Analysis Service
2. Usando o Editor de expressões de propriedade, defina a propriedade ConnectionString da seguinte maneira:[User::ConnectionString] + "Format=Tabular;"

Serviços de análise

1. Criar uma nova conexão do Analysis Services
2. Usando o Editor de expressões de propriedade, defina a propriedade ConnectionString da seguinte maneira:[User::ConnectionString] = "Impersonation Level=Impersonate;"

Crie o Contêiner Foreach

Aqui, você criará um Foreach com base no conjunto de linhas do esquema Catalogs. Isso nos dará o DatabaseNamepara cada banco de dados na instância e DatabaseNameserá colocado em sua variável correspondente.

Criar uma tarefa de script de execução

Defina o script para usar o Visual Basic.

Defina ReadOnlyVariables e ReadWriteVariables da seguinte maneira:

• ReadOnlyVariables:User::ConnectionString,User::DatabaseName,User::InstanceForDatasource
• ReadWriteVariables:User::BackupDir,User::DatabaseId,User::XMLAScript

Editar o roteiro

• Adicione uma referência ao assembly Analysis Management Objects (AMO).

  • Clique com o botão direito do mouse no nome do projeto (deve ser o item superior no Project Explorer na janela do Visual Studio que se abriu quando você clicou em Edit Script) e selecione Add Reference.

  • Adicione uma referência ao componente Analysis Services Objects. A dll para AMO deve estar localizada em <SQL Server InstallationDrive>:\Program Files\Microsoft SQL Server\<SQL Server Version>\SDK\Assemblies .

• No cabeçalho do script, adicione uma instrução Imports para usar o AMO:Imports Microsoft.AnalysisServices

• Substitua o conteúdo de Public Sub Main() pelo seguinte script:

  Public Sub Main()
      '
      Dim ASServer As New Microsoft.AnalysisServices.Server()
      Dim AsDatabase As New Microsoft.AnalysisServices.Database
      Dim ASConn As String = ""
      Dim ASDatabaseName As String = ""
      Dim ASDatabaseId As String = ""
  
      'Create a variable that uses the dynamic ConnectionString variable
      ASConn = Dts.Variables("ConnectionString").Value
  
      'What database are we working with in this iteration of the Foreach?
      ASDatabaseName = Dts.Variables("DatabaseName").Value
  
      'Use the Analysis Services AMO to get the DatabaseId for this DatabaseName.
      '   It is necessary to get the DatabaseId because the XMLA backup command requires the DatabaseId,
      '   and if at any point the database is renamed, the DatabaseId will differ from the DatabaseName.
      Try
          'Establish the connection to SSAS.
          ASServer.Connect(ASConn)
  
          'Get the database.
          AsDatabase = ASServer.Databases.FindByName(ASDatabaseName)
  
          '***ONLY IF the EstimatedSize > 0***. 
          ' --> If it is 0, it could be a corrupted database which causes the backup process to stop.
          ' --> In that case, we will leave the DatabaseId variable blank to be a visiblie indicator that the db is corrupt.
          '***ONLY IF the number of cubes > 0***.
          ' --> The databse could be corrupt or backup could hang if there are no cubes.
          If AsDatabase.EstimatedSize > 0 And AsDatabase.Cubes.Count > 0 Then
              'Get the DatabaseId
              'Retrieve the DatabaseId from the Databases collection.
              ASDatabaseId = ASServer.Databases.FindByName(ASDatabaseName).ID
          Else
              Dts.Events.FireWarning(0, "Verifying database", "The estimated size and/or number of cubes in the database does not meet the requirements. Estimated Size= " & AsDatabase.EstimatedSize & "; " & "Number of cubes= " & AsDatabase.Cubes.Count, String.Empty, 0)
          End If
      Catch ex As Exception
          'Couldn't connect. Do not error out because maybe the next iteration will succeed.
          Dts.Events.FireWarning(0, "Establishing SSAS Connection", "Unable to connect to the SSAS Server with ConnectionString= '" & ASConn & "'. Error: " & ex.ToString, String.Empty, 0)
      End Try
  
      'Print info for output while testing/debugging.
      Dts.Events.FireInformation(0, "Establishing SSAS Connection", "Connection established. ConnectionString= '" & ASConn & "'.", String.Empty, 0, True)
  
      'Put the DatabaseId into the SSIS variable. 
      Dts.Variables("DatabaseId").Value = ASDatabaseId
  
      'If we were able to retrieve the DatabaseId, then generate the backup directory and filename and the XMLA script.
      If ASDatabaseId <> "" Then
  
          'Print info for output while testing/debugging.
          Dts.Events.FireInformation(0, "Retrieving DatabaseId", "DatabaseId retrieved for " & ASDatabaseName & ". DatabaseId= '" & ASDatabaseId & "'.", String.Empty, 0, True)
  
          'Get timestamp for the backup file
          Dim timeStamp As String = Now.Year & _
                                      Right("0" & Now.Month, 2) & _
                                      Right("0" & Now.Day, 2) & _
                                      Right("0" & Now.Hour, 2) & _
                                      Right("0" & Now.Minute, 2) & _
                                      Right("0" & Now.Second, 2)
  
          'Get the filename and location for the backupfile
          Dim filePath As String = "\\<servershare>\" & Dts.Variables("ServerForDatasource").Value & "\" & ASDatabaseName
          Dim fileName As String = ASDatabaseName & "_" & timeStamp & ".abf"
  
          'Put the filepath into the SSIS variable
          Dts.Variables("BackupDir").Value = filePath
  
          'Print info for output while testing/debugging
          Dts.Events.FireInformation(0, "Retrieving full backup path", "BackupPath= '" & filePath & "\" & fileName & "'.", String.Empty, 0, True)
  
          'Build XMLA script
          Dim xmlaScript As String = ""
          xmlaScript = "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
                              "<Object><DatabaseID>" & ASDatabaseId & "</DatabaseID></Object>" & _
                              "<File>" & filePath & "\" & fileName & "</File>" & _
                          "</Backup>"
  
          'Put the XMLA script into the SSIS variable
          Dts.Variables("XMLAScript").Value = xmlaScript
  
          'Print info for output while testing/debugging.
          Dts.Events.FireInformation(0, "Generate XMLAScript", "XMLAScript= '" & xmlaScript & "'.", String.Empty, 0, True)
      Else
          'Print info for output while testing/debugging.
          'If DatabaseId is empty, we will not perform the rest of the tasks in the Foreach container.
          Dts.Events.FireWarning(0, "Retrieving DatabaseId", "Unable to retrieve the DatabaseId for Database= '" & ASDatabaseName & "'.", String.Empty, 0)
      End If
  
      ' Return success
      Dts.TaskResult = ScriptResults.Success
  End Sub
  

Criar uma tarefa do sistema de arquivos (para criar o diretório de backup)

É importante definir UseDirectoryIfExistspara trueevitar um erro se o diretório de backup já existir.

Edite a restrição de precedência entre a Tarefa Executar Script e a Tarefa do Sistema de Arquivos

Essa restrição de precedência manipulará um DatabaseId ausente. O DatabaseId estaria ausente se uma conexão com o servidor SSAS não pudesse ser estabelecida no vbscript ou se o banco de dados estivesse corrompido. Você não deseja fazer backup de um banco de dados corrompido. Isso interromperá o backup.

Exemplo de Expressão: `@[User::DatabaseId] !=""

Criar uma tarefa DDL de execução do Analysis Services

Seu fluxo de controle final deve ficar assim:

Criar o trabalho do SQL Agent

1. Adicione uma etapa para cada instância do SSAS da qual será feito backup.
2. Cada etapa deve ser configurada para executar o pacote SSIS.
3. Para cada etapa, clique na guia Definir valores e defina o valor de InstanceForDatasourcecomo o nome da instância da etapa.
   • Exemplo de sintaxe para Property Path:\Package.Variables[User::InstanceForDatasource].Properties[Value]
   • ( Mais sobre como usar a guia Definir valores )

Notas:

• Para tornar todo o diretório de backup dinâmico, bastaria adicionar outra variável para isso e contabilizar no vbscript.

• Novamente, se necessário, toda a variável de cadeia de conexão pode ser definida pelo trabalho do SQL Agent em vez de apenas o nome da instância.

Outra opção seria Powershell SQL Server Analysis Services Backup no Powershell

Eu consegui isso sem nenhum SSIS. Em vez disso, usei um servidor vinculado. Levei algum tempo, mas ainda acredito que é a melhor maneira de fazer backup de bancos de dados SSAS.

Você pode ver como fiz neste link .

Também apago arquivos de backup antigos, como você pode ver neste link .