Introduction
One of the most important responsibilities of a database administrator is to make sure that all the databases are backed up across environments managed by them. In my earlier article titled Importance of Database Backups and Recovery Plan I discussed the importance of a good database backup and recovery plan for all the user and system databases. In this article we will take a look at the steps which a database administrator needs to follow to backup and restore an analysis services database.
Backup an Analysis Services Database Using SQL Server Management Studio
Database Administrator can backup analysis services database either by using SQL Server Management Studio (SSMS) or by using XMLA command.
Let us start by backing up Adventure Works DW 2008 analysis server database which is the sample database available in SQL Server 2008.
1. Connect to Analysis Services Instance using SQL Server Management Studio.
2. Expand Databases, right click the Analysis Services Databases which you want to backup and select Back Up… option from the drop down as shown in the snippet below.
3. In the Backup Database Window, specify the analysis services database backup file location and choose any of the available options:
a) Allow file overwrite: – choose this option if you want the backup file to be overwritten.
b) Apply compression: – choose this option if you want to apply compression.
c) Encrypt backup file: – choose this option if you want to encrypt the analysis services backup file.
4. Once all the options are selected click the Script drop down in Backup Database window and choose Script Action to New Query Window option as shown in the snippet below to generate an XMLA script which can be used to backup the analysis service database.
XMLA Command Generated to Backup the Analysis Services Database
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
</Object>
<File>D:SSASBackupsAdventure Works DW 2008.abf</File>
</Backup>
5. Finally, click OK to backup the Adventure Works DW 2008 analysis services database.
Scheduling Analysis Services Database Backups
Database administrator can automate the backup of analysis services database using a SQL Server Agent Job. Follow the below steps to create an SQL Server Agent Job.
1. Connect to the SQL Server Database Engine Using SSMS.
2. Expand the SQL Server Agent, right click Jobs and choose New Job… option from the drop down list as shown in the below snippet.
3. On t5he General Page, provide an appropriate name for the SQL Server Agent Job for example Analysis Services Database Backups. Next, click on the Steps link from the right panel.
4. In the Steps Page click the New…. button to open up New Job Step window.
5. In the New Job Step window, choose SQL Server Analysis Services Command as Type and also provide the Analysis Services Instance Name in the Server textbox.
6. Copy and Paste the XMLA command which was generated earlier under the command windows as shown in the below snippet. Click OK to create the ‘Backup Analysis Services Database – Adventure Works DW 2008’ Job step and to return to the Steps Page.
7. Next , to schedule the SQL Server Agent Job to run once a day at 12 AM. Click Schedules in the right side panel.
8. In the Schedule Page click the New… button to open up the New Job Schedule window.
9. In the New Job Schedule window, provide an appropriate name and choose the frequency ( daily in this case) as shown in the below snippet and click OK to save the schedule and to return to parent window.
10. Finally, click OK in the New Job window to create an analysis services backup job.
Restore an Analysis Services Database
The DBA can restore an analysis services database either by using SSMS or by using an Analysis Services XMLA Query.
Let us restore Adventure Works DW 2008 analysis server database which we had backed up earlier.
1. Connect to Analysis Services Instance using SSMS .
2. Right click the Databases node and select the Restore… option from the drop down list as shown in the below snippet.
