{"id":2327,"date":"2016-10-19T21:19:36","date_gmt":"2016-10-19T19:19:36","guid":{"rendered":"http:\/\/blog.hosebei.ch\/?p=2327"},"modified":"2016-10-19T21:19:36","modified_gmt":"2016-10-19T19:19:36","slug":"configrmgr-the-complete-backup-currently","status":"publish","type":"post","link":"https:\/\/blog.hosebei.ch\/?p=2327","title":{"rendered":"ConfigrMgr – The complete Backup (currently)"},"content":{"rendered":"

Motivated from my attend at IT\/Dev Connection in Las Vegas, I wanted to create my complete Backup from ConfigMgr so far.
\nBecause 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:
\n\"SQL<\/a>
\n<\/p>\n

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.
\nWhen you have opened your SQL Management Studio, create a new Database, mine is called “DatabaseMaintenance”:
\n\"Created<\/a>
\nAfter creating the Database you can navigate to the to the SQL Server Agent and open the node Jobs, right-click on Jobs and Select \u201cNew Job\u2026\u201d:
\n\"SQL<\/a>
\nEnter a name for the Job, and switch to Steps:
\n\"SQL<\/a>
\nClick 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:
\npowershell.exe -command \"While(([math]::round(((Get-ChildItem -Path '\\\\deheim.hosebei.ch\\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 '\\\\deheim.hosebei.ch\\hosebeiDFSroot\\Backup_Folder\\SCCM_BackupLocation\\hosebeisccm01\\SQL_Backup_S01' | Sort CreationTime | select -First 1).Fullname -Recurse -Force}\"<\/code>
\nThis 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:
\n\"PoSh<\/a>
\nClose the Job by clicking OK Twice.
\nIf you consider to use the type “PowerShell”, DARE YOU<\/strong>! It is not working, or only limited functionality is given. See this wonderful Blog from SQL Hammer<\/a>.
\nThen navigate through the Management Tree and right-click on “Maintenance Plan” to select “New Maintenance Plan…”:
\n\"New<\/a>
\nAfter 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.
\nYou 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:
\n\"Maintenance<\/a>
\nTo add the first step of the maintenance Plan, open the Toolbox through View:
\n\"Maintenance<\/a>
\nFrom 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:
\n\"configmgr_backup07\"<\/a>
\nAnd 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:
\n\"Select<\/a>
\nTo 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:
\n\"Cleanup<\/a>
\nClick 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<\/a>. 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:
\n\"Create<\/a>
\nThis 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…”:
\n\"SQL<\/a>
\nEnter the name “Index Rebuild and Statistic Refresh” and click on Steps:
\n\"Step1\"<\/a>
\nClick on “New…” and enter the Name “Rebuild Index”, add the T-SQL command A from Ola Hallengren<\/a>. Make sure to change the Database to your Maintenance Database:
\n\"rebuild<\/a>
\nFinish 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<\/a>:
\n\"Statistic<\/a>
\nClick 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:
\n\"Step<\/a>
\nHere is the Job Task selection wizard:
\n\"configmgr_backup17\"<\/a>
\nNow it is time to implement the Windows Server Update Service cleanup script, which can be download from Microsoft:
\nRe-index the WSUS 3.0 Database<\/a>
\nJust 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:
\n\"WSUS<\/a>
\nInsert 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:
\n\"WSUS<\/a>
\nNow 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:
\n\"Backup<\/a>
\nDouble-Click on the added Backup Task and select the Databases you like to back up (in my Case: ConfigMgr DB, SUSDB and MaintenanceDB):
\n\"Backup<\/a>
\nMake sure that you select compress backup within the Options Tab, and it is always a good idea to select checksum and backup integrity:
\n\"SQL<\/a>
\nWhen 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:
\n\"Copy<\/a>
\nNow 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<\/a>), make sure to change the type to Operating System:
\npowershell.exe -command \"Add-Type -Assembly \u2018System.IO.Compression.FileSystem\u2019 -PassThru | Select -First 1 | % { [IO.Compression.ZIPFile]::CreateFromDirectory(\u2018d:\\program files\\microsoft configuration manager\\cd.latest\u2019, \u2018\\\\deheim.hosebei.ch\\hosebeiDFSroot\\Backup_Folder\\SCCM_BackupLocation\\hosebeisccm01\\SQL_Backup_S01\\cd.latest_\u2019 + (Get-Date -format \u2018yyyyMMddHHmm\u2019) + \u2018.zip\u2019) }\"<\/code>
\n\"CD.Latest<\/a>
\nMind 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.
\nAfter 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:
\n\"Maintenance<\/a>
\nDouble-Click on the added step, and select the previously created Job:
\n\"Select<\/a>
\nNow you are all set, and at last, you need to schedule the maintenance plan, just click on the calendar icon wihtin you subplan:
\n\"Schedule<\/a>
\nWithin the following wizard, you can configure how often this should run:
\n\"New<\/a><\/p>\n

Hope this helps, and if som one can provide a one-liner for exporting al Task Sequence (see this template Link<\/a>), I’m happy to update my blog post \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,19,33,36,38,40],"tags":[],"class_list":["post-2327","post","type-post","status-publish","format-standard","hentry","category-configmgr","category-general","category-sccm","category-setup","category-sql","category-tools"],"_links":{"self":[{"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=\/wp\/v2\/posts\/2327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2327"}],"version-history":[{"count":0,"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=\/wp\/v2\/posts\/2327\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.hosebei.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}