How to Automatically Backup ConfigMgr Reports using SQL Server Agent
By Garth Jones
In a recent blog post, I showed you how to backup all of your ConfigMgr reports by using PowerShell. Then last week, I showed you how to automatically backup your reports using AfterBackup.bat. In that last blog post, I said that I would show you how to backup your ConfigMgr reports by using SQL task scheduler, so this blog post will show you how to do those steps.
Here are the steps that you will need to perform:
1. First, in order to create and test the PowerShell (PS) script, review the blog post How Do You Backup All of Your Custom ConfigMgr Reports? It is important that this step be completed BEFORE proceeding to the next step.
2. Copy the PS script to your SQL Server. I will use D:\PSScripts\ConfigReportsBak.ps1.
3. Determine which account is running the SQL Server Agent service. In my case, this is LocalSystem, otherwise known as SYSTEM account.
4. Ensure that the account named in Step #3 has at least READ and WRITE permissions.
5. Edit the PS script and make the following changes:
a. Change Line 21 to your ConfigMgr backup directory and add the folder called, Reports. In my example below, D:\SQLBak\Reports, I highlighted the line. You will notice that the Data&Time variable is no longer used.
b. Edit Lines 12 and 13 by removing the single quote and carriage return at the end of the line so that lines 12, 13 and 14 become one line. This will also change the position of Line 21 to Line 19.
6. Open SQL Server Management Studio (SSMS) and then expand the SQL Server Agent | Jobs node.
7. Right-click on the Jobs node and then select New Job…
8. Enter ConfigMgr Reports Backup in the Name field, and then click on the Steps page node in the left-hand side pages section.
9. Click the New… button.
10. Enter Backup ConfigMgr Reports for the Step name and change the Type field to PowerShell before clicking on the Open… button.
11. Browse to the ConfigReportsBak.ps1 script and then click on the Open button.
Note: If you get an Access Denied message at this step, check to make sure that you have granted both READ and WRITE permissions to the script.
12. Click OK to close the New Job Step window.
13. At this point, you can schedule the task to run or you can click OK to close the New Job window. I recommend that you close the Job and test it first before scheduling it. This way you can confirm that everything is working as expected.
Testing Your Job
1. To test your job, select the job and right-click. Select Start Job at Step…
2. This job will take a few minutes to complete. When completed, you will see a status window. Click on the Close button, otherwise, review the job status and job history for errors.
Add a Schedule to the Job
1. Select the job, right-click and point to Properties.
2. Select the Schedules node, click New… and then adjust the scheduled time to whatever makes sense for you. I would suggest daily and not have this task occur on the quarter hour increment. Click OK twice to set the schedule.
The SQL Server Agent is now configured to back up your ConfigMgr reports. Remember that you still need to, at a minimum, backup the D:\SQLBak\Reports folder using your traditional backups. If you have any questions, please feel free to contact me at @GarthMJ.