Everytime we upgrade a SQL Server installation (service pack or cumulative update), msdb is changed from FULL recovery mode to SIMPLE recovery mode.
I understand that the msdb database doesn't change much and it is common to have it in SIMPLE, but I'm wondering why the installer is changing it. It is causing errors in my backup software.
Here is an extract from the installation logs:
2020-02-15 03:06:38.25 spid8s Starting execution of MSDB.SQL
2020-02-15 03:06:38.25 spid8s ----------------------------------
2020-02-15 03:06:38.35 spid8s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.35 spid8s Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.56 spid8s Checking the size of MSDB...
2020-02-15 03:06:39.41 spid8s
2020-02-15 03:06:39.41 spid8s Setting database option TRUSTWORTHY to ON for database 'msdb'.
2020-02-15 03:06:39.41 spid8s Setting database option RECOVERY to SIMPLE for database 'msdb'.
2020-02-15 03:06:39.46 spid8s ----------------------------------
2020-02-15 03:06:39.46 spid8s Finished execution of MSDB.SQL
2020-02-15 03:06:39.46 spid8s ----------------------------------
2020-02-15 03:06:39.46 spid8s -----------------------------------------
2020-02-15 03:06:39.46 spid8s Starting execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.46 spid8s -----------------------------------------
2020-02-15 03:06:39.47 spid8s -----------------------------------------
2020-02-15 03:06:39.47 spid8s Finished execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.47 spid8s -----------------------------------------
Best Answer
This is already mentioned in the documentation at Microsoft site as below:
You need to change the recovery model after doing any installation or service pack as per your requirement.
Hope above clarifies your concern.