Attach and Detach Analysis Services Databases
There are often situations when an Analysis Services database administrator (dba) wants to take a database offline for a period, and then bring that database back online on the same server instance, or on a different one. These situations are often driven by business needs, such as moving the database to a different disk for better performance, gaining room for database growth, or to upgrade a product. For all those cases and more, the
Detach commands enable the Analysis Services dba to take the database offline and bring it back online with little effort.
Attach and Detach commands
Attach command enables you to bring online a database that was taken offline. You can attach the database to the original server instance, or to another instance. When you attach a database the user can specify the ReadWriteMode setting for the database. The
Detach command enables you to take offline a database from the server.
Attach and Detach Usage
Attach command is used to bring online an existing database structure. If the database is attached in
ReadWrite mode, it can be attached only one time to a server instance. However, if the database is attached in
ReadOnly mode, it can be attached multiple times to different server instances. However, the same database cannot be attached more than one time to the same server instance. An error is raised when an attempt is made to attach the same database more than one time, even if the data has been copied to separate folders.
If a password was required to detach the database, the same password is required to attach the database.
Detach command is used to take offline an existing database structure. When a database is detached, you should provide a password to protect confidential metadata.
To protect the content of the data files, you should use an access control list for the folder, subfolders, and data files.
When you detach a database, the server follows these steps.
|Detaching a read/write database||Detaching a read-only database|
|1) The server issues a request for a CommitExclusive Lock on the database
2) The server waits until all ongoing transactions are either committed or rolled back
3) The server builds all the metadata that it must have to detach the database
4) The database is marked as deleted
5) The server commits the transaction
|1) The database is marked as deleted
2) The server commits the transaction
Note: The detaching password cannot be changed for a read-only database. An error is raised if the password parameter is provided for an attached database that already contains a password.
Detach commands must be executed as single operations. They cannot be combined with other operations in the same transaction. Also, the
Detach commands are atomic transactional commands. This means the operation will either succeed or fail. No database will be left in an uncompleted state.
Server or database administrator privileges are required to execute the
Server administrator privileges are required to execute the