Sql-server – SET RECOVERY Model Using sp_msforeachdb

sql server

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.

Best Answer

You have to use dynamic SQL to implement this because the DDL is getting evaluated for each database regardless of the check, but it doesn't get executed because of the check.

set quoted_identifier on

EXEC sp_msforeachdb "
IF '?' not in ('tempdb')
begin
    exec ('ALTER DATABASE [?] SET RECOVERY SIMPLE;')
    print '?'
end
"
Related Topic