Стратегия резервного копирования базы данных для MS SQL Server

Типы резервного копирования SQL Server

Тип резервной копии	Описание
Полная	Все файлы данных и часть журнала транзакций
Журнал транзакций	Любые изменения базы данных, записанные в файлах журнала
Заключительные фрагменты журнала	Активная часть журнала
Разностная	Части базы данных, которые изменились с момента выполнения полного резервного копирования базы данных
Файл (файловая группа)	Указанные файлы или файловые группы
Частичная	Первичная файловая группа, все файловые группы, доступные для чтения и записи, и любые указанные файловые группы, доступные только для чтения
Доступная только для копирования	База данных или журнал (не оказывается влияния на последовательность резервного копирования)

В SQL Server предоставляется несколько методов резервного копирования для удовлетворения требований всевозможных сфер бизнеса и разнообразных применений баз данных.

Полные резервные копии

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

• резервное копирование всех данных в базе данных;
• резервное копирование всех изменений, которые возникают во время выполнения резервного копирования;
• резервное копирование всех транзакций, не зафиксированных в журнале транзакций.

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

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

Резервные копии журнала транзакций

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

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

При резервном копировании журнала транзакций сервером SQL Server выполняется следующее:

• Создаются резервные копии журнала транзакций от последней успешно выполненной инструкции BACKUP LOG до конца текущего журнала транзакций.
• Усекается журнал транзакций до начала активной части журнала транзакций, и отбрасываются сведения в неактивной части.

Активная часть журнала транзакций начинается с момента самой последней открытой транзакции и продолжается до конца журнала транзакций.

Резервные копии заключительных фрагментов журнала

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

Разностные резервные копии

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

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

Резервные копии файлов и файловых групп

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

• Создаются резервные копии только файлов базы данных, которые указаны в параметре FILE или F1LEGROUP.
• Разрешается резервное копирование конкретных файлов базы данных вместо всей базы данных.

При создании резервных копий файлов и файловых групп необходимо:

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

Частичные резервные копии

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

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

Резервные копии данных, доступных только для копирования

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

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

Разностные резервные копии данных, доступных только для копирования, идентичны обычным разностным резервным копиям.

Примечание.
Резервные копии данных, доступных только для копирования, могут создаваться и восстанавливаться с помощью инструкций BACKUP и RESTORE языка программирования Transact-SQL. Эти резервные копии не поддерживаются средой SQL Server Management Studio.

Что такое модели восстановления?

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

В SQL Server имеется три модели восстановления базы данных: простая, полная и с неполным протоколированием. Каждая из моделей сохраняет данные в случае сбоя сервера, но между моделями существуют основные различия в восстановлении данных сервером SQL Server.

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

Простая модель восстановления

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

Полная модель восстановления

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

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

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

Модель восстановления с неполным протоколированием

Подобно полной модели восстановления, в модели восстановления с неполным протоколированием для восстановления базы данных используются резервные копии как базы данных, так и журнала. Однако в модели восстановления с неполным протоколированием требуется меньше места для следующих операций: CREATE INDEX, операции массовой загрузки, SELECT INTO, WRITETEXT и UPDATETEXT. Вместо хранения в журнале сведений об операциях в нем отмечается только наличие этих операций в виде разрядов в экстентах.

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

С помощью этой модели можно восстанавливать все данные, но невозможно восстановить только часть резервной копии, например выполнить восстановление до определенной метки.

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

• Полное резервное копирование выполняется, если:
  • База данных имеет небольшой размер
  • База данных подвергается незначительным изменениям или доступна только для чтения
• Следует периодически очищать журнал транзакций, если используется полная модель восстановления

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

Когда следует применять стратегию полного резервного копирования базы данных?

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

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

Управление журналом транзакций

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

• Установить простую модель восстановления базы данных.
• Периодически очищать журнал транзакций с помощью параметра NO_LOG или TRUNCATE ONLY инструкции BACKUP LOG.

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

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

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

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

Что такое стратегия резервного копирования базы данных и журнала транзакций?

• Следует объединить резервное копирование базы данных и журнала транзакций, если:
  • База данных часто изменяется
  • Полное резервное копирование занимает слишком много времени

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

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

Когда следует использовать стратегию резервного копирования базы данных и журнала транзакций

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

Что такое стратегия разностного резервного копирования?

• Разностное резервное копирование следует использовать, если:
  • База данных часто изменяется
  • Необходимо сократить время резервного копирования
• Резервное копирование журналов транзакций выполняется отдельно

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

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

 

Когда следует применять стратегию разностного резервного копирования?

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

Что такое стратегия резервного копирования файлов и файловых групп?

• Файлы или файловые группы следует использовать, если:
  • База данных имеет большой размер
  • Полное резервное копирование занимает слишком много времени
• Резервное копирование журналов транзакций выполняется отдельно
• Возможны сложности с управлением

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

Когда следует использовать стратегию резервного копирования файлов и файловых групп?

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

Операторы резервного копирования

• Разрешение на выполнение резервного копирования базы данных имеют члены следующих ролей:
  • sysadmin
  • db_owner
  • db_backupoperator

Для резервного копирования базы данных SQL Server требуются специальные права. Необходимо внимательно проанализировать, кому разрешается выполнять резервные копирования. Можно создавать резервные копии баз данных с помощью SQL Server Management Studio или путем выполнения инструкций языка программирования Transact-SQL.

Кто выполняет резервное копирование?

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

• фиксированной серверной роли sysadmin;
• фиксированной роли базы данных db_owner;
• фиксированной роли базы данных db_backupoperator. Члены роли dbbackupoperator имеют разрешения, перечисленные в следующей таблице.

Роли sysadmin и db_owner наделяются особо привилегированными правами. Членам этих ролей разрешается выполнять многие дополнительные задачи, которые могут влиять на целостность и безопасность базы данных и сервера. Путем включения пользователя в члены роли db_backupoperator ему предоставляются средства, позволяющие выполнять резервное копирование баз данных, но при этом ему не разрешается выполнять другие задачи, для которых требуются особо привилегированные права.

Уровень сервера	Уровень базы данных
Просмотр любой базы данных	Резервное копирование базы данных
	Резервное копирование журнала
	Контрольная точка

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

Резервные носители

• SQL Server поддерживает следующие носители:
  • Лента
  • Диск
• Устройство резервного копирования
  • Физическое хранилище резервных файлов
• Резервный набор данных
  • Резервное копирование на одно или несколько устройств

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

Носители, поддерживаемые SQL Server

Резервное копирование может выполняться сервером SQL Server в файл на жестком диске или на ленту. Дисковые файлы (локальные или сетевые) являются наиболее распространенными носителями, используемыми для хранения резервных копий. Когда выполняется резервное копирование на ленту, накопитель на магнитной ленте должен быть локально подсоединен к SQL Server.

Что такое устройство резервного копирования?

Первый шаг резервного копирования состоит в создании файлов резервных копий, которые будут содержать архив. Файл резервной копии, создаваемый до того, как он будет использоваться для операции резервного копирования, называется устройством резервного копирования. Устройства резервного копирования можно создавать с помощью SQL Server Management Studio или путем выполнения системной хранимой процедуры sp_addumpdevice.

Хранение резервных копий в нескольких файлах

Сервером SQL Server может одновременно (параллельно) вестись запись в несколько файлов резервных копий. Когда имеется несколько файлов резервных копий, данные распределены по всем файлам, которые используются для создания резервной копии. В этих файлах хранится разбитый на части резервный набор данных. Резервный набор данных является результатом одиночной операции резервного копирования, выполняемой над одним или несколькими файлами.

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

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

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

Примечание.
Если используется несколько устройств, каждому файлу резервной копии назначается семейство, например Семейство 1, которое идентифицирует устройство, создавшее файл.

Параметр MEDIANAME команды BACKUP задает имя для всего набора резервных носителей. Когда резервное копирование базы данных выполняется в несколько файлов, следует использовать параметр MEDIANAME. С помощью параметра MEDIANAME несколько файлов связываются друг с другом в качестве членов набора носителей.

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