Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Detach and Attach a SQL Server 2008 Analysis Services Database


By:   |   Last Updated: 2010-07-20   |   Comments   |   Related Tips: > 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.

 Analysis Service Database Instance using SQL Server Management Studio

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.

 In Detach Database screen, you need to click OK to detach the Adventure Works DW database

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.

Attaching an Analysis Service Database Using SQL Server Management Studio

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 ":\Program Files\Microsoft SQL Server\\OLAP\Data\". In this example, I will be attaching the Adventure Works DW database from "E:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\Adventure Works DW.1.db".

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


Last Updated: 2010-07-20


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools