I wanted to build a script that sets the RECOVERY MODE for each user database to SIMPLE and put it into an agent job for my dev servers. It seemed simple (sorry for the pun):
EXEC sp_msforeachdb 'USE ?; IF DB_ID() >= 5 ALTER DATABASE ? SET RECOVERY SIMPLE;'
It works too. Only executing against the right databases, and all that. However, I get the following error message:
Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
I could write dynamic SQL against the sys.databases view for the user databases, but I am wondering why it is that sp_msforeachdb is generating this error.
Does anyone have any experience that could shed light on this?
EDIT: With the code set to exclude any DB with an ID < 5, I'm puzzled as to why I'm getting this error at all.