Основы резервного копирования и восстановления данных

Мишель Пуле (mapoolet@mountvernondatasystems.com)-редактор журнала SQL Server Pro, соучредитель компаний Mount Vernon Data Systems и Six Sigma Uptime.

Большинство компаний, существующих на рынке достаточно давно, переживали катастрофические события, которые могли бы вывести их из игры, например сбой базы данных. Резервная копия базы данных представляет собой копию данных, структур и объектов безопасности, содержащихся в базе данных. Каждая база данных должна резервироваться по своему графику в зависимости от количества выполняемых за день транзакций записи. Для минимизации потерь при сбое базы данных необходимо выполнять резервное копирование всех баз данных, используемых на предприятии. А дабы убедиться, что резервные копии работоспособны, следует проверять их работу после операций восстановления. По меньшей мере, необходимо иметь копии баз данных, пригодные для быстрого восстановления, а сама операция восстановления должна быть отработана и не вызывать никаких трудностей.

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

Не стоит доверять ложному чувству защищенности, возникающему после ввода в эксплуатацию новейшей системы высокой доступности. Если все данные виртуализованы и консолидированы, риски даже возрастают. Как проста была жизнь, когда на одном компьютере выполнялся единственный экземпляр базы данных. Теперь обычно на сервере в виртуальных машинах исполняются десятки экземпляров SQL Server, которые, в случае отказа физического сервера, откажут все одновременно. Если средства позволяют, вы можете создать отказоустойчивый кластер хостов виртуальных машин на разных физических серверах. При необходимости высокой доступности так обычно и делают. Но даже такая отказоустойчивая система может оказаться уязвимой в случае, скажем, пожара, потопа или землетрясения. Резервные копии все равно необходимы. При этом создание резервных копий доверено ограниченному кругу лиц. Более подробно о том, кто имеет право создавать резервные копии, рассказано во врезке «Кто может выполнять резервирование?».

Частота резервирования базы данных зависит от того, как долго она будет восстанавливаться из резервной копии. Чем чаще выполняется резервирование базы данных, тем меньше времени займет восстановление. График резервирования и восстановления можно настроить индивидуально для каждой базы данных. Тип резервирования зависит еще от объема базы данных и количества транзакций, выполняемых за единицу времени. Основными типами резервирования являются полное, журнальное и инкрементальное. Более подробные сведения о режимах восстановления приведены во врезке «Модели восстановления баз данных», команды по резервированию SQL Server описаны во врезке «Стандартные команды для резервирования».

Полное резервирование

Стратегия полного резервирования является самой простой для понимания и реализации. В конце каждого рабочего дня (или в любой другой промежуток времени, который вы можете назначить) просто запускается процедура полного резервирования базы данных (рисунок 1). При этом не нужно выполнять отдельное резервирование журналов и не требуется использовать дополнительные параметры. Управление файлами в таком режиме резервирования также не требует особого внимания, так как речь идет о единственном файле полной резервной копии. Восстановление из полной резервной копии тоже очень простое: необходимо просто восстановление из единственного файла. Использование полных резервных копий – хороший выбор для организаций с недостаточно опытным ИТ-персоналом.

Рисунок 1. Расписание заданий на полное резервирование

Больше всего полное резервирование подходит для «небольших» баз данных – назовем так базы данных, резервирование которых может быть завершено за отведенное для этого время. Когда SQL Server осуществляет полное резервирование базы данных, сначала выполняется сохранение на диск всех экстентов (экстент представляет собой восемь идущих последовательно страниц, размер каждой составляет 8 Кбайт). Затем SQL Server резервирует журнал транзакций, чтобы все изменения базы данных, которые могли произойти за время резервирования, также были сохранены в файле полной резервной копии.

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

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

Для выполнения полного резервирования базы данных выполните следующий код:

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’WITH INIT, NAME = ‘AdventureWorks Full Db backup’,
DESCRIPTION = ‘AdventureWorks Full Database Backup

Параметр DISK определяет целевой файл резервной копии. Вы можете выполнять резервирование на диск или на ленту (в данном случае – на диск). Перед началом резервирования убедитесь, что папка для хранения резервной копии существует. В большинстве случаев резервирование на диск происходит значительно быстрее, чем на ленту, но стоимость дисковой памяти существенно выше. Для обеспечения дополнительного уровня защиты можно выполнять резервирование на диск, а затем сохранять резервную копию на ленту. Параметр WITH INIT указывает, что файл резервной копии должен быть перезаписан. Этот метод подходит в том случае, если резервирование Windows выполняется после каждого резервирования базы данных. NAME – имя резервной копии, до 128 символов. Если имя не указать, поле имени останется пустым. DESCRIPTION – более полное и подробное описание, которое может помочь, например, через длительный промежуток времени выяснить, что это за резервная копия и зачем она была создана.

Для полного восстановления базы данных выполните следующую команду:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.BAK’
WITH RECOVERY, REPLACE

WITH RECOVERY предписывает SQL Server отменить все незавершенные транзакции, которые могли быть в журнале транзакций, и оставить базу в рабочем состоянии. REPLACE означает перезапись любого существующего файла с тем же именем. Более подробно об этом рассказано во врезке «Замена базы данных».

При использовании стратегии полного резервирования необходимо следить за размером файла журнала транзакций. Полное резервирование не осуществляет очистку журнала транзакций от неактивных записей. Если выполнять только полное резервирование базы данных, вслед за этой операцией следует выполнять резервирование файла журнала с очисткой. Для этого используется установка TRUNCATE_ONLY, как в приведенной ниже команде:

BACKUP LOG AdventureWorks
WITH TRUNCATE_ONLY

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

Полное резервирование с сохранением журнала

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

На рисунке 2 приведен пример расписания для полного резервирования с сохранением журнала – еженедельное полное резервирование по воскресеньям и сохранение журнала транзакций в каждый следующий день до следующего воскресенья, когда снова будет выполнено полное резервирование. Резервирование журнала сохраняет все изменения, произведенные с момента предыдущего резервирования журнала. В рассматриваемой схеме планирования происходит сохранение ежедневных изменений.

Рисунок 2. Расписание заданий на полное резервирование с ведением журнала

Если не указано обратное, после завершения резервирования журнала неактивные записи в нем «удаляются» (в действительности они помечаются для перезаписи). При запуске команды BACKUP LOG вы можете добавить параметры NO_TRUNCATE или COPY_ONLY, чтобы при резервировании записи в журнале не изменялись. Но мы не рекомендуем использовать эти параметры, если только вы не знаете наверняка, для чего это может понадобиться.

SQL Server 2005 имеется режим резервирования копии заключительного фрагмента журнала (tail-log backup), то есть резервирование после краха базы данных в том случае, если журнал транзакций не был испорчен. В этом режиме осуществляется резервирование последних транзакций, выполненных с момента последнего резервирования журнала. Более подробно об этом режиме рассказано во врезке «Что такое резервные копии заключительного фрагмента журнала».

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

Если в базе данных массовые обновления носят регулярный характер, возможно, имеет смысл использовать модель восстановления с неполным протоколированием (bulk logged recovery). Поскольку отдельные записи, включенные в массовую операцию в этом случае не журналируются, этот подход сокращает накладные расходы на ведение журнала SQL Server. Хотя вы можете получить заметное увеличение производительности при выполнении массовых операций, вы рискуете потерять данные при восстановлении, если исходные данные для повторного выполнения массовых операций окажутся в момент восстановления недоступны. При применении простой модели восстановления резервирование журнала также невозможно, так как в этом случае происходит обрезание журнала до контрольной точки.

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

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’
WITH INIT, NAME = ‘AdventureWorks Full Db backup’,
DESCRIPTION = ‘AdventureWorks Full Database Backup’

А затем следует выполнить резервирование журнала с помощью команды:

BACKUP LOG AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TlogBkup.bak’
WITH NOINIT, NAME = ‘AdventureWorks Translog backup’,
DESCRIPTION = ‘AdventureWorks Transaction Log Backup’, NOFORMAT

Параметр WITH NOINIT в последней команде указывает, что файл резервной копии должен быть записан в режиме добавления (append) на существующий носитель, диск или ленту. В этом случае все резервные копии журнала транзакций будут дописаны в один и тот же файл один за другим подряд. NOFORMAT предписывает процессу резервирования сохранить всю заголовочную информацию, которая может содержаться на резервных дисках в заголовках. Этот способ принят по умолчанию, и явное указание данной установки является необязательным, но оно полезно в качестве самодокументирования операции.

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

1. Если база данных в состоянии онлайн, ограничьте доступ к ней, переключив режим доступа (в окне свойств) на RESTRICTED_USER. Таким образом доступ к базе данных будет разрешен только членам группы базы данных db_owner и членам групп сервера dbcreator и sysadmin.
2. Выполните резервирование заключительного фрагмента журнала.
3. Исправьте ошибку, вызвавшую крушение базы данных.
4. Выполните восстановление полной резервной копии с параметром NORECOVERY.
5. Если возможно, примените все сохраненные в резервных копиях журналы транзакций с параметром NORECOVERY.
6. Выполните восстановление резервной копии заключительного фрагмента журнала с параметром RECOVERY.

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

BACKUP LOG AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TaillogBkup.bak’
WITH NORECOVER

Для полного восстановления из полной резервной копии необходимо сначала восстановить файлы базы данных с помощью команды:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’
WITH NORECOVERY

Параметр NORECOVERY сообщает SQL Server, что частичные транзакции должны быть оставлены как есть, не нужно пытаться отменить их. При последующем восстановлении журналов транзакций будут восстановлены данные, позволяющие завершить эти частичные транзакции. При использовании параметра NORECOVERY база данных остается в нерабочем состоянии. Сразу за полным восстановлением должны быть восстановлены все резервные копии журналов транзакций с параметром NORECOVERY, как показано ниже:

RESTORE LOG AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TlogBkup.bak’
WITH NORECOVERY

Наконец, выполните восстановление заключительного фрагмента с параметром RECOVERY:

RESTORE LOG AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_TaillogBkup.bak’ WITH RECOVERY

Стратегия полного восстановления с восстановлением журналов не является абсолютной защитой. Если одна из резервных копий журналов окажется повреждена, то восстановление будет возможно только до точки, предшествующей поврежденному журналу. Например, предположим, что по воскресеньям выполняется еженедельное полное резервирование, а резервирование журналов – с понедельника по субботу. Если повреждена резервная копия вторника, то восстановлены могут быть только данные понедельника: риск повреждения целостности данных в результате применения транзакций среды к данным понедельника вряд ли оправдан. И восстановление заключительного фрагмента тоже ничего не даст.

Полное плюс разностное резервирование

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

Разностное резервирование имеет накопительный характер – оно включает в себя все данные и структуры, которые были изменены с момента последнего полного резервирования вне зависимости от того, когда осуществлялось последнее полное резервирование и сколько раз с того момента выполнялось разностное резервирование. Предположим, что полное резервирование было выполнено в воскресенье, а разностное резервирование производилось каждый день, как показано на рисунке 3. Разностная копия понедельника будет содержать все изменения, выполненные в понедельник, разностная копия вторника – изменения понедельника и вторника, разностная копия среды – изменения понедельника, вторника и среды и т.д.

Рисунок 3. Расписание заданий на разностное резервирование

Восстановление разностной резервной копии обычно требует меньше времени, чем восстановление полной резервной копии плюс журналы, так как восстановить одну разностную копию получается быстрее, чем цепочку журналов. Сохранение разностной резервной копии выполняется командой:

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_DiffDbBkup.bak’
WITH INIT, DIFFERENTIAL, NAME = ‘AdventureWorks Diff Db backup’,
DESCRIPTION = ‘AdventureWorks Differential Database Backup’

Чтобы восстановить базу данных из разностной резервной копии, выполните следующие шаги.

1. Если база данных в состоянии онлайн, ограничьте к ней доступ, переключив режим доступа (в окне свойств) на RESTRICTED_USER. Тем самым доступ к базе данных будет разрешен только членам группы базы данных db_owner и членам групп сервера dbcreator и sysadmin.
2. Выполните резервирование заключительного фрагмента журнала.
3. Исправьте ошибку, вызвавшую сбой базы данных.
4. Выполните восстановление полной резервной копии с параметром NORECOVERY.
5. Выполните восстановление последней имеющейся разностной резервной копии с параметром NORECOVERY.
6. Выполните восстановление резервной копии заключительного фрагмента журнала с параметром RECOVERY.

Для восстановления разностной резервной копии (выполняется после восстановления полной копии) введите команду:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_DiffDbBkup.bak’WITH NORECOVERY

Затем восстановите заключительный фрагмент журнала с параметром RECOVERY, с помощью приведенной ранее команды.

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

Комбинирование стратегией

Если повторное выполнение транзакций для восстановления операций последнего дня представляется нецелесообразным, вы можете выполнять полное резервирование в воскресенье, разностное резервирование каждую последующую ночь и резервирование журналов транзакций по утрам и вечерам с понедельника по субботу, как показано на рисунке 4. Если в пятницу вечером с базой данных случится беда, а разностная резервная копия четверга окажется поврежденной, можно будет выполнить восстановление по разностной копии среды, а затем применить журналы четверга и пятницы. Таким образом база данных будет восстановлена до самого момента отказа. Более подробно этот вопрос рассматривается во врезке «Как восстановить базу данных по состоянию на заданный момент времени».

Рисунок 4. Расписание заданий на полное, разностное и журнальное резервирование

Для уменьшения риска потери данных следует придерживаться смешанной стратегии, включающей полное резервирование, резервирование журналов и разностное резервирование, хотя такое смешение способов несколько усложняет стратегию резервирования и управление резервными копиями. Следует также оценить, потерю какого объема данных можно допустить после сбоя базы данных и восстановления. Использование вместо простого восстановления стратегий полного восстановления или модели с неполным журналированием влечет за собой больше накладных расходов на операции с файлом журнала транзакций и приводит к созданию больших файлов резервных копий, но обеспечивает более высокую степень сохранности данных.

Альтернативные стратегии резервирования

Резервирование в SQL Server не сводится только к полному, разностному и журналу транзакций. Более сложные стратегии, включающие резервирование файлов или групп файлов, стратегию частичного резервирования и резервирование только путем копирования.

Доступ к базе данных во время выполнения резервирования и восстановления

Резервирование базы SQL Server является онлайн-процессом, все хранящиеся в SQL Server данные во время операции резервирования доступны. Операции изменения базы данных, предложения INSERT, UPDATE и DELETE доступны точно так же, как выборка данных (SELECT). Во время резервирования нельзя изменять структуру базы данных или файловую структуру – предложения ALTER DATABASE, ADD FILE или SHRINKFILE во время резервирования выполняться не могут. Если для базы данных включен режим автоматического запуска уменьшения файла базы данных (auto-shrink), возможен конфликт во время выполнения резервирования. Так, если в процессе выполнения резервирования запустится автоматическое уменьшение файла базы, то обе операции могут завершиться отказом. Та операция, которая стартует раньше, установит блокировку файла, а следующей операции придется ожидать снятия блокировки. Если первая операция снимет блокировку, то начнется выполнение второй. Если же произойдет тайм-аут блокировки первой операции, вторая операция завершится отказом. Такой подход может показаться неправильным с точки зрения исполнения второй операции, которая вынуждена ожидать отказа, и только после него выдаст отказ. Но если учесть, что работа второй операции зависит от успеха первой, если при выполнении первой операции произошел отказ, выполнение второй не имеет смысла. Для предотвращения такой проблемы следует отключать автоматическое уменьшение файла базы данных перед выполнением резервирования.

В большинстве случаев восстановление базы SQL Server является автономной операцией, во время которой доступ пользователей к базе невозможен. При использовании SQL Server 2005 Enterprise Edition с моделью полного восстановления частичное восстановление и восстановление неосновных групп файлов по умолчанию являются онлайн-операциями. Части базы данных, которые не должны восстанавливаться, например группы файлов с доступом только для записи, могут быть доступны пользователям на всем протяжении выполнения операции восстановления. Группы файлов для чтения/записи доступны, если они не были переведены в автономное состояние для восстановления. Эта возможность очень полезна для больших баз данных, работающих в режиме 24x7x365. Дополнительную информацию можно найти в документации SQL Server 2005 BOL, «Performing Online Restores» (http://msdn.microsoft.com/ru-ru/library/ms188671.aspx), а также во врезке «Почему восстановление базы данных не может выполняться онлайн».

Подведем итоги

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

RESTORE DATABASE AdventureWorks
FROM DISK = 'E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak'
WITH NORECOVERY

RESTORE LOG AdventureWorks
FROM DISK = 'E:\SQLdata\BACKUPS\AdventureWorks_TlogBkup.bak'
WITH RECOVERY, STOPAT = ‘ Dec 10, 2007 8:10 PM’

Резервирование файлов/групп файлов

Эта стратегия резервирования подходит только в том случае, если база данных состоит из нескольких файлов или групп файлов. Если размеры базы или требования к производительности делают полное резервирование базы данных невозможным и если необходимо быстрое восстановление в случае отказа, стоит принять во внимание стратегии резервирования файлов/групп файлов.
Эта стратегия может использоваться для SQL Server 2005 или SQL Server 2000, при этом при выполнении каждой операции требуется указать, какие файлы, группы файлов или комбинации будут резервироваться. При этом следует выполнить полное резервирование базы данных вскоре после создания, после чего выполнять регулярное резервирование файлов или групп файлов. Если для конкретной базы данных необходимо задействовать простую модель восстановления, все доступные для чтения/записи файлы и группы файлов должны резервироваться одновременно. Для минимизации потерь данных при восстановлении выбирайте модель полного восстановления или модель восстановления с неполным протоколированием, при этом необходимо включить в стратегию резервирование журнала транзакций.
Восстановление базы все равно означает ограничение доступа к базе данных, но на меньшее время, чем при полном восстановлении базы данных. Во время восстановления доступ ограничивается только к группам файлов, восстанавливаемым в данный момент.
В худшем случае, если требуется восстановление всей базы данных и вы используете модель полного восстановления, потребуются все резервные копии журналов транзакций с момента создания базы данных. Кроме того, если необходимо восстановление базы на определенный момент времени, потребуется полный набор резервных копий журналов транзакций.