SET RECOVERY Model Usando sp_msforeachdb

Question

Eu queria criar um script que definisse o RECOVERY MODE para cada banco de dados do usuário para SIMPLE e colocá-lo em um trabalho de agente para meus servidores dev. Parecia simples (desculpe pelo trocadilho):

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

Também funciona. Apenas executando contra os bancos de dados corretos e tudo isso. No entanto, recebo a seguinte mensagem de erro:

Msg 5058, nível 16, estado 1, linha 1 A opção 'RECOVERY' não pode ser definida no banco de dados 'tempdb'.

Eu poderia escrever SQL dinâmico contra a exibição sys.databases para os bancos de dados do usuário, mas estou me perguntando por que é que sp_msforeachdb está gerando esse erro.

Alguém tem alguma experiência que possa esclarecer isso?

EDIT: Com o conjunto de códigos para excluir qualquer banco de dados com uma ID < 5, estou confuso sobre o motivo de eu estar recebendo este erro.

    

Answer 1

Você precisa usar SQL dinâmico para implementar isso, porque a DDL está sendo avaliada para cada banco de dados, independentemente da verificação, mas não é executada por causa da verificação.

set quoted_identifier on

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

    

Answer 2

De acordo com a documentação: "Backup e restaurações não são permitidas no TempDB".

Isso se deve apenas ao armazenamento temporário (backups e temporários não combinam). Então, se você precisa fazer o backup, está fazendo algo errado. Em outras palavras, a Microsoft está tentando evitar erros.

Documentação de referência que inclui outros limites de tempdb: link

    

Answer 3

Você está obtendo esse erro porque DB_ID () é avaliado no banco de dados "atual". Portanto, se você executou sua instrução sp_msforeachdb no mestre, db_id () sempre será avaliado como 1 e, portanto, a condição será sempre avaliada como verdadeira. Eu suspeito que você quer algo assim:

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'