SQL. Настройка резервного копирования.

После изучения множества информации с разных источников, решил описать процесс настройки резервного копирования БД  MS SQL Server для полной модели восстановления, какую модель использовать решать Вам, но от себя добавлю, что если в вашей БД большой поток информации (например создаются десятки, сотни или тысячи документов в 1 час), то потеря информации за день работы будет просто неприемлемой, в таком случае только полная модель обеспечит сохранность ваших данных. Данная статья предназначена для начинающих системных администраторов и содержит по моему мнению минимальный набор действий для резервного копирования БД 1С. Установка\Настройка самого SQL сервера и развертывание БД на нём в не рамках данной статьи.

Все настройки будем производить с помощью SQL Management Studio. Для начала нужно создать Устройство резервного копирования, можно и не создавать, но на мой взгляд это гораздо удобнее и правильнее. в оснастке SQL Management Studio -> Объекты сервера-> Устройства резервного копирования. Нужно указать имя устройства и файл в котором будут храниться резервные копии (лучше с расширением BAK), в дальнейшем можно посмотреть содержимое носителя, там будут перечислены все резервные копии. 

Теперь можно приступать к настройки Плана обслуживания (Maintenance Plan). План Обслуживания можно создать сразу для всех БД, но удобнее для каждой БД создать свой план обслуживания.

В нашем Плане обслуживания будет три подплана: 1 - резервное копирование БД (Полное); 2 - резервное копирование БД (Разностное); 3 - Резервное копирование Журнала транзакций. У каждого подплана есть свое расписание выполнения.  Расписание каждый настраивает по своим усмотрениям, в моём же случае полное копирование делается раз в неделю в воскресенье, Разностное копирование каждый день кроме воскресенья, ЖТ - журнал транзакций каждый час. При такой модели резервирования можно восстановить искомую БД на любую дату и час, причем экономим пространство на жёстком диске т.к. полное резервирование выполняется фактически раз в неделю, а в течении недели только изменения. 

 Настройка дневного расписания. Недельное отличается только установленной галочкой "Воскресенье" и снятыми с "понедельника" по "Субботу"

Расписание для ЖТ. Красным выделено время сохранения в течении дня, имеет смысл например, если пользователи работают с БД в определённый период, если режим работы 24х7, то оставляем по умолчанию.

На рисунке ниже, изображен редактор недельного подплана, он состоит из задач, которые выполняются в заданной последовательности. Последовательность задается вручную, причем зелёные стрелки означают, что следующая задача будет выполнена только при успешном выполнении предыдущей, а синяя, что задача выполнится при любом завершении предыдущей задачи. В редактор подплана обслуживания, задачи можно добавить из "Панель элементов", которая находится в левом верхнем углу, когда редактор открыт.

Задачи.  В каждую задачу нужно зайти и выбрать БД, для которой она будет выполняться и ряд других настроек (если есть). Рассмотрим, какие задачи содержит недельный подплан нашего плана обслуживания.

1. "Проверка целостности БД" (Check Database Integrity Task).  Следующая задача будет выполняться, только если БД не содержит ошибок. (Замем резервировать БД с ошибками?)

2. "Восстановить индекс" (Rebuild Index Task).  Восстановить (Перестроить) индекс необходимо каждый день, т.к. при работе с индексами они сильно фрагментируются и при фрагментации более 25% SQL начинает заметно "тормозить". Эта операция довольно ресурсоёмка, поэтому её можно делать хотя бы раз в неделю, а в дневном подплане заменить её менее ресурсоёмкую задачу "Реорганизация индекса". 

3. "Обновить статистику" (Update Statistics Task).  Для оптимизации... Кстати эту задачу можно выполнять несколько раз в течении дня, если ваша БД сильно нагружена.

4. После обновления статистики ОБЯЗАТЕЛЬНО нужно очистить процедурный кэш. Для этого перетаскиваем в редактор задачу "Выполнение инструкции T-SQL" и в поле "инструкция T-SQL:" написать процедуру DBCC FREEPROCCACHE. Но нужно учесть, что эта процедура очищает кэш у ВСЕХ БД, а мы обновили статистику по одной! Как очистить процедурный кэш для определённой БД, читаем здесь. Вкратце: DBCC FLUSHPROCINDB(ID_БД)

5. "Резервное копирование БД" (Back Up Database Task). В этой задаче указываем какую БД мы резервируем, тип резервной копии (Для недельного подплана -  Полное, для дневного - Разностное, для часового - Журнал транзакций.) Ставим переключатель в положение "Создать резервную копию баз данных в одном или нескольких файлах" и добавляем ранее созданное устройство резервного копирования. В таком случае ВСЕ копии сохраняются в один файл, который указали при создании  устройства резервного копирования, если переключатель оставить в "Создать файл резервной копии для каждой базы данных", то на каждое резервное копирование будет создаваться отдельный файл и на Полное и на Разностное и на ЖТ, что очень неудобно при восстановлении, зато удобно при хранении. Не забываем указать что нужно сжимать резервные копии!

6. "Очистка Журнала" Очищает записи, создаваемые при выполнении задач. Также можно включить задачу "Очистка после обслуживания" и настроить её для удаления текстовых логов или устаревших резервных копий.

     Подплан для резервирования ЖТ, состоит из одной задачи "Резервное копирование базы данных".ЖТ для меня удобнее сохранять не в Устройство резервного копирования, а в отдельный файл, что необходимо указать в настройке задачи.

UPD 12.01.2024

Начиная с версии платформы 8.3.22 1С создаёт все индексы с настройкой Allow page lock = False (Блокировка на уровне страниц) для уменьшения эскалации блокировок. В связи с этим задача "Реорганизация индекса" в дневном (Dayly) подплане завершается с ошибкой, и в журнале выполнения задач так же отображаться ошибка на выполнения дневного задания, что не есть хорошо.
 Есть несколько вариантов решения задачи:

1. Для не очень больших баз в дневном подплане заменить задачу "Реорганизация индекса" на "Перестроение индекса", ну или просто оставить только недельный подплан с расписанием запуска каждый день;
2. Для больших баз можно временно включить свойство Allow page lock = True для всех индексов, в задаче "Выполнение инструкции T-SQL"

USE [Infobase]
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)' 
GO

 затем выполнить задачу "Реорганизация индекса", и снова для всех индексов установить свойство Allow page lock = False

USE [Infobase]
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)' 
GO