Back to the Basics: Restoring an Analysis Services Database
Posted by decipherinfosys on February 24, 2011
Not every DBA or Database Developer has had experience with Analysis Services so it did not come as a surprise when a DBA at a client site approached us for quick help in restoring the Analysis Services Database on-to the development environment. In our back to the basics series of posts, we will be covering this topic today.
Let’s use SSMS to connect to Analysis Services instance first. In case you have never done that before, when you connect, select “Analysis Services” from the drop-down in the Server Type, the proper Server Name (your instance) and the authentication and you will be connected to the Analysis Services instance in which we will restore the back-up of the Analysis Services Database:
Once connected, right click on “Databases” and select “Restore”:
You will be presented with a very simple “Restore Database” window – most of the items are self explanatory – you would browse to select the location of your backup file (we selected the production backup file in the example below):
Once that is done, you need to then give a name for the restored database (in our example, we are calling it PRODCOPY), select a storage location using the browse button, Allow for the database overwrite if it already exists on the instance, choose to overwrite the security information and if the backup was encrypted, provide the password so that you can move ahead with the restore process:
Once that is done, you would be able to restore the database on that instance and then do configuration changes, data source changes etc.
Another way of doing the restore: In case you are not a GUI kind of guy and like to us scripts, you can also use XMLA. You can read up on the command here. You can invoke XMLA by right clicking on the Analysis Services instance and choosing “New Query” and XMLA. The command parameters are the same as you saw in the GUI option above so add the values appropriately and then execute it in order to complete the restore process.
- XMLA – here.
- Backup and Restore Analysis Services Databases – article on SQLServerPerformance.com by Ashish Kumar Mehta – here.
- MSDN post on backing up and restoring Analysis Services Database – here.
- Database Journal Article on restoring Analysis Services Database using Windows Powershell and SQL Server 2008 AMO – here.