1.  Create linked server to Analysis Services Server
  2.  Create a T-SQL script to gather all CUBE(s) and generate XMLA script. There are two ways to gather CUBE(s)
    1. Use OPENQUERY to gather information via the linked server which we have just created
    2. Use a text file and BULK INSERT statement. The txt file contains all CUBE(s) nameDECLARE @BackupFolder nvarchar(100),
      @BackupFileName nvarchar(100),
      @DatabaseCubeName nvarchar(50),
      @Time nvarchar(30),
      @XMLA nvarchar(4000),
      @SQLScript nvarchar(500),
      @PathOfCubeList nvarchar(100),
      @XMLAFile nvarchar(100);– Set BackupFolder
      SET @BackupFolder = ‘G:\SQLServer\OLAP\Backup’
      –SET @PathOfCubeList = ‘D:\DungDT\CubeArchive’
      SET @Time=CONVERT(Varchar(10),GETDATE(),120)+
      +’_’+ CASE WHEN DATEPART(HH,GETDATE()) >=10 THEN CONVERT(varchar(2),DATEPART(HH,GETDATE()))
      ELSE ‘0’+CONVERT(varchar(2),DATEPART(HH,GETDATE())) END
      +’_’+ CASE WHEN DATEPART(MI,GETDATE()) >=10 THEN CONVERT(varchar(2),DATEPART(MI,GETDATE()))
      ELSE ‘0’+CONVERT(varchar(2),DATEPART(MI,GETDATE())) END
      +’_’+ CASE WHEN DATEPART(SS,GETDATE()) >=10 THEN CONVERT(varchar(3),DATEPART(SS,GETDATE()))
      ELSE ‘0’+CONVERT(varchar(3),DATEPART(SS,GETDATE())) END

      SET @PathOfCubeList = N’D:\DungDT\CubeArchive\DATABASE_CUBE.txt’

      IF OBJECT_ID(‘tempdb..#DatabaseCubes’) IS NOT NULL
      DROP TABLE #DatabaseCubes
      CREATE TABLE #DatabaseCubes(DatabaseCubeName nvarchar(50))

      SET @SQLScript =’BULK INSERT #DatabaseCubes
      FROM N”’+@PathOfCubeList+”’
      WITH(rowterminator=”\n”,firstrow=1)’

      EXEC (@SQLScript)

      DECLARE c CURSOR FOR
      SELECT DatabaseCubeName
      FROM #DatabaseCubes
      –SELECT [CATALOG_NAME]
      –FROM OPENQUERY([SSASSERVER],’SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS’) AS c;

      OPEN c
      FETCH NEXT FROM c INTO @DatabaseCubeName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF @DatabaseCubeName LIKE ‘%_SMI_CUBE’
      BEGIN
      SET @BackupFileName = @BackupFolder +’\’+@DatabaseCubeName+’_’+@Time+’.abf’
      SET @XMLAFile = @BackupFolder +’\’+@DatabaseCubeName+’_’+@Time+’.xmla’
      SET @XMLA = N'<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

      ‘+@DatabaseCubeName+’

      <File>’+@BackupFileName+'</File>
      <AllowOverwrite>true</AllowOverwrite>
      <ApplyCompression>true</ApplyCompression >
      </Backup>’ + CHAR(13)+CHAR(10);

      BEGIN TRY
      — Create XMLA file
      –EXEC xp_cmdshell @SQLScript
      EXEC (@XMLA) AT [SSASSERVER];
      END TRY
      BEGIN CATCH
      SELECT
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_PROCEDURE() AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_MESSAGE() AS ErrorMessage
      END CATCH
      END
      FETCH NEXT FROM c INTO @DatabaseCubeName
      END
      CLOSE c
      DEALLOCATE c

  3. Create a job to run the above script. Because SQL Agent Job uses Agent Service Account to run so we need to add this account on Analysis Services Server as Administrators Role to allow it to have the backup cube permission.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s