How to schedule the analysis database backup(Archive Database)

Question

How to schedule the analysis database backup(Archive Database)

Mohammad Sufian

Ten Centuries

Points: 1292
More actions
June 23, 2005 at 9:28 am

#164953

Hi,
I had 6 cubes and they are too big in size and it take time to backup them
How can we schedule them on nightly bases.I tired msmdarch /a but it is not working with xp_cmdshell.
the if i execute the same command from command prompt then it workes fine.
Can any body help me for the better way, or why it is not working with xp_cmd shell.
from
sufian

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RGHippen SSC Enthusiast Points: 107 More actions · Answer 1

Please reference my [Backup all Analysis Services databases to drive] stored procedure code under [Resources > Scripts > Backup / Restore] from the sqlservercentral.com main page.

We have this Analysis Services backup stored procedure scheduled to run daily in a SQL job.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

re: the msmdarch not working, you need to check which account is being used to run the sql agent service and whether or not they're a member of the olap administrators group.

Technically, if you used 'standard' backup software and backed up the olap data directory and also the repository (which should be in mssql) then you can restore using these backups.

Steve.

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 3

hi,

As i mentioned before this is the error i am getting can u pls answer for this why this error is diplayed.

I dont need any restore script.

'msmdarch' is not recognized as an internal or external command,

operable program or batch file.

NULL

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

have you included or referenced the pth correctly within your xp_commandshell call? mormally getting 'not recognised' error means that either you haven't included the path to the executable or the path isn't in the current users '%path%' system variable (either one will cause this type of error).

Steve.

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 5

Thanx Steve

I think and will try to give the whole path .

But i think MS-SQL is much intelligent because when microsoft creates a extended stored procedure it also creates the refrence paths for dll.

from

sufian

imtiazu Grasshopper Points: 13 More actions · Answer 6

The SQL script looks great... If only i knew the "Repository" parameter to pass in.

Can someone advise what this is please.

Thanks

Immy

imtiazu Grasshopper Points: 13 More actions · Answer 7

So I found my access rep.DB but i guess at some point this should be merged into being an SQL DB in order for this script to work?

I have the latest SP's installed for both AS and SQL (Sp4) but i dont have any extra databases within my SQL server...

Immy

imtiazu Grasshopper Points: 13 More actions · Answer 8

sorry.... as every minute passes, i find more out about this.

It looks like i can edit the rep db settings on the AS server. I assume i can just change these settings in the connection string and create myself a SQL DB to hold this info?

Sorry for all the messages, i am knew to this!

Immy

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

Immy,

I'm guessing you're referring to having your repository in MSAccess. To get it into MSSQL, right click on the AS server within Analysis Manager and select the Migrate Repository option. It's often best to create a ASRepository database within MSSQL first rather than letting the system create it within msdb (the default). After doing this, the connection string should reflect the fact that your repository is now in MSSQL.

Steve.

Tho Nguyen Newbie Points: 8 More actions · Answer 10

What should I pass for the value of @RepositoryDB?

Thanks a bunch.