In the case you have done a clean install of SCOM 2012 SP1 you might want to take a look at the file names of the OperationsManagerDW database and log files. They might look like this:
So empty names for the files and ending up with “.mdf” and “.ldf” for the file names.
However, my good friend Marnix Wolf also found that when using custom database and log file paths, the file names can take on the name of the directory they are in!
In the Release Notes of SCOM 2012 SP1 this known issue is mentioned (well, at least the part where the file names can be empty):
There are multiple ways to overcome the issue, and it would be best to solve this right after installation of the first management server before you install the other management servers.
One of the ways to fix this is to “Detach – Rename – Attach” the database. I will describe this process below with screenshots and all for those who like the step by step guides.
Preparations
First of all some work before we get started:
- BACKUP your database before you play around with it!!
- Stop the System Center * services on the management servers before you do the next steps
- Perhaps you want to make sure where your database files live, because you want to be able to find these files. Open SQL management studio. Right-click on the database OperationsManagerDW and select Properties. On the left click the “Files” tab and find the Path and File Name of the database. Remember these.
The steps
This procedure uses the Detach – Rename – Attach method.
- Open SQL Management studio and connect to the database instance where the SCOM databases live.
- Find the OperationsManagerDW database and right-click it. Go to Tasks – Detach…
- If there are still any connections just use the “Drop Connections” option
- After a succesful detach the database will be gone from the SQL management studio.
- Next go to the Windows Explorer and on your file system find the database files and rename them to what you want them to be called. In general the default would be OperationsManagerDW.mdf and OperationsManagerDW.ldf .
- In SQL management studio, right-click on Databases and select Attach…
- Click the Add button to select the database file.
- Select the mdf file belonging to the OperationsManagerDW database in the file path where it lives
- The resulting screen shows some information on how it wants to attach the database and information about the files. There are a few problems in this screen (red circles). The first is the Owner might not be the one you want and the second is that it can not find the two files it expects.
- To change the owner just click on that field and a dropdown list will appear where you can select from known logins and take the one you want.
- Next we have to go to the Not Found files. This is because the database file knows which files it relies on (at least one database file and one log file) and it only knows about the old file names in the old file paths. So we need to change both and point them to the correct files. The first one points to the database file (mdf), so click the small button next to it.
- Go to the correct path where the database file is located and select the database file OperationsManagerDW.mdf in my case.
- Next do the same with the second file and point it to OperationsManagerDW.ldf for the log file.
- Now attach the database
- when opening the properties of the OperationsManagerDW database and going to the Files tab you will now see the correct paths and file names for both files belonging to this database.
- Do not forget to start the System Center * services again on the management servers!
The management servers and databases should be fine now.
Like I mentioned before my friend Marnix Wolf used another method and that is the backup – Restore method, whereby you can define the path location and file names of the files. You can find that post here: http://thoughtsonopsmgr.blogspot.nl/2013/01/om12-sp1-known-issue-data-warehouse.html.
Thanks to our SQL DBA David Scheltens for the procedure!
Well, I hope this helps and enjoy your monitoring!
Bob Cornelissen