☰

使用 sp_msforeachdb 设置恢复模型

tag-icon • tag-icon sql-server
使用 sp_msforeachdb 设置恢复模型

我想编写一个脚本，将每个用户数据库的恢复模式设置为简单模式，并将其放入我的开发服务器的代理作业中。这看起来很简单（抱歉使用了双关语）： 

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

它也有效。仅针对正确的数据库执行，等等。但是，我收到以下错误消息：

消息 5058，级别 16，状态 1，第 1 行
无法在数据库“tempdb”中设置选项“RECOVERY”。

我可以针对用户数据库的 sys.databases 视图编写动态 SQL，但我想知道为什么 sp_msforeachdb 会生成此错误。

有没有人有经验可以阐明这一点？

编辑：将代码设置为排除任何 ID <5 的数据库，我很困惑为什么会出现这个错误。

答案1

您必须使用动态 SQL 来实现这一点，因为无论是否检查，每个数据库的 DDL 都会得到评估，但由于检查，它不会被执行。

set quoted_identifier on

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

答案2

根据文档：“不允许在 TempDB 上进行备份和恢复”。

这是因为它只是用于临时存储（备份和临时存储不能混为一谈）。因此，如果您需要备份它，那您就做错了。换句话说，微软正在努力防止出现错误。

包含 tempdb 的其他限制的参考文档：http://msdn.microsoft.com/en-us/library/ms190768.aspx

答案3

您之所以会收到该错误，是因为 DB_ID() 计算结果为“当前”数据库。因此，如果您在 master 中执行了 sp_msforeachdb 语句，db_id() 将始终计算结果为 1，因此条件将始终计算结果为 true。我怀疑您想要这样的东西：

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

答案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'

相关内容