Applying Service Packs or Cumulative Updates changes msdb into SIMPLE recovery mode - by RobNicholson, MCSM

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 780752 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/5/2013 8:36:29 PM
Access Restriction Public


I discovered this problem in 2008R2 but is also present in SQL 2012.

After installing SQL Server 2008R2 SP2 10.50.4000 (from SP1) I checked the SQL error log and noticed the msdb transaction log backups were failing.  All the instances (six) on the server now have the recovery model for msdb set to SIMPLE.  These transaction log backups are required in case of disaster recovery (the backuphistory table can provide the locations and which backups have been taken).  The change happens at the beginning of INSTMSDB.SQL portion of the upgrade when the instance is first brought online.  The recovery model of msdb should be not changed during an upgrade.  

Besides breaking the log chain this causes a nuance as after the instances are patched msdb needs: 
1. To be changed back to FULL
2. Take a full backup of msdb 
3. Take a tlog backup of msdb

The Microsoft article below makes a recommendation that full recovery should be used if the history information needs to be restored, it then goes on to state that after each upgrade (SP or CU) msdb will be set to Simple recovery model.  It appears that upgrades created by Microsoft are breaking its own recommendation. 
Sign in to post a comment.
Posted by Microsoft on 6/6/2013 at 5:19 PM
Thanks for contacting us. This change is by design as noted on the document which you linked. While I do understand that this can be a maintenance burden after an upgrade, there is currently not a work around for us to switching the system into SIMPLE recovery
Posted by Microsoft on 3/11/2013 at 2:17 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.