×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

How to change Data and Log device storage locations for Logger and AW/HDS databases

Document

Fri, 02/18/2011 - 12:59
Feb 18th, 2011
User Badges:
  • Cisco Employee,

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

Symptoms:

Using ICMDBA tool, customer installed Logger and AW/HDS databases(s) on C:\ drive but now wants to change the Data and Log device storage locations to another drive that has more space (D:\ drive) but unable to do so.


/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}


Resolution:

In order to change the Data and Log device storage location, you have to recreate the database using ICMDBA tool!  You can then select the desired Data and Log storage drive.

1.    Backup the Database (right click on database > All Task > Backup Database)

2.    Backup the .mdf and .ldf files for the database, stored in the current device location (<drive>\mssql\data).  In order to copy/paste the .mdf and .ldf files, you have to do the following:

a.    Stop Distributor or Logger service (depending on which database you’re working on).

b.    Using Enterprise Manager or SQL Server Management Studio, take the database offline (right click on the database > All Tasks > Take offline)

c.     Copy the .mdf and .ldf file, stored in the current device location (<drive>\mssql\data), to the newly specified location (<new drive>\mssql\data).

d.    Using Enterprise Manager or SQL Server Management Studio, bring the database online (right click on the database > All Tasks > Bring online)

3.    Once you have backed up the database and the .mdf and .ldf files, you can delete the database (right click on database > All Task > Delete)

4.    Create new database using ICMDBA tool.  Here, you can specify the new storage location of the Data and Log devices!

5.    Using Enterprise Manager or SQL Server Management Studio, take the database offline (right click on the database > All Tasks > Take offline)

6.    Replace the newly created .mdf and .ldf files, in the newly defined drive, with the ones that were recently backed up.

7.    Using Enterprise Manager or SQL Server Management Studio, bring the database online (right click on the database > All Tasks > Bring online)

8.    Start Distributor or Logger service (depending on which database you’re working on).


Loading.

Actions

This Document