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.

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

Click Next >

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

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

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

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

Under Database(s) client the drop down

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

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 >

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.

Click Finish to complete.

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.