ConfigMgr Site Restore Fails If Database Has Multiple Database Files
I recently came across an issue that will cause a ConfigMgr restore to fail. This issue will only apply if you are using the built-in “Backup Site Server” maintenance task and your ConfigMgr database is configured with multiple database files. I was running 15-11, but this will likely affect previous 2012 versions of ConfigMgr.
You can verify if your ConfigMgr database is using multiple database files in SQL Management Studio by right-clicking the database and going to files:
If you let ConfigMgr setup create the database for you it will only contain a single MDF database file and this issue would not impact your site. I like to pre-create my database in order to pre-size it and split it into 4 database files as shown above.
The “Backup Site Server” maintenance task does correctly backup all the database files ( .MDF, .NDF, LDF)
However, the ConfigMgrBackup.ini created by the backup did not list the 3 additional database files (.NDF) in the SecondaryFiles line.
During the restore in the SetupConfigMgr.log (Mines Here) it will fail because the ( .NDF) files aren’t copied to the folder you tell the MDF database to restore.
What’s odd is the (.NDF) files from the ConfigMgr database are copied to the log (.LDF) file restore folder
When the ConfigMgrSetup.log shows it’s trying to attach the database the command will fail because the .NDF files don’t exist in the same folder as the .MDF database files.
You will see lines in the ConfigMgrSetup.log like this:
So the first thing I was thinking was to try to add the (.NDF) file names into the SecondaryFiles section in the ConfigMgrBackup.ini
Note: The trailing , is required after the last NDF file
This did allow the recovery to succeed, but the ( .NDF) database files were not copied to and attached to the ( .MDF) folder location you specify in the setup wizard. They are instead copied the log file folder and attached from that folder location:
You can see below the ( .NDF) files where copied to the path I defined for the SQL Server Log file not MDF.
There are two issues here:
- The ConfigMgr backup service is not writing the additional ( .NDF) database files to the ConfigMgrBackup.ini
- Even if the ( .NDF) files are manually added to the secondary files line in the ConfigMgrBackup.ini the site recovery setup will not copy them to the folder you specify for the MDF location instead they will copy to the LDF file location
I did find a workaround that will work and allow the recovery process the complete and restore the ( .NDF) files to the correct folder where the ( .MDF) is restored to.
You will need to pre-copy the ( .NDF) files from the ConfigMgr backup folder and copy them to the folder where you are going to restore the MDF database file to in the recovery wizard. In my case this was “E:\ConfigMgr MDF Database”
This will allow ConfigMgr to successfully restore the database in the recovery and also keep the ( .NDF) in the same directory as the ( .MDF)
Note: DO NOT edit the ConfigMgrBackup.ini to add the .NDF files
Also the ( .NDF) files will still be copied to the LDF folder defined in the recovery wizard. You can delete them after the recovery to save space.
Bug has been filed here: https://connect.microsoft.com/ConfigurationManagervnext/feedback/details/2400275