SQL Server Backup Recommendations for Configuration Manager

Update: 12/2015 – Be aware that starting with CM build 1511 and later, you’ll want to include the CD.LATEST folder in the backup files.

In Configuration Manager 2012 SP1 and later, note that you have the ability to recover a site (secondary sites don’t count) using a standard, native SQL Server database backup.

From the following link: http://technet.microsoft.com/en-us/library/gg712697.aspx

“Configuration Manager can recover the site database from the Configuration Manager backup maintenance task or from a site database backup that you perform by using another process. For example, you can restore the site database from a backup that is performed as part of a Microsoft SQL Server maintenance plan.”

Why is this important?

The standard ConfigMgr backup task will simply copy all of the database data and log files to the backup folder location. During the SQL Backup task, you have the ability to specify compression for a database backup.

In a recent test on our Central CAS, a standard ConfigMgr backup task resulted in a SQL backup size of about 542 GB. The backup size using the native SQL backup, with compression was 59 GB in size! That is a huge savings in disk space. Given that a restore really only needs the database, using the native SQL backup task makes sense.

There have been a few questions on how to set this up, and what retention to use.

Here is a guide on setting up the back up task using the built in SQL Server Maintenance wizard.

Open Microsoft SQL Server Management Studio and connect to your ConfigMgr SQL Server instance.

clip_image001

Expand the Management node, right click Maintenance Plan. Choose New Maintenance Plan Wizard.

clip_image002

Click Next >

clip_image003

Change the name of the plan. To the right of schedule, click the Change button

clip_image005

Enable backups for at least every other day. Click OK. Then Next >

clip_image006

Choose the following three options, highlighted above, click Next >, then Next > again.

clip_image007

Since you will not want to use backups for recovery over 5 days old, change this setting to 1 week. Click Next >

clip_image009

Under Database(s) client the drop down

clip_image010

Select the database(s) you wish to backup. Got reportserver installed? Be sure to select those databases as well. Click OK

clip_image012

Change the backup folder location to an alternate folder or drive as applicable. Optionally, select the create a subfolder checkbox for each database. IMPORTANT: choose Set backup compression ‘Compress Backup’ to take advantage of compression. Click Next >

clip_image013

For the Define Maintenance Cleanup Task, you will select the folder that the backups are being written, file extension name, include first level folders and reduce the delete file settings to 1 week. Click Next>, click Next > again.

clip_image014

Click Finish to complete.

clip_image015

You should be rewarded with success. Time for a cold one.

After the next schedule run date/time Double check that the database backup was in fact created. And, it is always a good idea to periodically restore a backup to an alternate location as a way to test that this is a valid backup.

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

37 Responses to SQL Server Backup Recommendations for Configuration Manager

  1. Pingback: SQL Server Backup Recommendations for Configuration Manager « Chris Nackers Blog

  2. Pingback: Anoop's » ConfigMgr SCCM Backup and Restore Guides

  3. Pingback: Thoughts on ConfigMgr backup strategy… | Marc Westerink's blog

  4. Pingback: Tales of a Forced ConfigMgr Site Restore | Adam the Automator

  5. Duberry Wotsit says:

    Is a full backup of the database enough or does anything need to be done with the logs?

    • configmgrmvp says:

      Full database backup is all that is required. For your CM, SUSDB & Reporting databases; you should be running Recovery mode = simple, so the transaction logs are not needed.

  6. Pingback: ConfigMgr 2012 RTM to R2 Upgrade checklist | Steve Thompson [MVP]

  7. Pingback: ConfigMgr 2012: SQL Maintenance Plans and Index Optimization Recommendations « Chris Nackers Blog

  8. Pingback: ConfigMgr 2012: SQL Maintenance Plans and Index Optimization Recommendations « Nackers Consulting Services, LLC

  9. Pingback: Install ConfigMgr 2012 R2 CU1 in your hierarchy | System Center 2012 by EG

  10. Pingback: SCU 2015 – Top 10 ConfigMgr 2012 Issues – SQL Content Links | Steve Thompson [MVP]

  11. To add a bonus, the SCCM site keeps running while SQL is making a backup. When you backup the site using the SCCM Maintenance Tasks the SCCM site gets shutdown.

  12. Pingback: How can I upgrade to System Center 2012 R2 SP1 with MDT 2013 Update 1 integrated? | just another windows noob ?

  13. Stefan says:

    Using a SQL maintenance plan creates a backup of the DB just as a charm. But what about the files and reg keys of the site? For this I could use the sms site backup task again – but this task always dumps the site DB… So I still end up with copies of the hughe database and log devices?!? Dis you manage to disable the database dump in the sms backup task?

    • Steve T says:

      Those added files are not needed. In fact, during a site recovery, using the full CongfigMgr 2012 backup task data, the only thing used is the database backup. The files are thrown away. One more reason to use native SQL backup, with compression.

      • Mike says:

        Hi Steve – do you have a source for that? (Not needing anything except the .BAK during a recovery). I am a DBA and I’m working with our SCCM admin to find the best backup approach, and if I can show that to be true then we can go with SQL native backups.

      • ConfigMgrMVP says:

        As mentioned in the post, in addition to the *BAK db files, starting with 1511 you will need the CD.LATEST folder. That is it.

  14. Pingback: Automize | Step-By-Step guide for Upgrading SCCM 2012 R2 SP1 to CU2

  15. Dan says:

    What sort of backup is recommended on the Secondary Site DBs?

    • Steve T says:

      Secondary sites are not backed up. Check that MSFT link I posted. Secondary sites are recovered by re-installing from the parent primary site.

  16. Pingback: How to perform a Testdbupgrade before SCCM Installation

  17. Pingback: Step-By-Step guide for Upgrading SCCM 2012 R2 SP1 to CU2

  18. Pingback: Upgrade your 2012 R2 SP1 ConfigMgr environment to 1602 #LikeABoss – Ami Casto

  19. This post has become a landmark on the web for SCCM admins. Good work – thank you! The process works the same with SQL 2014 except that the Back Up Database (Full) task dialog now has 3 tabs and moves things around a little. But still… this is extremely helpful.

    • Steve T says:

      Thank you for the feedback. Should probably create an updated version for SQL 2014. However, will wait until SQL 2016 is released to see what else changed.

  20. Pingback: Configuration Manager SQL Server Backup guidelines | Steve Thompson [MVP]

  21. yamin says:

    if we follow the above procedure, the cd.latest folder gets backup everyday?

  22. Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]

  23. Pingback: ConfigMgr SCCM 1606 Upgrade Guide and Explanation - PowerON

  24. Pingback: Upgrading to Configuration Manager 1606 | Harry Lowton

  25. John Hollcraft says:

    Hey, Steve I guess I still don’t trust a SQL only backup for a safe restore. Old school maybe. I’ve had success restoring a production environment using the site server backup but never have attempted a DB-only restore. BTW, what SQL permissions are required for a remote clustered SQL Server?

  26. John Hollcraft says:

    I know it’s supported but I for a standalone primary site I would still rather have a site back up. Actually, I always have both a site back up and a SQL Maintenance Plan just for redundancy. Also you need more than just the site back up to do the job right. All of the source files need to backed up if you lose the server. I actually had to do this about a year ago. Client had lost everything but I had been on site for a patching remediation project and had insured that everything was backed up to archival storage. When the disaster hit we were able to restore in 12 hours or so.
    I’m slowing up a bit but still do a project or two from time to time. It has been a long time since SMS 1.1 🙂

    • ConfigMgrMVP says:

      Had typed a lengthy reply, only to have one errant key stroke erase it. Other files are needed for a full restore, this is mentioned here:
      https://stevethompsonmvp.wordpress.com/2016/05/31/configuration-manager-sql-server-backup-guidelines/

      The point of this blog article was to show that a native SQL backup could be used for a recovery. In my humble opinion, a site maintenance plan backup buys nothing and in one client that I worked with prevented a daily SQL backup because the maintenance plan COPIES the data files with no compression. The other files such as site control are not used and thrown away during a restore… so, what real value is the site maintenance plan? (rhetorical question) The product team knows that this component has not changed for years and is somewhat broken.

      You are one of the few I know that has used this product since the 1.x days – lol.

  27. Pingback: Top 5 blog posts for 2016 | Steve Thompson [MVP]

  28. Pingback: Installing SCCM 2012 SP2/R2 SP1 – Quick Start Guide

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s