We’ve talked about moving the OpsMgr database from one SQL Server to another machine, but what if you want to keep it on the same server but need to move it to another drive? You may decide you want to do this if you have a second disk drive with more space, or to move to another spindle for better performance.
In the example below, we will move the database from the D: drive to the E: drive. The database file names are the default names of OperationsManager.mdf and OperationsManager.ldf. If you changed the database name as part of your OpsMgr installation, the filenames will also be different.
- To determine the location of the files for the OperationsManager Database, log into SQL Server Management Studio, connect to the server running the OperationsManager Database, click on the OperationsManager database and right-click New Query and enter: sp_helpfile. The filename will show the location (and names) of the OperationsManager database files.
- First, stop the OpsMgr services on the RMS (OpsMgr Config Service, OpsMgr Health Service, OpsMgr SDK Service). Also stop the OpsMgr Health Service on any other management servers.
- Backup the OperationsManager and the master database as well, just to be safe. This can be done using the SQL Server Management Studio; select the OperationsManager database in the left pane, right-click, select Tasks, select Back Up… and follow the instructions. Be sure to do a full backup. Do the same for the master database.
- Detach the OperationsManager database: In a SQL query select the master database and type: sp_detach_db ‘OperationsManager’ (Don’t highlight the OperationsManager database on the left pane or it will not detach because it is in use! Also the database will not detach because it is in use if any management servers are running the OpsMgr Health Service shut down).
- Using Windows Explorer, copy the data and log files from the current location to the new drive/location. We are assuming that the location is E:\Sqldata.
- Re-attach the database. In a SQL Query window select the master database and type: sp_attach_db ‘OperationsManager’, ‘E:\Sqldata\OperationsManager.mdf’, ‘E:\Sqldata\OperationsManager.ldf’
- Verify it worked using sp_helpfile. Select the OperationsManager database and in query window, type: sp_helpfile. The filename column returned from sp_helpfile should reflect the new locations.
- Restart the services on the RMS and other management servers and validate functionality of the new database.
This entry was designed to provide specifics as to how this is done for the OperationsManager database, see http://support.microsoft.com/kb/224071 for general processes regarding database moves.