Backup

Backup all SSAS databases automatically and schedually.

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

May be there are a lot of alternative methods to perform this task and many solution’s.

But I have researched a lot regarding to backup all the SSAS databases within a particular server instance.

In my last blog post I have shared “How to schedule and dynamically backup all the SQL databases within a server instance”.
Here ,I am 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. Create the table in SSMS of the output of all the catalog name within a server given by frying the query into the DMV in SSAS from SSMS.
You can use the following script in order to find all the current catalogs name within a server.
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 no of database backup to be made by reading the max count of catalog name from the table just we created above.

2.3. 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 an XMLA script for backup
2.4. 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. Here read the catalog name of database with in a server one by one by using a table that we created before from CubeBackupInfo.
2.6 Modify the XMLA script using ‘Script Task’ within where I 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 all the cube database with in a server into a default location of SSAS backup.
-stores acc 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 name can overwrite it if it exist already.
i.e. : C:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPBackup

3.Make a SQL Server Job agent.
3.1. 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 the link as http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx
4.Make schedule to backup all the catalogs/ database within a particular server. i.e. monthly or weekly according to your specific time set.

Hope this will help for someone , also I am thinking to post another blog about ‘Dynamic partition within a cube’ using a similar approach where I have researched a lot in this topic too.

Thanks,
Anil Maharjan

Backup and Restore Cube at a time using SSIS Package.

Hello all,

Firstly, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

Also, this may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of method to perform this task and many solution’s.

While working I have to take a Backup of Cube from one database and Restore it into another database within a same machine.So,for this i usually do manually by right clicking the Backup and Restore it to another database but every time say development cube changes frequently and i have to take backup and then again restore it into production cube.

Doing manually seems little lazy,so i think of doing it dynamically or say by just a click so that.

For this I use a SSIS package where we can use the “Analysis Services Execute DDL Task ” from toolbox and by editing it, we can set/configure the values as:

Also, i forgot to mention that for the restore and backup XMLA script ,we have to right click on the database and click restore/backup and then click the script button so that it will generate the XMLA script for restore and backup to particular database.After generating the XMLA,we can directly put into the “Analysis Services Execute DDL Task” by configuring the “SourceType” :Direct input and SourceDirect : XMLA script (i.e of backup / Restore).

But here i use the file connection for pointing the XMLA script from particular location so that we can execute this for different database by only changing the XMLA file.

This may be simple but i think it may work out for someone .

Thanks,

Anil Maharjan