Monday, June 9, 2014

SCCM 2012 SQL Server Backup Recommendations


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.



The article above is straight from Steve Thompson
http://stevethompsonmvp.wordpress.com/2013/06/07/sql-server-backup-recommendations-for-configuration-manager/

No comments:

Post a Comment