SSAS DB Recovery

Copper Contributor

Hi. I sure how this is an easy one. I am fairly familiar with SQL basics but very little regarding analysis services - essentially successfully installed it a few times, but nothing more.


I am supporting a SQL 2016 server with Analysis Services installed. I received a ticket indicating that the analysis services service would not start. Doing some digging I discovered is the folder there was a single dbname.o.xml (or something very similarly named xml file), that had a file size of zero and was in fact  empty. Some googling indicated I should delete this file. I actually copied it off and renamed the extension of the original file to txt. I was then able to successfully start the analysis services service.


Later in the afternoon I was notified that the "database was missing" and I was asked to "restore" it.


I saw that the database folder was in that path. So my thinking it probably had to do with that file I renamed..... Since that files was empty, I went to the previous nights backup. I verified that the file had what looked to be valid contents and restored that single file. The analysis services database still did not show - so, I figured restarting the analysis services service my be a good thing to try.


I restarted the service successfully and still no database in SSMS. Even weirder, when I looked back at the file system, I saw that the file I restored was no longer there. I actually restored the file a number of times and restarted the service and verified that when the service starts it deletes that xml file.


My next course of action was to restore that xml file as well as the database folder within the ...database folder from the previous nights backup.


Restarted the analysis services service and still no luck - even worse, not only did the start of the service delete the xml file, it also deleted that database's folder!


I really need to get this database restored.


Now this is where the story takes a turn for the ugly. Due to a problem with the VSS driver installed on the system, that application that was supposed to take application aware backups (VEEAM) has not been working for some time, so simply restoring the database with that system is not an option (file system restore is possible though). Because it was backed up with VEEAM there were no native SQL backups performed. Even worse, logs have never been truncated.... These are very small databases (I've been told)....


Anyways, I understand there are a number of things I need to address with this server but my immediate need is to get this one database back up and running. I have one idea that may not be feasible..... Any help would be greatly appreciated!


Thank you and be safe all!



0 Replies