SQL Server MS Analysis Services Backup

[katekis]

I am a DBA on SQL Server 2005 and SQL Server 2000 environment. I am new to Analysis Services, most my DBA experiences is from SQL Server 2000.

I run daily Backups on all databases in SQL Servers but I do not run any Backups on Databases in Analysis Server.

Should I be running a Daily backup of Analysis Server?

I am applying a update to one of our Analysis Service Databases, I am going through each of the databases and running a backup. Is this the only time I need to run a backup or should I be running a backup daily? What are the risks of not running a daily backup on Analysis Service Databases?

 

[mrdenny]

You should be running a backup on a regular basis just like with any other database.

If you don't backup the SSAS database you run the risk of loosing the structure. Also you'll increase the recovery time after rebuilding the database as you'll need to import all the data and reprocess all of it.

This site covers the basics of SSAS backups.

http://geekswithblogs.net/darrengosbell/archive/2005/10/02/55755.aspx

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[katekis]

How do you schedule a Analysis Server Backup?

With Microsoft SQL Server Management Studio if I right+click on the database and select backup I can backup the database but I am not able to schedule a backup.

I normally use Quest Litespeed for all my database backups, it does not look like this software has an ability to run or schedule Analysis Service Database Backup.

 

[mrdenny]

No lightspeed doesn't have the ability to backup an SSAS database.

You can schedule the SQL Agent job run a job with a OS Command, or use the task scheduler. The link I posted has info on how to backup the SSAS database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[katekis]

Where is the link posted with info on how to backup the SSAS database? I don't know what the OS Command should be to setup a scheduled Agent Job.

 

[mrdenny]

The link is above in a prior answer. There's no command line tool to create a SQL Agent job. You use SQL Server Management Studio to create the SQL Agent job.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog