MS SQL. Установка модели восстановления для всех баз

Все об администрировании Windows NT/2000/2003/2008/2012 Server (в т.ч. R2) серверов. Групповые политики, домены, безопасность и пр. То, чего не найдешь в бескрайних просторах Интернета. Решения тех проблем, которые не решаются типовыми ответами, которые можно получить в техподдержке Майкрософта - а именно: переустановить продукт или купить какой-ть другой лицензионный диск.


Модератор: UncleFather

Аватара пользователя
UncleFather
Site Admin
Сообщения: 1503
Зарегистрирован: 17 авг 2004 16:20, Вт
Контактная информация:

MS SQL. Установка модели восстановления для всех баз

Сообщение UncleFather »

Задача:

Необходимо установить ПОЛНУЮ (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


Alexander A. Manaeff©

Понравилась статья? Будем крайне признательны за репосты в соцсетях! Материально поддержать проект можно здесь

Мои странички:
ВКонтакте
Одноклассники
Youtube
Facebook
Instagram

Изображение
Изображение
Изображение
Изображение