< Blog

One-Stop ConfigMgr Backup Using SQL Server

By Garth Jones

Over the last month, I published several blog posts about backing up the ConfigMgr database, backing up ConfigMgr reports, and the importance of re-indexing the ConfigMgr database. Each of these how-to posts is setup as a single task, but wouldn’t it be nice if you could combine all three into one scheduled task? I know it would be great to do that, so this blog post will show you how to combine all three items into one SQL Server Agent Job.

To ensure that we’re all starting from the same point, make sure that you have read and implemented the steps in the following posts before starting:

· How to Automatically Backup ConfigMgr Reports using SQL Server Agent

· How to Backup Your ConfigMgr Database Using SQL Server

· Installation Guide to Ola Hallengren’s SQL Server Maintenance Solution

First, review and record the existing Job names in a table similar to the one I have below.

One-Stop ConfigMgr Backup Using SQL Server-Job Names

Each of my existing jobs will be executed by using the following SQL queries:

One-Stop ConfigMgr Backup Using SQL Server-Job Names Table

Next, we need to create a New Job.

One-Stop ConfigMgr Backup Using SQL Server-New Job

Provide a job title, and then click on the Steps node.

One-Stop ConfigMgr Backup Using SQL Server-Steps Node

Click New

One-Stop ConfigMgr Backup Using SQL Server-New

Give your Step a name and paste the first SQL query listed within the table created earlier in this post. Repeat this step for each Job within the table.

One-Stop ConfigMgr Backup Using SQL Server-Step Name 

Once completed, your job step list will look similar to the following:

One-Stop ConfigMgr Backup Using SQL Server-Job Step List

The only issue to be concerned with at this point is that all three jobs will start within a few seconds of each other and this is NOT ideal. The sp_start_job procedure will start the job and return immediately without waiting for each job to complete, so we need to fix this problem.

The trick to fixing this problem is to use the SQL command:

WAITFOR DELAY ’00:10:00′

This command will delay the next job for 10 minutes before continuing. I recommend adjusting the wait command for your environment (in my example below, I used 5 minutes).

To add a wait delay between jobs, select Job Step 2 and then click on the Insert… button. Give the new step a name and paste the wait command. Click OK to return back to the job step list. Repeat this process in-between each of the other steps.

Tip: Ensure that all step names are unique.

One-Stop ConfigMgr Backup Using SQL Server-Wait for Delay

When you are done, your Job will look similar to the one below.

One-Stop ConfigMgr Backup Using SQL Server-Completed Job Step List 

Once the job is saved, I recommend testing it and then scheduling it. I also recommend adjusting the scheduled time to whatever makes sense for you, but you should make it daily and do not have this task occur on the quarter hour increment.

For details about how to configure the schedule, please review the How to Automatically Backup ConfigMgr Reports using SQL Server Agent blog post. If you have any questions, please feel free to contact me at @GarthMJ.