Problem
One of the SQL Server Master Data Services (MDS) models was incorrectly modified and we need to restore the MDS database to the state before the modification took place. We have tried to restore the database, but we are getting errors when we try to browse the MDS data using the Explorer option in the MDS web application. How do we correctly restore and MDS database?
Solution
There could be different reasons why you may need to restore the MDS database, for example:
- Rolling back user changes (when rolling back transaction in MDS and other methods do not work)
- Rolling back a SQL Server patch
- Other user errors
- Other disaster recovery related reasons
Restore a Master Data Services Database
The process of restoring the MDS database is the same as for any other database, but there are additional steps that you may need to perform to make sure that MDS application does not display the following error when a user tries to view the MDS data:
MDS Configuration Manager Step to Complete the Database Restore
After the database restore we need to login to the MDS server and open MDS Configuration Manager.
Click on the “Database Configuration” option on the left and then click on the “Select Database” button:
Enter the MDS database connection information:
Enter the SQL Server instance name and click “Connect”. The MDS database name will be populated. Click “OK” to connect to the database.
Once you are connected you will get the MDS database connection information. You will see that there is an error saying that the database should be repaired:
Click on the “Repair Database” button and click “OK” on the pop-up message:
The database recovery is complete now.
Go back to the MDS application and verify that you do not get errors anymore.
Next Steps
- Read other MDS tips here.
- Make sure you have disaster recovery steps documented for the MDS.
- Check Microsoft resources about MDS.
- Read about SQL Server Restore with these tips and tutorial.
