Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 5 down vote accepted

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
"
share|improve this answer
    
Thanks a lot. I hadn't thought that the engine was still evaluating the SQL regardless of the condition. – RyanB Jun 4 '12 at 18:20

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

share|improve this answer

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;'
share|improve this answer
    
I thought the same thing but adding the ? to the DB_ID check, or adding a USE ? to the parameter still produces the same error. – RyanB May 31 '12 at 19:15
    
Hmm... interesting. What does "exec sp_msforeachdb 'select ''?'', db_id(''?'')' produce? – Ben Thul May 31 '12 at 23:05
    
With the IF statement it returns the Name and DB_ID of each of the user databases. – RyanB May 31 '12 at 23:58
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' 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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