Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

4 Answers 4

up vote 2 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
add comment

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
    
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
add comment

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
add comment
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
    
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. –  coincoin Apr 10 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. –  SvW Apr 10 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. –  coincoin Apr 10 at 11:48
    
@EricDANNIELOU: Is this some kind of automatic text? In that case, I am sorry, but I was unaware of that functionality ... –  SvW Apr 10 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". –  coincoin Apr 10 at 12:20
add comment

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.