By: Ashish Kumar Mehta | Comments | Related: > Analysis Services Administration
Problem
While looking through the new features and improvements in SQL Server 2008 Analysis Services (SSAS), we found a potentially interesting feature that allows you to detach and attach an Analysis Services Database. In this tip we cover the basic steps to detach and attach an Analysis Services database.
Solution
Due to business requirements, a database administrator may need to take an Analysis Services database offline for a particular period of time and then may need to bring it back online either on the same server or on a different server. The reason for such a need could be to move the database to another server for better performance or maybe a planned product upgrade, etc. In this tip, we go through an example of how to detach and attach an Analysis Services 2008 database using SQL Server Management Studio.
Detaching an Analysis Service Database Using SQL Server Management Studio
1. Connect to Analysis Service Database Instance using SQL Server Management Studio.
2. In the Object Explorer, expand Databases and then right click the Analysis Service Database and choose the Detach... option from the pop-up menu as shown in the snippet below. In this example I will be detaching Adventure Works DW database.
3. In Detach Database screen, you need to click OK to detach the Adventure Works DW database. Note that you can also include a password to encrypt certain data.
Attaching an Analysis Service Database Using SQL Server Management Studio
1. Connect to the Analysis Service Database Instance using SQL Server Management Studio.
2. In the Object Explorer, right click Databases and then select the Attach... option from the pop-up menu as shown in the snippet below. In this example I will be attaching the Adventure Works DW database which we have detached earlier.
3. In the Attach Database screen, you need to specify the folder where your Analysis Services database resides and click OK to attach the database. In addition, you would need to specify the password that was used when you detached the database in the previous step. Also, if you want to make this read only, select the Read-only check box.
By default, Analysis Services databases reside in "
To move an Analysis Database from one server to another you need to move the entire folder where the data exists. Also, you need to make sure that SQL Server has the correct folder and file permissions on the new location.
Next Steps
- Read these other Analysis Services Tips
- Here is another article on other approaches for moving an Analysis Services database
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
