Задача:
Необходимо установить ПОЛНУЮ (FULL) модель восстановления для всех созданных и вновь создаваемых баз MS SQL.
Решение:
Для вновь создаваемых баз модель восстановления, если она не указана явно при создании, устанавливается такой же как модель восстановления системной базы «model».
Для уже созданных баз их модели восстановления можно получить следующими скриптами Transact-SQL:
Для всех баз, включая системные:
Код: Выделить всё
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
Изменить режим восстановления на FULL для всех баз дынных, у которых иной режим восстановления, за исключением системных можно при помощи скрипта T-SQL:
Код: Выделить всё
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
Этот скрипт можно вставлять в задания планировщика MS SQL Agent перед выполнением резервного копирования баз. Тогда, при каждом резервном копировании будет выполняться проверка режима восстановления всех баз, кроме системных и переключение на полную модель восстановления баз с иной моделью восстановления.
Дополнительно можно почитать статью
Модели восстановления (SQL Server)
Источник: Как получить и изменить режим восстановления (Recovery model) баз данных SQL Server