< Blog

How to Backup Your ConfigMgr Database Using SQL Server

By Garth Jones

Earlier this week, I wrote a blog post entitled, “How to Automatically Backup ConfigMgr Reports using SQL Server Agent.” I realized afterwards that I have never posted instructions on how to backup a ConfigMgr SQL database using SQL Server itself. I also did a quick check and there is not much in the way of details about how to do this task, so this post should help to fill in the gaps.

Although I’m not a fan of using SQL Server database-only backups, I recognize that I’m in the minority (I like ConfigMgr backups) but having said that, why would you want to use the SQL backup task over the ConfigMgr backup task?

There are a couple of main reasons:

First, SQL Server backup will NOT interrupt ConfigMgr. This might seem like an odd item, but when the ConfigMgr backup task runs, the first thing it does is shut down the SMSExec server. This will stop ConfigMgr from doing anything, such as distributing software.

Second, SQL Server backup will allow you to compress the backup file, thereby making it smaller.

Both are good reasons to use SQL Server backup instead of the ConfigMgr backup task.

Here are the steps that you will need to perform:

1. To start using SQL Server backup, open SQL Server Management Studio (SSMS), connect to your SQL Server, and then locate your ConfigMgr database. Right-click and point to Tasks and Back Up…

How to Backup Your ConfigMgr Database Using SQL Server-Step 1

2. Click the Add… button.

How to Backup Your ConfigMgr Database Using SQL Server-Step 2

3. Browse to your SQL backup location, in my example it is D:\SQLBak, then provide a name for the backup file (CM_RS2.bak) before clicking on OK.

How to Backup Your ConfigMgr Database Using SQL Server-Step 3

4. Click OK.

How to Backup Your ConfigMgr Database Using SQL Server-Step 4

5. Click the Media Options node and change the Back up to the existing media set to Overwrite all existing backup sets.

How to Backup Your ConfigMgr Database Using SQL Server-Step 5

6. Click on the Backup Options node and then change Set backup compression to Compress backup.

How to Backup Your ConfigMgr Database Using SQL Server-Step 6

7. In the top menu bar, click the Script menu and select Script Action to Job.

How to Backup Your ConfigMgr Database Using SQL Server-Step 7

8. Click OK to create the job.

How to Backup Your ConfigMgr Database Using SQL Server-Step 8 

9. Now in the Back Up Database window, click Cancel.

How to Backup Your ConfigMgr Database Using SQL Server-Step 9

10. Expand the SQL Server Agent | Jobs nodes. Notice that the newly created job is listed for you.

11. To test the job, right-click on the job and select Start Job at Step….

Note: This job might take a while to complete.

How to Backup Your ConfigMgr Database Using SQL Server-Step 11 

12. Once completed, you will see a status window. Click on the Close button. If there are any errors, review the job status and job history.

How to Backup Your ConfigMgr Database Using SQL Server-Step 12

Now that you have successfully tested your job, you can schedule the job to occur. I recommend that you adjust the scheduled time to whatever makes sense for you. I also recommend that you make it daily and not have this task occur on the quarter hour increment. Remember that you still need to, at a minimum, backup the D:\SQLBak folder using your traditional backups.

For details on 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.