Резервное копирование – задача номер один, которую должен выполнять каждый уважающий себя администратор.
Как известно, люди делятся на тех, кто делает бэкапы, и тех, кто будет их делать.
К сожалению зачастую многие специалисты делают резервное копирование не средствами СУБД, а используют выгрузку информационной базы в dt файл или простое копирование файлов базы данных.
В этих случаях нельзя сделать бэкап, не прерывая работы пользователей.
При этом получившийся файл копии, даже с учетом сжатия будет довольно большим, что увеличивает стоимость хранения данных. Кроме этого, нет возможности восстановить состояние системы на произвольный момент времени (восстановление возможно только на момент создания копии).
В данной статье мы рассмотрим возможности создания различных типов бэкапов на примере MS SQL Server.
Вы узнаете:
- Какие типы бэкапов бывают и чем они отличаются
- Что такое модель восстановления и какое это имеет отношение к бэкапам
- Когда и какую модель восстановления данных нужно использовать
- Как разрабатывать стратегию резервного копирования
- Как часто создавать бэкапы, где их хранить и что делать в случае сбоя
Резервное копирование в MS SQL Server
Смотрите другие материалы по оптимизации:
Курс по технической настройке 1С и повышению стабильности.
Поддержка – 3 месяца. Объем курса – 35,5 учебных часов.
Не откладывайте свое обучение!
Статья в блоге разработчиков “How we made backups faster with SQL Server 2017” в которой интересные картинки – показано что выполняется при создании бэкапа базы (backup flow in SQL Server) https://blogs.msdn.microsoft.com/sql_server_team/how-we-made-backups-faster-with-sql-server-2017/
Нажмем ОК. Далее стоит добавить задание задачу Резервное копирование базы данных . Так же щелкнув на добавленном задании, увидим опции настройки задания.
Добрый день!
Раз в день делаю полное резервное копирование, размер архива небольшой всего 1,5 Г. Решил попробовать сделать резервное копирование транзакций. Размер при каждом архивировании стабильно получается 7,7 Гб, что в несколько раз больше полного архива. Это как такое может быть?
База занимает 6,6 Гб,лог 38 Гб.
38 Гб логов при размере базы в 6 Гб это мощно -)
Напишите по шагам как именно вы делаете копирование.
Ничего особенного,в 21:30 делается полное резервное копирование и очистка после обслуживания.
Ночью делаются регламентированные задачи:
1. Проверка целостности базы
2. Реорганизация индекса
3. Обновление статистики
4. Очистка кеша.
Любопытно,что после резервного копирования лог увеличивается еще так на 2,5 гб.
Лог уменьшил, переведя базу в простой режим,сжал лог и вернул в полный.
Вот так удаленно подсказать не смогу, надо подключаться смотреть на месте.
Отдельное спасибо авторам за наглядное понятное изложение без лишних технических подробностей! Очень полезно новичкам, чтобы быстро понять что к чему.
Есть один неприятный момент, при Полной модели восстановления базы,
транзакшен лог растет и может достигать значительных размеров
(интенсивная работа, обмены, изменение конфигурации), а усечение его сделать нельзя. приходится раз настраивать сценарии на выходные переводящий базу простую модель восстановления, усечение транзакшен лога и обратно в полную модель.
Что бы лог не рос, нужно регулярно делать бэкап этого лога, тогда и проблем не будет.
конечно, журнал не может не расти когда его размер меньше, чем размер перестраиваемого индекса (для примера) – и это не зависит от значения параметра recovery model. Часто админ базы сам провоцирует сервер на увеличение файла журнала.
Задумайтесь над вопросом: зачем я стремлюсь к маленькому размеру файла журнала?
Ответ прост – потому что я не понимаю “зачем мне это?”, но мне так нравится.
вот эта последовательность действий:
1)переключение recovery model=simple
2)пересоздание журнала, с целью уменьшить его размер
3)переключение recovery model=full
должна заканчиваться еще одним пунктом:
4)сразу после пункта 3) сделать FULL BACKUP иначе журнал будет продолжать работать также как и в режиме recovery model=simple
ИМХО, журнал сразу следует создавать такого размера, чтобы дальнейший его рост был крайне редким событием. Даже если его размер будет близок к размеру всей базы.
Можно создать alert для монитора значения “% использованного места в журнале” и в случае превышения порога запускать автоматический BACKUP LOG.
И действия 1) – 4) уже не понадобятся.
пример:
alert для монитора значения «% использованного места в журнале» и автоматический BACKUP LOG
http://colleenmorrow.com/2011/11/10/creating-a-transaction-log-full-alert/
потерялись угловые скобки, читайте так “Если recovery model НЕ simple, то Log Truncation не будет составной частью checkpoint-а”
хочу развеять миф про “Простую модель восстановления (Simple)” т.к. непонимание – обычное следствие неудачной документации.
в PDF-файле стр.12 параграф “Простая модель восстановления (Simple)” содержит три ошибочных предложения:
1)Когда файл журнала транзакций будет заполнен на 70%, система начнет писать данные в начало файла, затирая самые ранние данные.
2)При такой модели восстановления файл журнала транзакций будет занимать минимальный объем на диске, но не будет хранить никакой истории транзакций.
3)Так как история транзакций не хранится, то нет смысла делать бэкапы логов.
замечание по пункту 1) – это неверно.
замечание по пунктам 2) и 3) – журнал ВСЕГДА будет хранить минимально необходимую информацию для выполнения RECOVERY (см.определение чуть ниже) после падения. Когда база работает в режиме recovery model=simple, то выполнить команду BACKUP LOG откажется само database engine потому, что оно, а точнее – разработчики, знает, что это лишено смысла т.к. DBA осознанно от этого отказался (BTW, у Oracle DB Server есть аналогичный режим=NOARCHIVELOG), но не потому, что журнал ничего не хранит.
ГЛАВНОЕ назначение журнала транзакций: научно говоря, реализация принципа DURABILITY (последняя буква в аббревиатуре ACID) транзакций, или другими словами, по-смыслу происходящего, сохранять информацию, необходимую для восстановления транзакционной целостности файлов базы данных в случае аварийного завершения работы SQL сервера, назовем эти действия RECOVERY. Есть у журнала транзакций и другие назначения кроме ГЛАВНОГО.
из документации (“Журнал транзакций” https://msdn.microsoft.com/ru-ru/library/ms190925%28v=sql.120%29.aspx) – другие цели, вовлекающие использование журнала транзакций:
The transaction log supports the following operations:
1)Recovery of individual transactions.
2)Recovery of all incomplete transactions when SQL Server is started. (должна бы стоять на первом месте, я называю ее ГЛАВНОЙ)
3)Rolling a restored database, file, filegroup, or page forward to the point of failure.
4)Supporting transactional replication.
5)Supporting high availability and disaster recovery solutions: AlwaysOn Availability Groups, database mirroring, and log shipping.
6)еще знаю CDC=”change data capture” – нужен для построения online бизнес-аналитики (BI) она же “система корпоративной отчетности”
7)еще и это надо – создание “горячих” полных бэкапов
Все записи в журнале имеют колонку LSN (LSN=”log sequence number”). LSN можно считать “уникальным идентификатором (счетчиком) события в базе”.
Журнал в MSSQL физически размечен на блоки, которые логически организованы по принципу кольца. Для простоты изложения, журнал можно представлять таблицей, организованной как кольцо (начало соединилось с концом), или закольцованным связным списком (для тех кто знает шо-цэ-таке).
На этом кольце есть непрерывный сектор “актуальных записей, необходимых для RECOVERY”; в процессе работы SQL-сервера положение сектора на кольце и его размер динамически меняется, остальные записи в журнале уже не нужны для RECOVERY. Сектор “актуальных записей” имеет LSN значения начальной (LSN_1, хвост) и последней записей (LSN_2, голова). Примерно каждые 90сек (интервал может быть изменен) в базе активируется процесс checkpoint и происходит одноименное событие в базе. checkpoint выполняет некоторые действия и двигает сектор “актуальных записей” по кольцу т.е. может увеличить LSN_1 и/или LSN_2 вследствие произведенных действий. Если сектор “актуальных записей” займет все кольцо целиком, то либо database engine увеличит файл журнала (если DBA это разрешает), создав в журнале свободные места для новых записей, и затем продолжит работу, либо остановит работу сервера (если DBA не разрешает увеличивать журнал).
ГЛАВНОЕ ОТЛИЧИЕ recovery model=simple от других моделей: database engine в конце выполнения каждого checkpoint-а знает о том в каком виртуальном блоке журнала нет “актуальных записей” и сразу помечает этот блок свободным для повторного использования (это действие называется Log Truncation), если этот блок не нужен для других 6-ти целей. Если recovery modelsimple, то Log Truncation не будет составной частью checkpoint-а т.е. checkpoint НИКОГДА не будет выполнять Log Truncation, последнее будет выполняться ТОЛЬКО в процессе выполнения команды BACKUP LOG.
Если recovery model=simple, то в некоторых ситуациях журнал базы может увеличиваться (если DBA это разрешает).
статья “Журнал транзакций” https://msdn.microsoft.com/ru-ru/library/ms190925%28v=sql.120%29.aspx параграф “Factors That Can Delay Log Truncation” перечисляет 14 случаев, когда Log Truncation откладывается чтобы удерживать в журнале необходимую инфу.
Очень понятный пример – освобождение блоков в журнале транзакций может задержать длинная открытая транзакция:
log_reuse_wait=4
log_reuse_wait_desc=ACTIVE_TRANSACTION
Description: A transaction is active (all recovery models).
A long-running transaction might exist at the start of the log backup.In this case, freeing the space might require another log backup.
Примечание: Long-running transactions prevent log truncation under ALL RECOVERY MODELS, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.
Если что непонятно изложил – извините.
Алексей, спасибо за такой подробный комментарий :)
Для начала необходимо отметить что у меня нет цели детально изложить все нюансы работы журнала, рассказать во всех подробностях как происходит резервное копирование, какой байт куда записывается и т.д. Я не готовлю людей к сертификации MCSE. Моя цель максимально упростить информацию, сделать так что бы сложные вещи были объяснены максимально простым языком.
Упрощать сложно, а усложнять очень просто.
Цель статьи дать основы, о чем и говорится в самом начале этой статьи. Главное что бы люди после прочтения поняли сам принцип работы и понимали почему резервное копирование происходит именно так.
Теперь по вашим замечаниям:
> замечание по пункту 1) — это неверно.
Документация с вами не согласна https://msdn.microsoft.com/ru-ru/library/ms189573(v=sql.120).aspx
Цитата: Кроме того, в простой модели восстановления автоматическая контрольная точка становится в очередь, если журнал заполняется на 70 процентов.
> 2) и 3) — журнал ВСЕГДА будет хранить минимально необходимую информацию для выполнения RECOVERY
Все верно, но этой информации не будет достаточно что бы восстановить базу например на состояние недельной давности, именно это я понимаю под историей. Конечно часть информации журнал хранит, с этим никто не спорит.
По поводу остального я уже написал, моя цель упрощение, а не усложнение.
А почему не уделяется вниманию организацию надежной системы резервного копирования ФАЙЛОВЫХ баз? Как правильно организовать это копирование, ведь очень много нюансов есть?
В данном курсе рассматривается в основном клиент-серверный вариант работы, ведь курс ориентирован в первую очередь на большие многопользовательские системы. Думаю для файловых баз выбор небольшой, наверно удобнее всего сделать задачу на автоматическое архивирование файла самой базы, естественно нужно проследить что бы с базой в это время никто не работал.
Вот и хотелось бы , чтобы рассказали про средства как автоматически организовать весь этот процесс, а не вызванивать в полночь всех сотрудников кто забыл выйти из базы и т.п.
Еще раз повторю, данный курс ориентирован на клиент-серверный вариант.
Ваш вопрос можно рассмотреть, но только в рамках мастер-группы.
Да я понял, я просто перешёл на эту статья из вашей рассылки с темой ““Умерла база, что делать??” – Как в принципе избежать таких ситуаций” и там не было увязки вопроса с резервным копированием с курсом по оптимизации , поэтому и написал.
Есть курс по администрированию, там рассмотрены вопросы резервного копирования не только для PostgreSQL но и для файловой базы.
http://курсы-по-1с.рф/deploy-admin-support-1c/
Спасибо! Очень интересный материал. Но хотелось бы еще о резервном копировании для PostgreSQL, т.к. он бесплатен.
Недавно я смотрел на ютубе запись “018. Базы данных: PostgreSQL – Владимир Бородин” . Владимир Бородин доходчиво объясняет что происходит внутри PostgreSQL. И тут “Как перестать беспокоиться и спать спокойно” он же объясняет как PostgreSQL выполняет полный горячий бэкап базы и зачем нужно верифицировать полный бэкап методом восстановления базы из него.
В добавок рекомендую посмотреть “История небольшого успеха с PostgreSQL” там на 32мин рассказывает как они проверяют бэкапы
Резервное копирование PostgreSQL рассмотрено в курсе по администрированию.
http://курсы-по-1с.рф/deploy-admin-support-1c/
Если восстановить базу из полного бэкапа, то дальше бэкап лога будет накатываться не с начала архива, а как-то сам определит то место, в нем, с которого начинаются не произошедшие (на момент бэкапа) транзакции?
Да это происходит автоматически.
Описание полной резервной копии: “Следует учитывать, что в бэкап попадают данные, которые были в базе данных на момент начала создания бэкапа. Если во время создания бэкапа в базе данных было что-то изменено, то это изменение в данный бэкап не попадет.” Встречал информацию, что если делать полную резервную копию, то все изменения, которые будут произведены после начала создания бэкапа и до его завершения, попадут в полный бэкап. Так ли это?
Спасибо за статью! Очень познавательно :)
В MS SQL Server не попадает, по крайней мере в моих опытах. Как в других СУБД не могу сказать.
Оч.схематично: полный бекап в любых СУБД (Oracle,MSSQL,PostgreSQL,IBMDB2) будет состоять из двух фрагментов: 1-ый – это снимок всех файлов данных(табличных пространств, *.MDL и *.NDL,…) и 2-ой – это часть журнала ВСЕХ транзакций с момента времени Т1 по Т2, где Т1 – момент начала записи 1-го фрагмента, Т2 – момент окончания записи 1-го фрагмента. Восстановление базы всегда состоит из двух этапов – RESTORE и RECOVERY. Задача этапа RESTORE – восстановить все физические файлы данных из снимков (простым копированием из 1-го фрагмента). Задача этапа RECOVERY – восстановить транзакционную целостность файлов данных (по информации из 2-го фрагмента накатить завершенные транзакции и откатить незавершенные), после этого у всех файлов данных будет в заголовке одинаковый SCN,LSN,…(не знаю как они называтся в случае PostgreSQL и IBMDB2).
Если транзакция завершилась в интервале между Т1 и Т2, то ее данные увидите в востановленной из бэкапа базе.
Спасибо за подробности.
Очень понравилась статья :) Правда у меня есть один вопрос. Если есть журнал транзакций, то теоретически можно откатиться на любой момент времени, даже не делая резервное копирование журнала, верно? Или такого инструмента просто нет в MS SQL, чтобы делать подобный откат?
Мне такой инструмент не известен :)
возможно, что TeMochkiN наслышан о Flashback, реализованном в Oracle Database Server. Штука эта приятная, но Oracle DB Server Enterprise Edition стоит = 47500 USD * (число лицензируемых ядер CPU). Число лицензируемых ядер CPU может быть меньше чем число всех ядер CPU, но цена все равно кусается.
Кажется, я нашел то что нужно) теперь буду знать, что откатить журнал транзакций возможно тоже только при наличии резервной копии) https://technet.microsoft.com/ru-ru/library/ms190244(v=sql.105).aspx
Вы спрашивали можно ли восстановить базу на момент времени без резервной копии, я ответил что о такой возможности мне неизвестно.
А то что вы написали сейчас, это восстановление на момент времени как раз из резервной копии, этот момент рассматривается в курсе.
Извините, не ту ссылку прикрепил)
https://msdn.microsoft.com/ru-ru/library/ms179451(v=sql.120).aspx#Anchor_2
там написано: “Восстановление на момент времени всегда производится из резервной копии журналов.”
вот ответ на мой вопрос)
Возможность восстановить базу из бэкапа трудно переоценить, на иноязычном сайте видел алгоритмическую блок-схему действий DBA по восстановлению базы из бэкапа в разных ситуациях, так вот в той схеме все случаи невозможности восстановить базу заканчивались пунктом “обновить резюме и переехать для поиска работы в другой город” (В.С.В.:”жизнь сама таких накажет строго”).
:))
Спасибо, очень познавательно, но, как мне кажется, на стр. 14 закралась ошибка: также можно восстановить систему на 00:00 вторника, т.к. имеются полный бэкап и два разностных. Или я неправильно понял?
Да действительно можно восстановиться на вторник используя разностные бэкапы. Спасибо за замечание, исправим.
Надо накатить ))
Ок :)
Но главное, чтобы не как у админов – вчера пили пиво, меняли пароли… :))
Главное в правильной последовательности ;)
Если честно, то не совсем понятно как эта статья поможет в случае “Но, как назло, буквально 3 дня назад они “сломались”.”
В остальном хорошая статья о резервном копировании MS SQL и его типах резервных копий.
Вот только в большинстве случаев все-таки делать полный бэкап каждый день с добавлением его в архив не составляет друга. Базы с приростом в 10Гб в день скорее крайняя редкость, чем данность. Для таких баз конечно же разностная резервная копия – единственный выход.
И по модели восстановления – рекомендовать полную модель восстановления имеет смысл только в случае настройки резервного копирования логов (простыми словами – только если специалист имеет представление что это и с чем его “есть”). Иначе можно легко при очередном обновлении базы столкнутся с остановкой MS SQL сервера – на диске просто напросто не останется свободного места. Для человека, не знакомого с MS SQL – задачка будет та еще, т.к. загрузится в этой ситуации сервер уже не сможет нормально.
Делать полный бэкап конечно можно и каждый день и даже каждый час. Но если база весит более 100 Гб это будет не очень рационально. Для небольших баз, конечно можно использовать ежедневный полный бэкап.
Одной из задач главы по резервному копированию, как раз и является восстановление на определенный момент времени в том числе и на момент перед сбоем, а для этого без бэкапа логов не обойтись. Что бы свободное место на диске не заканчивалось как раз и нужно делать регулярный бэкап лога, тогда он не будет разрастаться до таких размеров что сможет занять все свободное место. Простая модель восстановления хорошо подходит для различных технических и тестовых баз, но для «боевой» базы, лучше использовать полную модель.
статья была о том как ЗАСТРАХОВАТЬСЯ, читай – “постелить соломки там где можно упасть и разбиться”, а не о том “что делать когда почки уже отвалились? надо пить ли боржоми?”
О, я знаю миллионы случаев, когда от незнания механизмов сисадмины держали полную модель, и в момент дефицита свободного места переключали в симпл, обрезали логи, переключали обратно. При этом никто никогда не применял восстановление на определенный момент времени. Ибо не знал, что так можно :)
>Дмитрий Трифонов 01.02.2016 О, я знаю миллионы случаев, когда от незнания механизмов сисадмины держали полную модель, и в момент дефицита свободного места переключали в симпл, обрезали логи, переключали обратно. При этом никто никогда не применял восстановление на определенный момент времени. Ибо не знал, что так можно :)
Я тоже усекаю логи переводом в симпл, потому что даже при бэкапе лога – он не усекается, хоть убейся.