Вики IT-KB

В случае, если резервное копирование баз данных Microsoft SQL Server выполняется сторонними средствами, например Microsoft System Center DPM, на регулярной основе, то для экономии дисковой ёмкости сервера БД, можно изменить режим восстановления Recovery model с используемого по умолчанию FULL на упрощённый SIMPLE.

Получить режим восстановления для всех баз данных:

SELECT name,recovery_model_desc AS Recovery_model 
FROM sys.databases 
ORDER BY recovery_model_desc, name

Другой вариант получить режим восстановления для всех баз данных, за исключением системных:

SELECT [name], DATABASEPROPERTYEX([name],'recovery') AS Recovery_model
FROM sysdatabases
WHERE name not in ('master','model','tempdb','msdb') 
ORDER BY Recovery_model, name

Как изменить режим восстановления на SIMPLE для всех баз дынных, за исключением системных:

USE MASTER
declare
   @isql varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128)
 
   declare c1 cursor for 
   SELECT  d.name, mf.name as logfile
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   where recovery_model_desc <> 'SIMPLE'
   and d.name not in ('master','model','msdb','tempdb') 
   and mf.type_desc = 'LOG'   
   open c1
   fetch next from c1 into @dbname, @logfile
   While @@fetch_status <> -1
      begin
 
      select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' checkpoint'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
      print @isql
      exec(@isql)
 
      fetch next from c1 into @dbname, @logfile
      end
   close c1
   deallocate c1

Проверено на следующих конфигурациях:

Автор первичной редакции:
Алексей Максимов
Время публикации: 20.08.2017 12:44