Creating Backup, Restore, attach and detach cube in SSAS

In general term, a backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event.

Backups have two distinct purposes.

Ø  The primary purpose is to recover data after its loss, be it by data deletion or corruption.  Data loss can be a common experience of computer users.

Ø  The secondary purpose of backups is to recover data from an earlier time, according to a user-defined data retention policy, typically configured within a backup application for how long copies of data are required. Though backups popularly represent a simple form of disaster recovery, and should be part of a disaster recovery plan, by themselves, backups should not alone be considered disaster recovery.

Backup is very important activities of the databases. It is also very important in cube also. In this article I am going to explain step by step to creating the cube backup.

                       

Open SQL server Management studio.

Connect with Analysis services

Explore Database. You will get the list of cube which has been deployed on this Analysis services.

Right click on the cube for which you want to create a back up.

Click on backup.

A: - Click on browse where you want to store the backup of the cube. Select the paths where you want to keep the cube back up and write the cube backup name. Click ok.

B: - There are three type of option while creating the back up of the cube.

                               

Ø  Allow  file overwrite ( if you check this option it will be overwrite if any cube back up if exist on the same location).

Ø  Apply compression ( if you check this option it will be compress the cube back up file)

Ø  Encrypt back up file (if you check this option it will protect you back up with password protection).

C: - If you have checked encrypt back up file option then it will be enabling. You need to provide password. Whenever you restore this back up you need to provide that password that time.

D: - If you want to generate the script of the back file then click on the script you will get the following script.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Object>     <DatabaseID>AdvenchaeWarks</DatabaseID>   </Object>   <File>E:\Cube BackUp\BackUpCube.abf</File>   <Password>****</Password> </Backup>

E: - Click OK.

Now back up has been created successfully.

Restore the backup cube file on the Analysis services

Right click on database

Click on Restore

A: - Click on the browser button and select the cube back up file. Click ok.

B: - Write Restore database name and select the path where you want to store .db file. Click OK.

C: - select the option if you want to over write the existing database the select it .

D: - while creating back up you used password for encrypt the backup file that password you need to provide here. If you did not provide password at backup creation type then keep it blank.

E: - If you want to generate script of restoring database click on script. You will get following script.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <File>E:\Cube BackUp\BackUpCube.abf</File>   <DatabaseName>TestCubeBackUp</DatabaseName>   <AllowOverwrite>true</AllowOverwrite>   <Password>****</Password>   <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> E:\Cube BackUp\</DbStorageLocation> </Restore>

F: - Click Ok .Now back up has been restored.

Refresh the database you will get the new database.

Detach the cube

Right click on the cube database which one you want to detach.

Provide the password and click ok.

Cube database has been detached.  Refresh the database.

                                

Attached the cube database

Right click on the database and select attach

Select the .db file

Click ok and write the password.

If you want to generate the script then click on the script

<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">   <Folder>E:\Cube BackUp\TestCubeBackUp.7.db\</Folder>   <ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100"> ReadWrite</ReadWriteMode>   <Password>****</Password> </Attach>

Click ok. Now cube database has been attached. Refresh the database you will that cube database.

                             

     

Hope this article is helping you. If you have any query  feel free to disturb me.