Backup and Restore of Analysis Services Database - gotchas
Since the evolution of SQL Server 2000 version the backup and restore methods have been enhanced for better performance to provide a better option for the DBA, as usual the backup and restore operations are an integral part of the Analysis Services infrastructure. The same methods/process has been completely rebuilt in SQL Server 2005 relational engine and Analysis Services.
Here is the glimpse of diagram about how the backup and restore works out within Analysis Services engine (from Technet):
The complete operation of backup/restore is carried out using XML against the engine, like once the backup command is issued against Analysis Services databases then it determines which files need to be packages in the single backup file. It will take care of the consistency of the database at the time of the backup and restore. It will make sure that all the objects in the backup file are of the same version.
Better to use the GUI provided within the SSMS backup UI, use as follows:
1. |
Start SQL Server Management Studio. |
2. |
Open the connection to the Analysis Services server. |
3. |
Right-click the database to back up and select Back up. |
The same operation can be achieved by running XML (DDL) code as follows:
<Backup>
<object>object_ref</object>
<File>BackupFile</File>
[<AllowOverwrite>true/false</AllowOverwrite>]
[<BackupRemotePartitions>true/false</BackupRemotePartitions >]
[<Locations>
[<Location>
<File>BackupFile</File>
<DataSourceID>Datasource ID</DataSourceID>
</Location >]
</Locations>]
[<ApplyCompression>true/false</ApplyCompression >]
[<Password>Password</Password>]
</Backup>
You might be thinking about compression of these Analysis Services databases, such a compression option is available with backup command. But I wouldn't suggest to use this option as the files within the partition these files (AS) are stored in compressed format. The default method is to compress the backup so do not use this option when performing with a script. Do not use any PASSWORD option for the backup as it is best to keep the backup file in a secred location, as it will be single-point-of-failure if you or whoever created the backup forgots the password.
If you going to build a strategy for your OLAP platform then better to have better understanding on storage modes of Analysis Services objects, as all the partitions and dimensions within the OLAP database use a particular storage mode that works quite opposite to ROLAP and HOLAP. If all the partitions and dimensions in your database use OLAP storage mode, as opposed to ROLAP and HOLAP, the data that you are importing into the Analysis Services database changes only at the time of processing. So always perform the backup operations once the processing of such cubes/dimensons are finished.
Similar to Relational Database methods make sure to conserve backup space and the time required to back up, you might consider backing up just the Create statement for your database. For a full backup that includes source data, you have to back up the database which contains detail data. In general, Analysis Services backups contain metadata and a subset of source data and/or aggregations, not the complete underlying detail data. However, if all objects are MOLAP, the backup contains metadata and source data.
An extract from BOL states on the storage modes how the OLAP uses the partitions and dimensions:
| Storage Mode |
Contents of backup file |
|
Multidimensional OLAP (MOLAP) partitions and dimensions |
Metadata, source data, and aggregations |
|
HOLAP partitions and dimensions |
Metadata and aggregations |
|
ROLAP partitions and dimensions |
Metadata |
.
Make sure to check the updated contents of Books Online for Analysis Services for more examples on the Analysis Services and backup strategies.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.