Sometimes we need to detach a SSAS database in SQL Server. In this article, we will show you the steps involved in detaching and attaching a SSAS database in SQL Server with an example.
What is Detach Database?
The DETACH DATABASE option removes a database from the instance of SQL Server but leaves the database intact within its data files.
The DEATTACH_REBUILD_LOG option rebuilds the transaction log.
When to Use Detach Database Option?
There can be several reasons why you would want to remove a database from an instance of SQL Server:
To move the database files to another location. This is commonly done when you are running out of disk space on the drive that contains the current database files or log files.
To attach the database to another instance of SQL Server. This is commonly done for development or testing purposes. You can check RemoteDBA for more information.
To backup the database files without using SQL Server tools such as BACKUP DATABASE or Maintenance Plans.
How to Detach a Database in SQL Server?
In this example, we will detach the WideWorldImportersDW database.
1) Firstly, right-click on the Database node and select the Tasks >> Detach… option as shown below:
2) On the Detach Databases wizard, you need to select the databases that you want to detach from the server instance. As we want to detach only the WideWorldImportersDW database, so we will check that option and click on the OK button as shown below:
3) On the Completing the Wizard page, you need to click on the Finish button to detach the selected database from SQL Server.
4) Once you click on the Finish button, it will take some time to detach the database. You can verify whether a database is detached or not by checking in SSMS Object Explorer. If a database is detached then, it will not appear under the Databases node as shown below:
How to Attach a Database in SQL Server?
If at any point of time you want to attach a detached database back into your SQL Server instance then follow the steps below:
1) Firstly, right-click on the Database node and select the Tasks >> Attach… option as shown below:
2) On the Attach Databases wizard, click on the Add button to locate the MDF and LDF files of your database.
3) In our case, we have detached our WideWorldImportersDW database, so we will browse to that location where we have stored those files and then click on the OK button as shown in the snippet below:
4) Once you click on the OK button, it will populate the database name textbox with the name of your MDF file.
If you want to move a SQL Server Analysis Services (SSAS) database from one server to another, or if you want to change the name of an SSAS database, you will need to detach the database first. This article explains how to detach an SSAS database using SQL Server Management Studio (SSMS) and Transact-SQL.
Before You Begin
To detach an SSAS database, you must be a member of the sysadmin fixed server role or the db_owner fixed database role on the instance of SQL Server where the SSAS database is located.
Steps:
1. In Object Explorer, connect to an instance of SSAS, and then expand that instance.
2. Expand Databases, and then right-click the database that you want to detach.
3. Click Detach, and then click OK to confirm.
4. In the Detach Database dialog box, select Drop Connections if you want SSAS to drop all client connections to the database when it detaches the database. If you do not want SSAS to drop client connections, clear this check box.
5. Select Reset Database if you want SSAS to reset the database when it detaches the database. This will delete any partitions, aggregations, role memberships, data sources, linked objects, schedules, and so on that has been created for this database. If you do not want SSAS to reset the database, clear this check box.
6. Click OK to confirm the detach operation.
7. The database will now be detached from SSAS.
Conclusion:
Detaching and attaching databases is a common task that DBAs need to perform. This article explained how to do this using SSMS and Transact-SQL.
Also Read : Ogpush