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:

image

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)

image

However, the ConfigMgrBackup.ini created by the backup did not list the 3 additional database files (.NDF) in the SecondaryFiles line.

image

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.

image

What’s odd is the (.NDF) files from the ConfigMgr database are copied to the log (.LDF) file restore folder

image

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:

image

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

image

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:

image

You can see below the (.NDF) files where copied to the path I defined for the SQL Server Log file not MDF.

image

There are two issues here:

  1. The ConfigMgr backup service is not writing the additional (.NDF) database files to the ConfigMgrBackup.ini
  2. 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

image

This will allow ConfigMgr to successfully restore the database in the recovery and also keep the (.NDF) in the same directory as the (.MDF)

image

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.

image

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 (1)

  1. miguel rodriguez says:

    cannot confirm this, as i haven't done recovery for a while. valuable info anyway.
    we could also leverage the smsbkup.ctl file (smsbkup.box Folder) to include those files.
    add a line like this to the "file backup" section, just below
    # Add files to back up here:
    example:
    file T:\MSSQL11.MSSQLSERVER\MSSQL\DATA %SITE_SERVER_DEST%\SiteDBServer

    Syntax: file [source folder] [destination inside the backup folder]

    seems easier to me, and takes care of all files, even if we add any in the future 😉

Skip to main content