Backup ConfigMgr Database with SQL Server

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 I will show you how to backup your ConfigMgr database using SQL Server. This post should 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…

2. Click the Add… button.

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.

4. Click OK.

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

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

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

8. Click OK to create the job.

 

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

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.

 

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.

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 about how to backup your ConfigMgr database using SQL Server, please feel free to contact me at @GarthMJ.