Maintenance Plans — резервное копирование и обслуживание баз данных в Microsoft SQL Server 2014

Microsoft SQL Server 2014 позволяет воспользоваться встроенными средствами резервного копирования и обслуживания баз данных. В данном случае план предназначен для обслуживания баз данных исключительно с моделью восстановления FULL. Если вы используете другую модель восстановления, то нужно воспользоваться другим планом, потому что резервное копирование лога транзакций вам может не потребоваться.

Описываемый план обслуживания успешно используется для баз данных 1С, но не ограничивается ими.

Модель восстановления FULL используется из-за настроенного зеркалирования баз данных.

Данный план решает три важные задачи:

1. Резервное копирование баз данных.
2. Ежедневное сжатие баз данных и лога транзакций.
3. Обслуживание баз данных.

Резервное копирование

Для резервного копирования используется три плана: ежемесячный, ежедневный, ежечасный. Каждый план делает резервную копию в своей сетевой папке.

Ежемесячный план

Условия ежемесячного резервного копирования следующие: ежемесячная копия должна храниться 2 года.

Ежемесячный план выполняется в последний день каждого месяца в 5:10 утра

1. Сначала выполняется проверка целостности. Если база повреждена - нет смысла делать её резервную копию.
2. Полное резервное копирование баз данных.
3. Резервное копирование лога транзакций.
4. Удаление старых бэкапов баз данных.
5. Удаление старых бэкапов логов транзакций.
6. Удаление истории.
7. Уведомление на почту об успешном выполнении плана.

Для того, чтобы можно было отправлять уведомления на почту, вам может потребоваться предварительно настроить Database Mail. 

Включаем Database Mail в Microsoft SQL Server 2014

Резервное копирование осуществляется в сетевую папку другого сервера. Примечательно, что по-умолчанию в качестве цели можно выбрать только локальные диски, но делать этого явно не стоит. Однако, SQL Server неявно позволяет вручную указать сетевой адрес, начинающийся с \\your_backup_server_name... Для экономии места на сервере бэкапов включено сжатие. Также включена проверка целостности резервной копии. Дополнительные настройки можно увидеть на картинке:

Ежедневный план

Условия ежедневного резервного копирования следующие: ежедневная копия должна храниться три месяца.

Ежедневный план выполняется каждый день в 1:10.​​​​​

1. Сначала выполняется проверка целостности. Если база повреждена - нет смысла делать её резервную копию.
2. Полное резервное копирование баз данных.
3. Резервное копирование лога транзакций. После этого задачи распараллеливаются:
   1. Сжатие базы данных.
   2. Остальные действия:
      1. Удаление старых бэкапов баз данных.
      2. Удаление старых бэкапов логов транзакций.
4. Уведомление на почту об успешном выполнении плана.

Если делать сжатие баз данных сразу после бэкапа лога транзакций, и если зеркало настроено в синхронный режим работы, то в этот момент лог транзакций успешно сожмётся. Тем самым мы экономим место на сервере. Этим пунктом мы выполняем одну из важных частей нашего плана по ежедневному сжатию баз данных и лога транзакций.

Резервное копирование осуществляется в сетевую папку другого сервера. Дополнительные настройки можно увидеть на картинке:

Ежечасный план

Условия резервного копирования следующие: необходимо обеспечить восстановление баз данных на любой момент времени за последнюю неделю. Резервное копирование файла логов транзакций позволяет это сделать.

Ежечасный план выполняется в 16-ю минуту каждого часа. На самом деле делать резервную копию логов транзакций можно и с другой периодичностью.

1. Резервное копирование лога транзакций.
2. Удаление старых бэкапов баз данных (необязательно).
3. Удаление старых бэкапов логов транзакций.

Уведомления на почту не делаем, ни к чему.

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

Резервное копирование осуществляется в сетевую папку другого сервера. Дополнительные настройки можно увидеть на картинке:

Обслуживание баз данных

Для обслуживания создан отдельный план, который выполняется каждый день в 4:10 утра. При этом выполняются следующие задачи:

1. Обновление статистики.
2. Сброс кеша хранимых процедур.
3. Перестроение индексов.
4. Реорганизация индексов.
5. Уведомление на почту об успешном выполнении плана.

Execute T-SQL Statement Task содержит в себе команду:

DBCC FREEPROCCACHE

Если будут вопросы - пишите в комментариях, постараюсь ответить и дополнить статью.