I understand this is an old thread, but it was the first hit when I searched "how to move mdw database" on Google.
I had configured MDW (Data Collection) on a SQL Server 2008 R2 production server, and I hosted the MDW database in a QA instance. Later on I had to move the QA instance to a different machine, with a different name (migration and name change mandatory for business reasons) and I wanted to keep the MDW data that was already collected. I could not find anything on that, so I gave this procedure a try and it worked for me:
- Stop all data collectors in the monitored server (production in my
case)
- Back up the MDW database, optionally put it offline (old QA server)
- Restore the MDW database to the new host instance (new QA server)
- Re-configure the monitored server to use the MDW database in its new
location (Right click on Data Collection, select Configure Management Data Warehouse. Select "Set up Data Collection" and click Next. Click on the ellipsis button and connect to the new instance, then from the drop down list select the MDW database that you restored in the previous step. Click Next, then Finish)
The data collectors should start automatically after the Configure Management Data Warehouse wizard finishes, and you can view the MDW reports in the new database location.
Then again, this worked for me in a SQL Server 2008 R2 environment, I am not sure if it works in SQL Server 2012.