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

By:   |   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.

 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms