ConfigMgr Site Restore Fails If Database Has Multiple Database Files
Overview:
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.
Issue:
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
Workaround:
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
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use
Comments
- Anonymous
March 15, 2016
The comment has been removed - Anonymous
August 03, 2016
This doesn't work, if the .NDF Files are splittet to two different drives. Are there any solution for this scenario? - Anonymous
August 24, 2016
Is the 25 MB auto-growth setting a standard practice you follow? I've been using Kent Agerlund's estimator spreadsheet, but it starts at 512 MB as the baseline and increases based on overall data size. Just curious.