SSAS: How to Schedule and Automate backups of all the catalogs within the Server Instance?WT.mc_id=DP-MVP-5003040

Article
1/17/2024

Introduction

With the course of time, you might need to backup all the SSAS databases within a server instance automatically and schedule to backup these databases monthly or according to your specific time set.

Maybe there are a lot of alternative methods to perform this task and many solutions. We have researched a lot regarding backing up all the SSAS databases within a particular server instance dynamically.

Previously we have shared "How to schedule and automate backup all the SQL databases within a server instance": http://bidn.com/blogs/Anil/ssas/1591/how-to-schedule-and-automate-backups-of-all-sql-server-databases-in-sql-server

Here we are going to show how to obtain all the cube databases backup automatically along with schedule time set.

These are the following steps you should have to follow.

1. Adding a linked server in SSMS.

You can simply add a linked server within a SSMS by using a script as

--Adding a linked server
 EXEC master.dbo.sp_addlinkedserver
   @server = N'SSAS_Backup'
 , @srvproduct=N'MSOLAP'
 , @provider=N'MSOLAP'
  , @datasrc=N'ANILMAHARJAN'  /* <<< My Analysis Services server name */
  /* <<< My Analysis Services database   name */
  go
   --Setup security as per your environment requirements.
EXEC master.dbo.sp_addlinkedsrvlogin
       
  @rmtsrvname=N'SSAS_Backup'
 , @useself=N'False'
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
go

2. Make a SSIS package.

You can simply create the SSIS Package as shown in below.

2.1.Table

Create the table in SSMS of the output of all the catalog names within a server by executing the query into the DMV in SSAS from SSMS.

You can use the following script in order to find all the current catalog names within a server. Also here you can create a log file to know the entire backup process by setting within a job agent.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CubeBackupInfo')
      DROP TABLE   [CubeBackupInfo]
SELECT  * into CubeBackupInfo  FROM OPENQUERY(SSAS_Backup,'select * from $system.dbschema_catalogs')

2.2. Read the total number of database backups

Read the total number of database backups to be made by reading the max count of catalog names from the table created above.

2.3. XMLA

Read the backup XMLA within a variable into SSIS from a particular location.i.e backup XMLA that generated manually and we can also generate it by using a some C# or VB.NET code embedded within SSIS.

Here is a Backup XMLA script:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>CatalogName</DatabaseID>
  </Object>
  <File>CubeFileName.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

2.4. Max loop

Now, within a for loop, set the max loop to the variable as @Max_No_Backup i.e max no of backup to be made.

2.5. Catalog names

Read the catalog names of databases within a server one by one by using a table that we created before from CubeBackupInfo.

2.6 XMLA script

Modify the XMLA script using `Script Task' within where we have used C# code in order to modify the XMLA and generate the modified XMLA for each catalog name one by one.

2.7. Backup

Backup the cube database within a server into a default location of SSAS backup.

stores according to the `catalog name' along with the `system date' in order to know the particular backup date. as i.e. TestCube-03-11-2011.abf
also catalog names can overwrite it if it exists already.

i.e. : C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup

3. Make a SQL Server Job Agent.

3.1. Job

You can easily make a job in SQL by following this link: http://www.sqlservercentral.com/articles/Stairway+Series/72267/

and Running and Scheduling SSIS Packages by following this link: http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx

3.2. Schedule

Make a schedule to backup all the catalogs/databases within a particular server. i.e. monthly or weekly according to your specific time set.

Share via