title | description | ms.date | ms.service | ms.custom | ms.topic | ms.author | ms.reviewer | author |
---|---|---|---|---|---|---|---|---|
Move an Analysis Services Database | Microsoft Docs |
Move a multidimensional or tabular model database to a different location to meet needs like better performance, room for growth, or product upgrades. |
05/02/2018 |
analysis-services |
multidimensional-models |
conceptual |
kfollis |
kfollis |
kfollis |
[!INCLUDEappliesto-sqlas] There are often situations when an [!INCLUDEssASnoversion] database administrator (dba) wants to move a multidimensional or tabular model database to a different location. 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.
A database can be moved in many ways. This document explains the following common scenarios:
-
Interactively using SSMS
-
Programmatically using AMO
-
By script using XMLA
All scenarios require the user to access the database folder and to use a method for moving the files to the desired final destination.
Note
Detaching a database without assigning a password to it leaves the database in an unsecured state. We recommend assigning a password to the database to protect confidential information. Also, the corresponding access security should be applied to the database folder, sub-folders, and files to prevent unauthorized access to them.
-
Locate the database to be moved in the left or right pane of SSMS.
-
Right-click on the database and select Detach...
-
Assign a password to the database to be detached, then click OK to execute the detach command.
-
Use any operating system mechanism or your standard method for moving files to move the database folder to the new location.
-
Locate the Databases folder in the left or right pane of SSMS.
-
Right-click on the Databases folder and select Attach...
-
In the folder text box, type the new location of the database folder. Alternatively, you can use the browse button (...) to locate the database folder.
-
Select the ReadWrite mode for the database.
-
Type the password used in step 3 and click OK to execute the attach command.
- In your C# application, adapt the following sample code and complete the indicated tasks.
private void MoveDb(Server server, string dbName,
string dbInitialLocation, string dbFinalLocation,
string dbPassword, ReadWriteMode dbReadWriteMode)
{
//Verify dbInitialLocation exists before continuing
if (server.Databases.ContainsName(dbName))
{
Database db;
//Save current cursor and change cursor to Cursors.WaitCursor
db = server.Databases[dbName];
db.Detach(dbPassword);
//Add your own code to copy the database files to the destination where you intend to attach the database
//Verify dbFinalLocation exists before continuing
server.Attach(dbFinalLocation, dbReadWriteMode, dbPassword);
//Restore cursor to its original
}
}
-
In your C# application, invoke
MoveDb()
with the necessary parameters. -
Compile and execute your code to move the database.
-
Open a new XMLA tab in SSMS.
-
Copy the following script template for XMLA
<Detach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>%dbName%</DatabaseID>
<Password>%password%</Password>
</Object>
</Detach>
-
Replace
%dbName%
with the name of the database and%password%
with the password. The % characters are part of the template and must be removed. -
Execute the XMLA command.
-
Use any operating system mechanism or your standard method for moving files to move the database folder to the new location.
-
Copy the following script template for XMLA in a new XMLA tab
<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Folder>%dbFolder%</Folder>
<ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100">%ReadOnlyMode%</ReadWriteMode>
</Attach>
-
Replace
%dbFolder%
with the complete UNC path of the database folder,%ReadOnlyMode%
with the corresponding value ReadOnly or ReadWrite, and%password%
with the password. The % characters are part of the template and must be removed. -
Execute the XMLA command.
Attach and Detach Analysis Services Databases
Database Storage Location
Database ReadWriteModes
Attach Element
Detach Element
ReadWriteMode Element
DbStorageLocation Element