Once you have finished the development of Analysis Services project, there are several methods available for deploying analysis services objects from one server to other server. In this series, we will discuss the most commonly used deployment methods one by one. This article covers deployment using BIDS. The entire series covers deployment using:
Deployment using Backup and Restore
Backup/Restore is a regular administrative task performed as part of a disaster recovery plan. Another occasion where you would want to backup/restore an SSAS database is when you can’t afford to process your analysis services database on a production server.
Backup Database:
To backup analysis services database, connect to the Analysis Server using SQL server management studio, expand the Databases folder, right-click the database you intend to backup and select Backup as shown in the figure below.
Figure 1
In the Backup Database dialog box shown below, check Apply Compression if you wish to compress the data in the backup file. Since partition files are already stored in compressed format, you won’t get much with this option. However, you will save some space for dimension files.
Figure 2
Select Encrypt backup file and type a strong password if you wish to encrypt the backup data, you would want to do this if your database contains sensitive information.However, please note that the backup file will be worth nothing if the password is forgotten or lost in which case nobody will be able to restore it.
Select Backup remote partition(s) if there are remote partitions (managed by another server) that you want to include in the backup.
Restore Database
In SQL Server Management Studio, connect to the analysis server, expand the Databases folder, right-click the database and select restore as shown in the figure below.
Figure 3
In the Restore Database dialog box, click the browse button to navigate to the place where you have the backup file to restore.
Figure 4
Check Include security information if you want to include the security information from the backup file. If you select Allow database overwrite, the Include Security Information option will be changed to Overwrite security information as shown in the figure below.
Figure 5
There are two options to choose for the security information:
- Copy all - This option will restore roles contained in the backup file, as well as the windows user/group accounts associated with the roles.
- Skip Membership - This option will restore the database roles contained in the backup file, but it will not restore the associated user/group accounts.
Click Allow database overwrite, if you wish to replace any existing databasewith same name. If you don’t wish to overwrite, please type in a different name.
If you opted to encrypt the database during backup, you will have to type the same password as you did at the time of backup. Please see the above figure for more details.
When to use Backup/Restore method
Use this method for deployment when you can’t afford to process your analysis services database on a production server. In such a scenario you can process the database on a staging server, and deploy it to the production server by backing up the database on the staging server and restoring it to the production server. You will also use this method when you don’t have the analysis services project file which gets created when the database is first developed in Business Intelligence Development Studio.
