Backing Up and Restoring an Analysis Services Database

Backing up lets administrators save a particular state of a Microsoft SQL Server 2005 Analysis Services (SSAS) database and its objects. Restoring lets administrators restore an Analysis Services database to a previous state. The reasons for doing backups include data recovery and preparation for audits. If you do not already have a backup plan and your data is valuable, you should design and implement plan as soon as possible. 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.

One clear benefit of automating backup is that the data snapshot will always be as up-to-date as the automated frequency of backup specifies. Automated schedulers ensure that backups are not forgotten. Restoring a database can also be automated, and can be a good way to replicate data, but be sure to back up the encryption key file on the instance you replicate to. The synchronization feature is dedicated to replication of Analysis Services databases, but only for the data that is out of date. All of the features mentioned here can be implemented through the user interface, by way of XML/A commands or programmatically run through AMO. For more information about backup strategies, see Backup Strategies with SQL Server 2005 Analysis Services.

Backing Up an Analysis Services Database

In SQL Server 2005 Analysis Services, administrators can back up an Analysis Services database to a single operating system file, regardless of size of the database. If the Analysis Services database contains remote partitions, the remote partitions can also be backed up. When you back up a database with remote partitions, all the remote partitions on each remote server are backed up to a single file on each of the remote servers. Therefore, if you want to create those remote backups off their respective host computers, you will have to manually copy those files to the designated storage areas.

Backing up an Analysis Services database produces a backup file whose contents vary depending upon the storage mode used by the database objects. This difference in backup content results from the fact that each storage mode actually stores a different set of information within an Analysis Services database. For example, hybrid OLAP (HOLAP) partitions and dimensions store aggregations and metadata in the Analysis Services database, while relational OLAP (ROLAP) partitions and dimensions only store metadata in the Analysis Services database. Because the actual contents of an Analysis Services database vary based on the storage mode of each partition, the contents of the backup file also vary. The following table associates the contents of the backup file to the storage mode used by the objects.

When you back up an Analysis Services database, you can choose from the following options:

• Whether to compress all database backups. The default is to compress backups.
• Whether to encrypt the contents of the backup files and require a password before the file can be unencrypted and restored. By default, the backed up data is not encrypted.

  Security Note:
  The user running the backup command must have permission to write to the specified backup location for each of the backup files, and must either be a member of the Analysis Services server role or a member of a database role with Full Control (Administrator) permissions on the database that is being backed up.

For more information about backing up an Analysis Services database, see Backup Options.

Restoring an Analysis Services Database

In SQL Server 2005 Analysis Services, administrators can restore an Analysis Services database from one or more backup files.

During restoration, you have the following options:

• You can restore the database using the original database name, or you can specify a new database name.
• You can overwrite an existing database. If you choose to overwrite the database, you must expressly specify that you want to overwrite the existing database.
• You can choose whether to restore existing security information or skip security membership information.
• You can choose to have the restore command change the restoration folder for each partition being restored. Local partitions can be restored to any folder location that is local to the Analysis Services instance to which the database is being restored. Remote partitions can be restored to any folder on any server, other than the local server; remote partitions cannot become local.

  Security Note:
  The user running the restore command must have permission to read each of the backup files, and must be a member of the Analysis Services server role on the Analysis Services server to which the database is being restored.

For more information about restoring an Analysis Services database, see Restore Options.

Security Considerations for Back Up and Restore

The security considerations described below will enable you to recover your data in the event of system failure, while minimizing the risk of unauthorized personnel gaining access to your data.

Physical Protection

To help protect your backup disk files, we recommend that you back up only to disk files that are protected by restrictive access control lists (ACLs). The ACLs should be set on the directory root under which the backups are created. In some cases, you might want to further protect disk-based backups by using the NTFS encrypting file system (EFS).

In addition, we recommend that you store a copy of your backup files in a secure, offsite location.

Back Up Password Protection

Microsoft SQL Server 2005 Analysis Services (SSAS) supports password protection for back up files. An Analysis Services backup file without password protection will allow any user with access to the backup file to restore the entire Analysis Services database and read its content.

When a password is given to the backup file, the password should meet the minimum requirements of a secure, strong password in order to effectively protect the data in the backup file.

A strong password has the following characteristics:

• Is a minimum of 15 characters long. Is a combination of letters, numbers, and symbols. Is not found in a dictionary, or cannot be read in any language in a meaningful way. For example, a weak password might be TodayIs2008-10-01. A strong, secure password cannot contain a command or any name of any kind.
• Is significantly different from previous passwords. For example, the next password after EfMaMjJaSoNd$2008 should not be followed by EfMaMjJaSoNd$2009.
• Is changed regularly. Each backup file has a different password.
• Is not stored or posted in unsecured places.

See Also

Other Resources

Administering Analysis Services
Backing Up and Restoring Databases
Backing Up, Restoring, and Synchronizing Databases (XMLA)

Help and Information

Getting SQL Server 2005 Assistance

Change History