2

I have a procedure to backup SSAS Databases that is working successfully.

I also delete the SSAS backup files that are older than 2 days.

What I am looking forward to implement is:

1) How to verify my SSAS backups? (restore verify only)

2) How to run a DBCC CheckDB in SSAS? (database integrity)

But I have not found any substantial documentation for these.

Any suggestions?

I know about the right-click/ Validate database on Visual Studio but I would like an automated process, not manually clicking.

enter image description here

2

I found a post by Darren Gosbell, a SQL Server MVP (at the time at least). His post can be found here:

http://geekswithblogs.net/darrengosbell/archive/2008/09/13/ssas-verifying-backups.aspx

He points out that you cannot run this from your desktop, but you can run it on the server to do the verification. The instructions from 2008 are:

Navigate to:   <Program Files>\Microsoft SQL Server\MSAS10.SQL08\OLAP\bin 

Then type:  msmdsrv /? 

Look at the commands available with msmdsrv and see what you want to do. (I am away from a SSAS installation at the moment, so I cannot verify, but it should be worth checking out.)

EDIT:
2) How to run a DBCC CheckDB in SSAS? (database integrity)

Starting with SQL 2016 we have a DBCC CHECKDB command for SSAS for Dimensional as well as Tabular

DBCC provides on-demand database validation for Multidimensional and Tabular databases on an Analysis Services instance. You can execute DBCC in an MDX or XMLA query window in SQL Server Management Studio (SSMS) and trace the DBCC output in either SQL Server Profiler or xEvent sessions in SSMS. The command takes an object definition and returns either an empty result set or detailed error information if the object is corrupted. In this article, you’ll learn how to run the command, interpret results, and address any problems that arise.

  • @Tom V - Thanks for the preview of SQL Server 2016's DBCC CHECKDB for SSAS. (It is about time). – RLF Apr 5 '16 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.