Alexander A. Manaeff

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

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 <> 'FULL'
   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 FULL'
      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

[b][size=150]Задача:[/size][/b]

Необходимо установить [b][i]ПОЛНУЮ (FULL)[/i][/b] модель восстановления для всех созданных и вновь создаваемых баз MS SQL.


[hr][/hr]
[b][size=150]Решение:[/size][/b]

Для вновь создаваемых баз модель восстановления, если она не указана явно при создании, устанавливается такой же как модель восстановления системной базы «[i]model[/i]».

Для уже созданных баз их модели восстановления можно получить следующими скриптами Transact-SQL:

Для всех баз, включая системные:
[code]SELECT name,recovery_model_desc AS Recovery_model
FROM sys.databases
ORDER BY recovery_model_desc, name[/code]

Для всех баз данных, за исключением системных:

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

Изменить режим восстановления на [b][i]FULL[/i][/b] для всех баз дынных, у которых иной режим восстановления, за исключением системных можно при помощи скрипта T-SQL:

[code]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 <> 'FULL'
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 FULL'
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[/code]

Этот скрипт можно вставлять в задания планировщика MS SQL Agent перед выполнением резервного копирования баз. Тогда, при каждом резервном копировании будет выполняться проверка режима восстановления всех баз, кроме системных и переключение на полную модель восстановления баз с иной моделью восстановления.

Дополнительно можно почитать статью
[url=https://docs.microsoft.com/ru-ru/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017]Модели восстановления (SQL Server)[/url]


Источник: [url=https://wiki.it-kb.ru/microsoft-sql-server/t-sql-script-samples/how-to-get-and-bulk-change-recovery-model-for-all-databases-sql-server]Как получить и изменить режим восстановления (Recovery model) баз данных SQL Server[/url]