Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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


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')
    print '?'
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:

share|improve this answer
Thanks Kyle. I understand the reasons why doing this would cause an error. That wasn't really my question, though. Why am I getting the error at all, is the real question. – RyanB May 31 '12 at 19:02
@RyanB: Oh I see now, sorry for the confusion. Going to leave this in case it helps someone else getting the same error for different reasons. – Kyle Brandt May 31 '12 at 19:27

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'')
  print ''?''
share|improve this answer
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – user130370 Apr 10 '14 at 7:51
@EricDANNIELOU: Again, how is that not answer? It's redundant, because the problem is solved for two years now and it lacks a bit of explanation, but still would likely solve the problem. – Sven Apr 10 '14 at 10:56
@SvW I never said this was not an answer, your automatic comment system did. Honestly, I don't think this post can be useful as is. – user130370 Apr 10 '14 at 11:48
@EricDANNIELOU: Is this some kind of automatic text? In that case, I am sorry, but I was unaware of that functionality ... – Sven Apr 10 '14 at 11:51
Yes, use the "review" button. I'd often like to say "Please provide more detailed answer or use the comment system if you just want to leave a clue". – user130370 Apr 10 '14 at 12:20

Your Answer


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.