SET RECOVERY Model Using sp_msforeachdb

Question

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.

Answer 1

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
"

Answer 2

As per the documentation: "Backup and restores are not allowed on TempDB".

This is because it is just meant for temporary storage (backups and temporary don't go together). So if you need to back it up you are doing something wrong. In other words, Microsoft is trying to prevent mistakes.

Reference Documentation which includes other limits of tempdb: http://msdn.microsoft.com/en-us/library/ms190768.aspx

Answer 3

You're getting that error because DB_ID() evaluates to the "current" database. So, if you executed your sp_msforeachdb statement in master, db_id() will always evaluate to 1 and so the conditional will always evaluate to true. I suspect you want something like this:

EXEC sp_msforeachdb 'IF DB_ID(''?'') >= 5 ALTER DATABASE [?] SET RECOVERY SIMPLE;'

Answer 4

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''Recovery'')=''FULL''
    and   DatabasePropertyEx(''?'', ''Status'')=''ONLINE'' 
    and ''?'' not in (''tempdb'')
begin
  exec (''ALTER DATABASE [?] SET RECOVERY SIMPLE;'')
  print ''?''
end'