gifts2017

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

Опубликовал Андрей Старченко (dr.death) в раздел Администрирование - Архивирование (backup)

Настройка резервного копирования БД 1С на MS SQL Server. На примере MS SQL Server 2012

После изучения множества информации с разных источников, решил описать процесс настройки резервного копирования БД  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_БД)

 T-SQL

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

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

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

 подплан ЖТ

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Саша Безымяный (help1Ckr) 11.09.13 10:01
Спасибо за статью. В приципе у нас есть админ, который делает бэкапы, но эти знания не лишние
2. rasswet (rasswet) 11.09.13 10:37
3. Алексей Митюков (kvert) 11.09.13 12:39
Спасибо за статью!
Вопрос: а возможно ли как-то настроить планы обслуживания в MS SQL Server Express ?
4. Андрей Старченко (dr.death) 11.09.13 14:00
(3)kvert, Не разу не работал с MS SQL Server Express, но в описании вроде есть графическая среда Management Studio Express, вот в ней и надо посмотреть. Ну и всегда можно написать запрос или скрипт на backup и запускать его по расписанию стандартными средствами ОС.
5. Александр Лыткин (TrinitronOTV) 16.09.13 10:10
Спасибо большое за предоставленную статью, главное, что всё наглядно представлено
6. Алексей Ширин (shiaju) 09.01.14 07:10
Хорошая статья для новичков!
7. Владимир Юровский (yurowski) 16.06.14 17:02
Отлично! Я себе тоже настроил.
8. Кайрат Сапаров (Astrakhan_man) 05.09.14 16:38
а можно запускать этот план обслуживания при работе пользователей, когда они проводят много документов? и когда меняется конфигурация?
9. Антон Стеклов (asved.ru) 05.09.14 17:28
(8) Astrakhan_man, конкретно этот - нельзя, он содержит задачу ребилда индексов. В остальном можно (т.е. резервная копия будет транзакционно целостна), но любые операции отнимают какие-то ресурсы.
Astrakhan_man; +1 Ответить
10. Андрей Старченко (dr.death) 09.09.14 04:36
(8) Astrakhan_man, Дневной и Trn можно, недельный - нет. При Настройке расписания нужно это учесть. В Sql этот план отобразиться в "Agent -> Задания" по вложенным планам: WEEKLY, Dayly от туда же их можно запускать вручную.
Astrakhan_man; +1 Ответить
11. Ed S (Eduard66) 26.09.14 12:32
А зачем обновлять статистику, если делается Rebuild index?
12. Иван Иванов (Famza) 12.03.15 12:56
При таком плане растет журнал транзакций. Надо бы и его шинковать?
13. Игорь Кот (mysav) 26.03.15 18:25
Какие нюансы есть при настройке резервного копирования распределенной базы данных?
14. Василий Коровин (vasyak319) 26.03.15 19:23
А зачем грохать процедурный кэш, если апдейт статистики всё равно инвалидит все скомпилированные планы запросов и процедуры и они будут перекомпилированы при первом обращении к ним?
15. Александр Киричков (GreenDragon) 28.03.15 12:45
(12) Famza, во-первых, шринкование журнала транзакций при полной модели восстановления и архивирования лишено абсолютно всякого смысла, если ты себе не враг. Читай внимательно теорию. Шринк - это операция удаления из журнала зафиксированных транзакций, что нарушит последовательность ведения непрерывной цепочки транзакций в бекапах, соответственно про восстановиться ты сможешь только на момент создания последнего исправного полного либо разностного архива.
Во-вторых, любителям шринкования нужно вспомнить - что происходит, когда используемое место в журнале транзакций достигает 75%.
16. Александр Ширипов (shira84) 15.05.15 08:36
а как менять уже созданное задание? все перековырял, не пойму как добавить еще пару баз для архивирования, максимум можно расписание поменять и все.
17. Александр Ширипов (shira84) 15.05.15 08:41
а все нашел, управление-планы обслуживания-резервное копирование, находим нужную задачу, щелкаем на картинку, в контекстном меню изменить.
18. Leonid Korolev (leonidkorolev) 15.05.15 09:41
"но на мой взгляд это гораздо удобнее и правельнее." на мой взгляд это вообще не удобно и неправильно. Вообще ничё не видно, по сути неуправляемая система бэкапирования. Например я вручную подчищаю, проверяю вообще наличие копий и т.д. Всё делаю визуально. Более того, я подозреваю что в описанной схеме файлы разностных копий могут уже через два три дня быть больше полного бэкапа.Если хранить в одном файле то я не знаю это можно ли отследить. Неделя это очень много. Я делаю каждый день полный, через два часа разностный и через каждые пять минут логи. Потеря данных может быть максимум за 5 минут работы, в статье за час.(скрин своего плана прикрепил). Час это много.

"что очень неудобно при восстановлении, зато удобно при хранении." И в чём же здесь неудобство. Вот лежать себе файлики и лежат, никому не мешают.

"(Замем резервировать БД с ошибками?)" Эээээ.... Представляю себе следующую картину. В результате ошибки на пункте Проверка целостности копия не была сделана. В конце дня падает база. В своё оправдание вы говорите начальству, что базу нельзя восстановить т.к. нет смысла делать копию после пункта Проверка целостности в случае ошибки. Может всётаки будем делать всегда, невзирая ни на что?
Прикрепленные файлы:
19. Leonid Korolev (leonidkorolev) 15.05.15 09:54
(15) GreenDragon,

"Шринк - это операция удаления из журнала зафиксированных транзакций, что нарушит последовательность ведения непрерывной цепочки транзакций в бекапах, соответственно про восстановиться ты сможешь только на момент создания последнего исправного полного либо разностного архива." Эээээ... а вы на полной модели восстановления пробовали это сделать? Принцип целостности данных для скуля святое. Не шринканете пока не забэкапите. Как вы говорите? "Читай внимательно теорию."
20. Андрей Старченко (dr.death) 15.05.15 12:02
(18) leonidkorolev, На вкус и цвет, как говорится... Никто вам не запрещает делать trn каждые 5, 10, минут, да хоть каждую минуту, конкретно у нас 1 час не критично, у кого либо может и 5 секунд критично будет, статья не об этом, а служит примером настройки. Про хранение аналогично, вот именно мне, мешает КУЧА файлов, но опять же на вкус и цвет... Разностные копии могут быть за несколько дней больше одной полной, но не больше, чем на каждый день делать полные! Про ""(Замем резервировать БД с ошибками?)" Эээээ....", возможно здесь вы правы, резервировать нужно.
21. Leonid Korolev (leonidkorolev) 15.05.15 13:49
(20) dr.death, "Разностные копии могут быть за несколько дней больше одной полной, но не больше, чем на каждый день делать полные!" Откуда такая уверенность да и ещё с восклицательным знаком? Я же спросил, вы можете вообще посмотреть объем ваших разностных копий? Вы видите что у вас копируется вообще и какого объёма? Оптимальна ли схема бэкапирования? Отчего зависит вообще объем разностных копий? Я всё это вижу (см. скрин выше), а вы как, на авось? Элементарное перепроведение документов бухом раздует дифференциальный бэкап до терабайтов. И чё делать? Удалять весь бэкап, всю историю бэкапов?
22. Андрей Старченко (dr.death) 15.05.15 14:25
(21) leonidkorolev, Почему на авось? Что мешает посмотреть содержимое "Устройство резервного копирования"? Ну и на диске он выглядит как один файл с вполне конкретным значением. А Trn у меня сейчас в разных файлах в одной папке. При восстановлении SQL знает что и где лежит.
Прикрепленные файлы:
23. Sesh Ren (Deroswent) 17.08.15 13:58
Собственно вопрос: а как удалить Резервные Наборы Данных с истекшим сроком годности???
Сделано все приблизительно как описано в статье, и вырос мой файл BACKUP.DAT уже до 66 Гб......
Прикольно конечно что я могу восстановить БД до состояния "4 месяца назад в 1,45 ночи" но дикс не резиновый то....
24. Leonid Korolev (leonidkorolev) 17.08.15 14:21
(23) Deroswent, ну вот ещё один минус копирования в один файл. Оставьте этот раздутый файл и переделывайте схему бэкапирования.
25. Sesh Ren (Deroswent) 17.08.15 14:30
(24) leonidkorolev, вот я и стаю на пороге этого. Но никак не могу сообразить как удалять ненужные мне файлы. Допустим, каждый бекап делается в отдельный файл.
Как удалить файлы старще 14 дней к примеру?
26. Андрей Старченко (dr.death) 18.08.15 05:44
(23) Deroswent, Если будете сохранять в отдельные файлы, то в план обслуживания добавьте Задачу "Очистка после обслуживания" и настройте на удаление резервных копий. Я же переношу раз в 3 мес. этот один большой бэкап на сервер-архив. После срабатывания задания опять создается файл бэкапа с новым архивом.
27. Федор Скобунеев (Skobuneev) 07.10.15 11:46
Скажите, а как настраивается количество бекапов? Ну то есть, чтобы был не всегда только последний полный бекап, а к примеру, несколько бекапов, Каждый из которых можно было бы восстановить?
28. Андрей Старченко (dr.death) 12.10.15 05:30
(27) По умолчанию так и будет, а вообще в плане обслуживания (недельном или дневном) в настройках задачи "Резервное копирование БД", есть настройка что делать, если набор записей не пустой, по умолчанию стоит "Присоединить", т.е. к существующим бэкапам добавиться новый. Можно поставить "Заменить", тогда каждый раз у Вас будет последний бэкап.
Skobuneev; +1 Ответить
29. Гость 10.12.15 11:47
Друзья, посоветуйте.
Если по плану обслуживания по какой нить причине не было сделано бекапирование, как сделать чтобы пришло оповещение на почту об этом.
Необходима жесткая проверка средствами SQL. Ну скажем после работы плана обслуживания запускается проверка существует ли в каталоге с бекапами файлы резервных копий. Интересует вариант исключительно средствами самого SQL возможность такая.
30. Андрей Старченко (dr.death) 11.12.15 05:37
(29) Гость, В редактировании плана обслуживание к задаче "Резервное копирование" нужно добавить задачу "Уведомление оператора" и связать их "красной" стрелочкой (добавляете обычную и по правому клику в контекстном мены выбираете "Ошибка").P.S. нужно добавить в Агенте сервера оператора и настроить e-Mail и/или команду net send