Вики IT-KB

Пошаговые руководства, шпаргалки, полезные ссылки...

Инструменты пользователя

Инструменты сайта


microsoft-sql-server:t-sql-script-samples:how-to-get-and-bulk-change-recovery-model-for-all-databases-sql-server

Как получить и изменить режим восстановления (Recovery model) баз данных SQL Server

В случае, если резервное копирование баз данных 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

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

Версия SQL Server
Microsoft SQL Server 2012 Service Pack 3 (11.3.6020.0)

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

Обсуждение

НиколайНиколай, 23.12.2022 11:22, 23.12.2022 18:12
Большое спасибо!
Добавил в цикл четвертую инструкцию - вернуть режим FULL и поставил в задание:)
Кстати, ввиду названий баз и файлов лога пришлось добавить квадратные скобки - вот так:

select @isql='USE [' + @dbname + '] DBCC SHRINKFILE ([' + @logfile + '], 1)'

, но это мелочи
КиперКипер, 30.06.2024 14:24
@Николай, а при чем здесь усечение лога SHRINKFILE logfile?
Ваш комментарий:
 
microsoft-sql-server/t-sql-script-samples/how-to-get-and-bulk-change-recovery-model-for-all-databases-sql-server.txt · Последнее изменение: 20.08.2017 14:42 — Алексей Максимов

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki