Backup and restore SCCM with SQL always-on

5 min readMar 14, 2021

Scenario:

SCCM with SQL always-on Backup.

I wrote this script long ago and I use it when there is a need to back up our SCCM environment. I thought that you might find it interesting, so I decided to write this post. We will go through the script and by the end , you will be able to understand what the script is doing. We will divide the script smaller parts and discuss them accordingly.

THE PROBLEM:

We have an SCCM environment with two sites, and 2 SQL serves with an availability group that contains our CM_XXX DB.

The SQL server for the SCCM is our listener.

We wanted to update our SCCM environment, so we tried to back up our SCCM. However, we couldn’t, so we have checked the smsbkup.log, and noticed this:

Everything looks fine.

Until the SCCM tried to back up the SQL database.

After about 5 minutes of -“Sleeping for 5 seconds…” the SQL backup failed.

After talking to Microsoft and reading an article about SCCM with SQL always-on carefully we have noticed that.

After some consultation, We decided to find a way to back up the SCCM and SQL even though we are working with an Always-on.

THE SOLUTION:

We need to back up the SCCM as if the “Backup maintenance task” were running correctly by making sure those key features happen:

It runs on a schedule.
Backs up the site database.
Backs up specific registry keys.
Backs up specific folders and files.
Backs up the CD.Latest folder.

SQL BACKUP.

To back up the CM_XXX database, all we have to do is right-clicking on it and choosing Task>backup, and restoring the database from there. We should make a maintenance plan that will rerun the back-up at any time we wish — link.

SCCM BACKUP.

When the SCCM backs itself with “Backup maintenance task” it backs up the next folders and files:

AdminUIContentStaging.
Inboxes.
Logs.
Data.
Srvacct.
Install.map.
CD.Latest.
Registry Keys.

The next snippet of code creates a new folder for our backup.

Change the $backup_path to where we want our back up to be stored.

Change the $sccm_Installation_Path to the SCCM installation path.

After that, we will need to copy the folders and files mentioned above. Those files and folders will be used later to restore the SCCM. Using the command below, we copy the files and folder from the SCCM installation folder to our backup folder.

Also, make sure you copy the content library folder if it is not already in a shared path.

Then we have to export and save the registry keys from the server to our backup folder.

Now after we fully backed our SCCM server, we need to create task schedule for the script (to automate the back up process). don't forget to add the necessary permission to the user who would run the task.

Read permission for the SCCM installation path.
Read permission for the Content library
Read permission for the registry.
Read and Write permissions to the backup folder.

Here is the Script, feel free to change it as you like.

Proof of concept.

First, We made some app and packages in our SCCM.

Then We have made those steps:

As we have mentioned above, we have backed up the SQL and SCCM and started the SMS_EXECUTIVE back again.
Then We deleted one application and one package from the SCCM console, and we stopped the SMS_Executive Service so it will not write to the DB.
After all of that, We will restore the DB from the backup I made earlier.
The database is in our availability group, so we have to remove the secondary replicas’ database.

We are running on all the secondary SQL servers the next SQL query.

ALTER DATABASE CM_XXX SET HADR OFF;DROP DATABASE CM_XXX;

Then removing the same database from the availability group of the primary replica.

use mastergoALTER AVAILABILITY GROUP [S-AG];remove DATABASE [CM_XXX];

Now we will restore the database from the backup.

alter DATABASE CM_001 set offline with rollback immediatealter DATABASE CM_001 set onlineRestore DATABASE CM_001 FROM disk = ‘C:\BKUP\SQL\BKup01.bak’ with REPLACE;

Alternatively, restore the DB by right-clicking on it, choosing Task>Restore>Database, and restoring the database from there.

Do not forget to add back the database to the availability group.

After the restore, we have fired up the SMS_EXECUTIVE service, and our apps and packages came back.

Conclusion.

We can backup and restore SCCM without using the SCCM backup maintenance plan.