Move an Analysis Services Database
There are often situations when an Analysis Services 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. |
Moving a database interactively using SSMS
-
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.
Moving a database programmatically using AMO
-
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.
Moving a database by script using XMLA
-
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.
