ConfigrMgr – The complete Backup (currently)

Motivated from my attend at IT/Dev Connection in Las Vegas, I wanted to create my complete Backup from ConfigMgr so far.
Because I was required to copy a lot of work from other people together, I decided to create a blog with each detailed step to achieve a fine Backup at the end, and I will start from the beginning, until we have reached our goal:
SQL Backup Maintenance

First make sure you can access the SQL Management Studio, and that you are able to create a Database, which is required to store some Stored Procedures from an active community contributor which I will outline later on. I have created that all on my LAB, so SQL and ConfigMgr are installed on the same virtual Machine, this may also be true for smaller Organizations.
When you have opened your SQL Management Studio, create a new Database, mine is called “DatabaseMaintenance”:
Created Database
After creating the Database you can navigate to the to the SQL Server Agent and open the node Jobs, right-click on Jobs and Select “New Job…”:
SQL Server Agent Job
Enter a name for the Job, and switch to Steps:
SQL Powershell Delete Files
Click on “New…” to add a new step to this job, make sure that you change the type to “Operating System (CmdExec)” and copy to following (modified) powershell command to the Command Text block:
powershell.exe -command "While(([math]::round(((Get-ChildItem -Path '\\\hosebeiDFSroot\Backup_Folder\SCCM_BackupLocation\hosebeisccm01\SQL_Backup_S01' -Recurse) | Measure-Object -Property Length -Sum).sum / 1024 / 1024 / 1024)) -gt 10) { Remove-Item (Get-ChildItem -Path '\\\hosebeiDFSroot\Backup_Folder\SCCM_BackupLocation\hosebeisccm01\SQL_Backup_S01' | Sort CreationTime | select -First 1).Fullname -Recurse -Force}"
This will delete the oldest Files within the given UNC Path, if the Size of the folder is greater than 10 Gigabyte. Be sure that the selected “SQL Server Agent Service Account” has writable access to the destination Folder, which you might to change on the given example above. The Step should then look like this:
PoSh SQL Delete Files
Close the Job by clicking OK Twice.
If you consider to use the type “PowerShell”, DARE YOU! It is not working, or only limited functionality is given. See this wonderful Blog from SQL Hammer.
Then navigate through the Management Tree and right-click on “Maintenance Plan” to select “New Maintenance Plan…”:
New Maintenance Plan
After a click on save, and by refreshing the Tree, your newly created maintenance Plan will show up. The newly created maintenance plan is not scheduled yet, keep in mind, that a scheduled maintenance plan gets disabled, if the plan is changed after activating the schedule. You will have to manually enable the Job under SQL Server Agent again.
You should now have an empty Maintenance Plan in Front of you, when you double-click on the Subplan, you can change the description or name:
Maintenance Plan Subplan
To add the first step of the maintenance Plan, open the Toolbox through View:
Maintenance Plan Toolbox
From the toolbox, double-click on “Execute SQL Server Agent Job Task”, and the step will be added to the Subplan. This first step will delete existing files from the Backup folder created in the job with the powershell Script before. Now double click on the added step:
And select the previously created job from the list. Again, make sure that the User Account which runs the SQL Server Agent need to be able to perform those file operations on the target path:
Select Job
To rename the steps, just click on the name, and the known rename appearance will be shown. If you have finished the manipulations of the first step, Open the Toolbox again and add a step named “History Cleanup Task”, move the step an appropriate position, and connect it from the “Delete old files” step:
Cleanup Step
Click on save, the maintenance plan will be stored, we have to leave the creative process and set up our maintenance Database. This process is quite simple, and I take it from Ola Hallengren. He explains on his website how to use his work, I really want to point out that I’m a user of those as well. Just simply download the file called MaintenanceSolution.sql and run it within your SQL, mind to change the targeted Database:
Create MaintenanceDB
This will store the changes within your own database, rather than the master DB, which would work too. Now navigate to the SQL Server Agent and open the node Jobs, right-click on Jobs and Select “New Job…”:
SQL Server Agent Job
Enter the name “Index Rebuild and Statistic Refresh” and click on Steps:
Click on “New…” and enter the Name “Rebuild Index”, add the T-SQL command A from Ola Hallengren. Make sure to change the Database to your Maintenance Database:
rebuild index
Finish this step by clicking on OK. within the “New Job” Wizard click on “New…” again, and add a Step named “Statistic Refresh”, take as T-SQL command the C from Ola Hallengren:
Statistic Refresh
Click on OK twice to close the Step and finish the Job Wizard. You can now go ahead to the Maintenance Plan, and add through the toolbox a step named “Execute SQL Server Agent Job Task”, connect it with the parent step, and rename the step as you like. Double-Click the step and select the previously created Job:
Step Job
Here is the Job Task selection wizard:
Now it is time to implement the Windows Server Update Service cleanup script, which can be download from Microsoft:
Re-index the WSUS 3.0 Database
Just copy the Script Block to the Clipboard, we will insert it in the T-SQL Statement which will be added right now. Open the Toolboox and add a “Execute T-SQL Statement Task”, connect it to the previous step, and rename it if you like:
WSUS Cleanup step
Insert the T-SQL Statement from the clipboard, check if there is a hidden char after the “GO” command (there are three), which may fail the execution:
WSUS Statement
Now it is time to back up the Databases, go to the Toolbox and add “Back Up Database Task”, connect the added Task, and Rename it if necessary:
Backup DB
Double-Click on the added Backup Task and select the Databases you like to back up (in my Case: ConfigMgr DB, SUSDB and MaintenanceDB):
Backup DB Selection
Make sure that you select compress backup within the Options Tab, and it is always a good idea to select checksum and backup integrity:
SQL Backup Options
When this is done, you can now add the step to create the zip-compressed file of the CD.Latest Folder, but first we have to create a Job for this, so navigate to the SQL Server Agent right-click on “Jobs” and select “New Job…”, name the job as you like:
Copy cd.latest
Now go ahead and select the steps, and click on “New…” to add a step to this Job. Choose a name you like for this step, and copy paste the command from Kent Agerlund (Source), make sure to change the type to Operating System:
powershell.exe -command "Add-Type -Assembly ‘System.IO.Compression.FileSystem’ -PassThru | Select -First 1 | % { [IO.Compression.ZIPFile]::CreateFromDirectory(‘d:\program files\microsoft configuration manager\cd.latest’, ‘\\\hosebeiDFSroot\Backup_Folder\SCCM_BackupLocation\hosebeisccm01\SQL_Backup_S01\cd.latest_’ + (Get-Date -format ‘yyyyMMddHHmm’) + ‘.zip’) }"
CD.Latest Job
Mind to change the two folders to respect your ConfigMgr Installation and the Backup Path from your environment. For your Information: This powershell command is a fire-and-forget step, it does not wait for the powershell command to finish, the plan will continue the Maintenance Plan after the execution of the command. You can then close the Job by click on OK twice.
After the creation of this Job, you can go back to your Maintenance Plan, open the Toolbox and add a “Execute SQL Server Agent Job Task” to your Subplan. Connect this step with the following “Back Up Database Task” and rename the step if you like, your Subplan should look like the following:
Maintenance Plan
Double-Click on the added step, and select the previously created Job:
Select Job
Now you are all set, and at last, you need to schedule the maintenance plan, just click on the calendar icon wihtin you subplan:
Schedule SQL Maintenance Plan
Within the following wizard, you can configure how often this should run:
New Job Schedule

Hope this helps, and if som one can provide a one-liner for exporting al Task Sequence (see this template Link), I’m happy to update my blog post 🙂

3 thoughts on “ConfigrMgr – The complete Backup (currently)

    • Yes, that is in fact the goal of this process. The site backup does not compress the database, and it does only back up the ConfigMgr Database, so for the WSUS Database you will have to configure a SQL Backup anyway. Needless to say that you should test your backup regurarly, regardelss which solution you may use.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s