2

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      -----------------------------------------

5

This is already mentioned in the documentation at Microsoft site as below:

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

You need to change the recovery model after doing any installation or service pack as per your requirement.

Hope above clarifies your concern.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.