I had a client that we recently implemented SCCM CB for. Everything was running smoothly, but they were getting an alert at 2:01 AM everyday stating that their Management Point was unhealthy. After some basic diagnostics, I was able to determine that their MP was fine, but it was the Maintenance Task Backup function that was causing the problem.
Why would the backup break the MP?
It turns out that the native backup function actually stops all of the internal services of SCCM when the backup process starts. The MP Health detection saw that the MP services had stopped, and then fired off the alert before the MP services started back up. Disabling the MP Health alert stopped the emails from going out, but it didn’t solve the problem.
SQL based backups are supported by Microsoft, but his wasn’t always the case. Support for this process was enabled for SCCM 2012 SP1. But the inherent problem with this mechanism is that it doesn’t backup the required files for SCCM to recover from backup, namely the CD.Latest folder.
Fortunately, with a little PowerShell and some knowledge of SQL Management Studio, we can resolve this short coming.
Before we get started, I need to acknowledge the work of Steve Thompson @Steve_TSQL. The script used is his, although slightly modified for UNC share usage. It is really effective. Thanks for the help Steve! (https://stevethompsonmvp.wordpress.com/2016/05/31/configuration-manager-sql-server-backup-guidelines/)
The first step in setting up the backup mechanism is to create the folders that will be written to. They can be local or UNC, but the key step here is to ensure that the Service Account used by the SQL Native Client has “Read” permission on the CD.Latest folder and “Full Control” of the target folder.
Next, we need to open up SQL Management Studio and create the job that will handle the folder copy function:
powershell.exe -command “Get-ChildItem -Path ‘\\[ServerName]\SCCM_Content\backup\CDlatest\*.zip’ | Where-Object {$_.CreationTime -lt (Get-Date).AddDays(-7)} | Remove-Item | Add-Type -Assembly ‘System.IO.Compression.FileSystem’ -PassThru | Select -First 1 | % { [IO.Compression.ZIPFile]::CreateFromDirectory(‘\\[PrimarySiteServer]\SMS_[SiteCode]\cd.latest’, ‘\\[ServerName]\SCCM_Content\backup\CDlatest\CDLatestArchive’ + (Get-Date -format ‘yyyyMMddHHmm’) + ‘.zip’) }”
***Note*** You will need to change the [ServerName] and [PrimarySiteServer] to match your environment
***Pro Tip***
Paste the command in PowerShell ISE so you can tweak the script to fit your own network or local paths. Once you have it dialed in, you can copy it over to SQL Management Studio.
-Start by right clicking on the Jobs folder under SQL Server Agent:
***Note*** If the icon is not green, but red, this means the SQL Native Client service isn’t running. Check the services snap-in (services.msc) to see if it is running. If it isn’t, start it.
-Give the job a name (in this case “BackupDemo”):
-Click on the Steps tab, then select “New”
-Give your step a name, select “Operating system (CmdExec)” in the Type menu, then paste your command line string into the Command window. Once all that is done, click “OK”. Once that has closed, click “OK” again to complete the wizard.
You can manually run the job once you have added the command line by right clicking on the copy job and selecting “Start Job at Step..” This is advisable to try before completing the process to ensure your command has been crafted successfully and to validate permissions on the source and target folders.
Your results should look like this:
-Next, we will need to create our Maintenance Plan. Right click on the Maintenance Plans folder and select “Maintenance Plan Wizard”
-Select “Next”
-Give the plan a name, then hit the “Change..” button next to the Schedule field.
-Create a schedule that works for your organization. In this case, I am scheduling the plan to run once a day at midnight. Select OK when complete.
-The schedule created should now appear in the Schedule field. Click next to proceed.
-Select the following items:
Clean Up History
Execute SQL Server Agent Job
Back Up Database (Full)
Maintenance Cleanup Task
Click next once complete.
-Move the Execute SQL Server Agent Job to the last step. Click Next when complete.
-Change the “Remove historical data older than” options to 1 Week(s). Click next when complete.
-On the Define Back Up Database (Full Task) – General Tab, in the Database(s) drop down, select the “All user databases (excluding master, model, msdb, tempdb)” option. Click OK.
-On the Destination tab, put in your target folder for your SQL backups. Tick the “Create a sub-directory for each database” checkbox. Make sure the “Backup file extension” is set to “bak”
-On the options tab, select the “Compress backup” option in the “Set backup compression” drop down. Once that has been set, click Next.
-Type or paste in the backup target folder in the “Folder” field under “Search folder and delete files based on an extension”. Type in “bak” under “File extension”. Tick the check box for “Include first-level subfolders”. Change the option “Delete files older than the following” to 1 week(s). Click next when complete.
-Select the CD.Latest backup job we created earlier. Click next when complete.
-Enter a path that you wish to use for backup logs. Click next when complete.
-Review the Maintenance Plan. Go back and change anything that needs correction. Click Finish when complete.
-The Maintenance Plan will be created. If all is successful, you should be greeted by this window. Click Close when complete.
-And our new Maintenance Plan should now be listed under “Maintenance Plans”!
-You can manually kick off the Maintenance Plan by right clicking on it, then selecting “Execute”. This is a good thing to do just to make sure that everything has been configured correctly.
-If nothing impedes the backup process, the SQL backup target folder should start to populate with the compressed backups
And our CD.Latest backup target folder should look something like this:
There are way to configure email alerting with SQL Maintenance plans, but I have yet to implement the functionality. It may be advisable to configure them to ensure that your backups are running correctly.
Once you have validated that the functionality is there, we can now safely rely on this function. If there is sufficient storage to store both the SQL Backup and the Native backup, it may be wise to implement both. Sometimes the “Belt and Suspenders” approach makes sense.
10,170 total views, 2 views today