sql-server - 将 SQL Server 实例上的每个数据库设置为“简单恢复”

Question

我正在尝试遍历实例上的每个数据库，检查恢复模型，如果是Full，我想将其更改为Simple.

我在这里做错了什么：

USE MASTER

DECLARE @isql varchar(2000),
DECLARE @dbname varchar(64)

DECLARE c1 cursor FOR 
    SELECT name 
    FROM master..sysdatabases 
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN c1

FETCH NEXT c1 into @dbname

WHILE @@fetch_status <> -1
BEGIN
    SELECT @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    SELECT @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    SELECT @isql = 'USE @dbname checkpoint'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    FETCH NEXT FROM c1 INTO @dbname
END

CLOSE c1
DEALLOCATE c1

我收到此错误：

消息 102，级别 15，状态 1，第 11 行
“NEXT”附近的语法不正确

标签： sql-servertsqlsql-server-2008

Answer 1

我修复了您的查询，您忘记在FROM此处添加：FETCH NEXT c1 into @dbname

应该是FETCH NEXT FROM c1 into @dbname

请参阅以下固定代码：

USE MASTER

DECLARE @isql varchar(2000)
DECLARE @dbname varchar(64)

DECLARE c1 cursor FOR 
    SELECT quotename(name) 
    FROM master..sysdatabases 
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN c1

FETCH NEXT FROM c1 into @dbname

WHILE @@fetch_status <> -1
BEGIN
    SELECT @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    SELECT @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    SELECT @isql = 'USE @dbname checkpoint'
    SELECT @isql = REPLACE (@isql, '@dbname', @dbname)

    PRINT @isql

    EXEC (@isql)

    FETCH NEXT FROM c1 INTO @dbname
END

CLOSE c1
DEALLOCATE c1