Backup Options

There are many ways to back up your Microsoft SQL Server Analysis Services databases and they all require that you have server administrator and database administrator permissions. You can open the Backup dialog box in SQL Server Management Studio, select the appropriate options configuration, and then run the backup from the dialog box itself. Or, you can create a script using the settings already specified in the file; the script can then be saved and run as frequently as required.

Backup and Synchronize

If the database is located on a remote instance of Analysis Services, you can use the synchronization feature to back up the database to the local instance. Development builds of a database can be moved into production in this manner. You can also use the conventional, file based, backup and restore to move the development build into production, but synchronization provides additional functionality. For example, you can have security settings which are different for the development and production computers; synchronization will provide you the option to maintain those settings and synchronize all objects other than roles. Also, synchronization typically does an incremental update of those objects which are different for the source and destination computers. This kind of incremental backup is not available using the backup/restore feature. For more information, see Synchronizing Analysis Services Databases and How to: Synchronize an Analysis Services Database.

Security noteSecurity Note

For each backup file, the user who runs the backup command must have permission to write to the backup location specified for each file. Also, the user must have one of the following roles: a member of a server role for the Analysis Services instance, or a member of a database role with Full Control (Administrator) permissions on the database to be backed up.