По материалам статьи Joe Lax на swynk.com: «Using SQL Server 2000 Recovery Models»

В этой статье подчёркивается, что прикладные системы требуют разных подходов к стратегии резервирования, в зависимости от критичности информации в СУБД. В SQL Server 2000 стратегии резервирования реализованы так, что выбор одной из моделей "recovery models" поможет Вам легко классифицировать ваши задачи резервирования и существенно упростит ваш план резервного копирования.
Для выбора моделей резервирования/восстановления необходимо выполнить несколько предварительных шагов, которые помогут Вам сделать правильный выбор:
- Проанализируйте, как часто Ваши данные изменяются в базе данных и на сколько ценный является информация в них.
- Определите, должны ли будут выполняться операции Bulk Copy или SELECT INTO.
- Определите, нуждаетесь ли Вы в восстановлении "point-in-time" (на заданный момент времени).
- Исходя из вышеперечисленных пунктов, выберите recovery model, который является наиболее приемлемой.
- Установите для базы данных выбранную модель, используя команду ALTER DATABASE.

Какие существуют модели восстановления?
Хотя большинство приложений используют разные структуры баз данных, когда дело сводится к резервному копированию, используются схожие схемы. Например, многие DBA имеют тестовые базы данных, которые не обязательно часто резервировать. Аналогично, многие из Вас работают с системами, где важна каждая минута работы пользователей и должна быть обеспечена высокая доступность и готовность данных. Для каждой из этих ситуаций, должны примениться различные стратегии резервирования.
Чтобы сделать резервное копирования проще в использовании, Микрософт сгруппировал различные стратегии в три стереотипа или модели: Простая (Simple), Полная (Full), и Bulk-Logged. Эти, присущие SQL Server 2000 модели не только помогут Вам хорошо обдумать ваши потребности, но и упростят поддержку соответствующих стратегий.

Принципы работы журнала регистрации транзакций и Bulk Copy

Прежде, чем рассматривать модели восстановления, обратимся к принципам функционирования двух связанных между собой механизмов SQL Server: transaction log и Bulk Copy utility. Применение моделей восстановления соответствующих этим двум механизмам, основано на одном из основных различий между ними.
Каждая база данных должна иметь, по крайней мере, один файл transaction log. Когда происходят изменения информации в базе данных, эти изменения отражаются сначала в transaction log прежде, чем транзакция будет считаться завершённой. Изменения будут записаны непосредственно в базу данных, только когда отработает очередной "checkpoint". В течение исполнения checkpoint, все завершённые транзакции отражаются в базе данных. Для каждой базы данных SQL Server периодически инициализирует checkpoint автоматически с заданным в настройках сервера интервалом.
SQL Server использует transaction log вместе с другими механизмами, что бы гарантировать целостность транзакций и защитить их от сбоев и отключений питания. Transaction log также позволяет прерывать и откатывать назад транзакции прежде, чем они будут завершены.
Представим себе бизнес-транзакцию, которая перемещает денежные средства с лицевого счёта клиента на его депозитный вклад. Транзакция должна включать две отдельные операции, дебет лицевого счета и кредит депозитного. Если бы изменения, предполагаемые этой транзакцией, сразу записывались непосредственно в базу данных, сбой операции между дебетом и кредит привёл бы к расхождениям в затронутых счетах. Вместо этого, изменения сначала записывается в transaction log, и только после того, как обе операции будут успешно зарегистрированы в журнале, и транзакция завершится, изменения будут отражены в базе данных.
SQL Server поставляется с утилитой (программа массового копирования bulk copy) которая очень полезна при загрузке большого количества данных из файла в базу. Одна из особенностей, которая позволяет этой утилите загружать данные быстрее, чем посредством инструкции INSERT, это её работа в non-logged mode (без регистрации в журнале транзакций). В этом режиме, каждая вставленная строка не будет записана в transaction log, что существенно ускоряет эту операцию. (Точно так же, модификация текстовых полей может быть зарегистрирована или наоборот, проведена в non-logged режиме).

Модели восстановления SQL Server 2000

SQL Server 2000 имеет три модели: Простая (Simple), Полная (Full), и Bulk-Logged.

Простая

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

Полная

Полная модель позволяет создавать не только полные резервные копии базы данных, но и последовательные, промежуточные резервные копии изменений, которые произошли начиная с последнего, полного резервного копирования. Дополнительной выгодой от этой модели является возможность восстановления базы данных на заданное время. Например, если пользователь случайно удалит все учетные записи в базе данных в 13:00, возможно восстановить базу данных на момент 12:59, приведя её в состояние, предшествующее удалению учетных записей. При выборе этой модели, место в transaction log высвобождается только когда будет сделано резервное копирование transaction log. Когда это происходит, все изменения, зарегистрированные в transaction log, будут записаны в резервную копию, и занимаемое ими в журнале место освободится. Поэтому базы данных, эксплуатируемые в этом режиме, должны иметь достаточно места, доступного для transaction log, чтобы хранить все транзакции, которые исполняются между каждым резервным копированием. Кроме того, не допускаются не регистрируемые операции.

Bulk-Logged

Bulk-Logged модель находится по смыслу между уже представленными двумя моделями. С одной стороны, становятся возможны последовательные резервные копии базы данных и transaction log обрабатывается также, как в полной модели. Однако, массовые операции копирования регистрируются только по минимуму. Вместо регистрации каждой вставки в таблицу, SQL Server регистрирует необходимый минимум для восстановления данных, если такое резервирование необходимо. Однако, из-за этого, если будет выполнена массовая операция копирования, восстановление point-in-time станет невозможно.

Использование моделей восстановления SQL Server 2000

При выборе модели, которая наибольшим образам соответствует Вашим потребностям, ответьте на следующие вопросы:
Когда данные изменяются в Вашей базе данных? Если изменения происходят только в результате контролируемых Вами операций, тогда простая модель могла бы быть самым легким способом резервирования. Вы будете должны выполнять полное резервное копирование только после того, как внесёте изменения. Например, если ваши данные загружаются в базу данных каждый вечер, эта модель будет самая простая и позволит быстро загружать данные при использовании Bulk Copy.
Однако, если каждое изменение в базе данных является важным и такие изменения происходят непрерывно в течение дня, полная модель будет больше соответствовать Вашим требованиям к защите критически – важных данных. Эта модель применяется в большинстве систем, которые выполняют бизнес - операции. В этом случае, Вы должны выполнить следующие шаги перед выбором подходящей для конкретного случая модели:
Нуждаетесь ли Вы в быстрой загрузке данных? Если Вы должны фиксировать все изменения, которые происходят в вашей базе данных в течении дня, Вы имеете выбор между использованием Полной или Bulk-Logged моделями. Обе поддерживают последовательное, периодическое резервное копирования журнала. Если Вы должны быстро загружать данные, используя Bulk Copy, значит лучше выбрать Bulk Copy модель. Иначе, выбирайте полную модель. Однако, перед окончательным принятием решения, Вы должны ответить для себя ещё на один вопрос: Нуждаетесь ли Вы в восстановлении point-in-time? Если Вы должны иметь возможность восстанавливать данные на любое время, Вы должны выбрать только полную модель.

Применение моделей

Теперь, когда Вы выбрали модель, можно применить её к базе данных. Чтобы это сделать, выполните инструкцию ALTER DATABASE:

ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]

Чтобы убедиться в том, что модель была успешно установлена для заданной базы данных, выполните команду:

SP_HELPDB [database name]

Эта хранимая процедура возвращает много информации относительно указанной базы данных. Сведения о recovery model указаны в столбце Status, в котором указано значение RECOVERY=[name of model].

Где теперь находятся параметры

Те из Вас, кто знаком с предыдущими версиями SQL Server, могут задаться вопросом, что случилось с sp_dboption, которая раньше использовалась при настройке для баз данных truncate log on checkpoint и SELECT INTO Bulk Copy. SQL Server 2000 всё еще поддерживает эти параметры для обратной совместимости, но не обещает такой поддержки в будущих версиях. Поэтому, хотя Вы всё еще можете достигать того же самого эффекта, используя sp_dboption, лучше использовать новые модели восстановления. Кроме того, выбирая новым способом recovery model, Вы будете использовать только одну команду, что бы SQL Server установил truncate log и SELECT INTO Bulk Copy. Используя sp_dboption, Вы должны будете выполнить две отдельных команды.

Последний штрих

Выбор модели не подразумевает автоматическую организацию резервного копирования. Вы всё еще должны настроить и спланировать резервирование.

Дополнительную информацию можно получить в Books Online в главе: Backing Up and Restoring Databases.