“You’re only as good as your last restore”

I’ve no idea who originally said that – but it probably qualifies as DBA folklore. I blogged recently about backups in SSAS so to close the loop here are the basics of restoring SSAS databases.

In the GUI:

Connect to the SSAS service with SSMS, right click the database node in object explorer and select restore. The following dialog appears:

Browse to a valid SSAS backup file.

Select a database to restore over or type a new name for a new database.

Optionally select an alternative storage location for database. (The folders that appear in the browse dialog are defined in the advanced server propertyAllowedBrowsingFolders. This is a pipe (|) separated list of local folder paths. Adding a folder to this list also gives access to all of its subfolders.)

If you are choosing to restore to an existing database you must enable the allow database overwrite option.

Choose whether to restore security metadata with the backup, the options are restore all, or to skip role membership.

If the backup was encrypted then provide the password.

The partitions page allows you to have control over the restore locations of local or remote partitions.

Using XMLA command scripts:

As with the backups SSMS builds an XMLA command script behind the scenes of the restore dialog. This script is available via the script dropdown at the top left of the dialog.

The following script restores the backup.abf backup into the pre-existing SSASDatabase database, overwriting all security metadata, and without encryption:

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\MSSQL\SQLDataDumps\backup.abf</File>
<DatabaseName>SSASDatabase</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
</Restore>

The following script restores the backup.abf to the new SSASDatabase without restoring any of the security metadata:

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\MSSQL\SQLDataDumps\backup.abf</File>
<DatabaseName>SSASdatabase</DatabaseName>
<Security>IgnoreSecurity</Security>
</Restore>

Finally this script will restore the backup.abf file to the pre-existing database SSASDatabase to a non-default location without restoring role membership metadata.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\MSSQL\SQLDataDumps\backup.abf</File>
<DatabaseName>SSASDatabase</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Security>SkipMembership</Security>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Log\</DbStorageLocation>
</Restore>