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

В этом руководстве вы узнаете о модели восстановления в SQL Server, включая простую, полную и с неполным протоколированием.

Введение в модель восстановления SQL Server

Модель восстановления — это свойство базы данных. Модель восстановления контролирует следующее:

• Как SQL Server регистрирует транзакции для базы данных.
• Требуется ли резервное копирование журнала транзакций базы данных.
• Какие виды операций восстановления доступны для восстановления базы данных.

SQL Server предоставляет вам три модели восстановления:

• Простой
• Полный
• Массово-зарегистрированный

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

Давайте создадим образец базы данных для демонстрации.

Сначала создайте новую базу данных под названием HR:

CREATE DATABASE HR;

Во-вторых, переключите текущую базу данных на HR:

USE HR;

В-третьих, создайте новую таблицу «Люди» в базе данных HR:

CREATE TABLE People(
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);

Наконец, вставьте несколько строк в таблицу People:

INSERT INTO People(FirstName, LastName)
  VALUES('John', 'Doe'),
 ('Jane', 'Doe'),
 ('Upton', 'Luis'),
 ('Dach', 'Keon');

Ниже показан полный сценарий:

CREATE DATABASE HR;
GO
USE HR;
CREATE TABLE People(
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);
INSERT INTO People(FirstName, LastName)
  VALUES('John', 'Doe'),
 ('Jane', 'Doe'),
 ('Upton', 'Luis'),
 ('Dach', 'Keon');
SELECT * FROM People;

Просмотр модели восстановления базы данных

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

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
WHERE name = 'HR';

Выход:

База данных HR имеет модель ПОЛНОГО восстановления.

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

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
ORDER BY name;

Изменение модели восстановления

Чтобы изменить модель восстановления на другую, используйте следующий оператор ALTER DATABASE:

ALTER DATABASE database_name 
SET RECOVERY recovery_model;

В этом заявлении:

• Сначала укажите имя базы данных, для которой вы хотите изменить модель восстановления, после ключевого слова ALTER DATABASE.
• Во-вторых, укажите модель восстановления после ключевых слов SET RECOVERY. Модель восстановления может быть одной из следующих: SIMPLE, FULL и BULK_LOGGED.

В следующем примере модель восстановления базы данных HR изменяется с FULL на SIMPLE:

ALTER DATABASE HR
SET RECOVERY SIMPLE;

Давайте рассмотрим каждую модель восстановления подробнее.

ПРОСТАЯ модель восстановления

В модели восстановления SIMPLE SQL Server удаляет журналы транзакций из файлов журнала транзакций на каждой контрольной точке. Это приводит к относительно небольшим файлам журнала транзакций.

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

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

Модель ПОЛНОГО восстановления

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

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

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

Модель восстановления BULK_LOGGED

Модель восстановления BULK_LOGGED имеет почти такое же поведение, как и модель восстановления FULL, за исключением операций с массовым протоколированием. Например, BULK INSERT плоских файлов в таблицы кратко описаны в файлах журнала транзакций.

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

• Перед периодической загрузкой данных установите модель восстановления на BULK_LOGGED
• Загрузить данные в базу данных
• После завершения загрузки данных установите модель восстановления обратно на ПОЛНУЮ.
• Резервное копирование базы данных

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

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

Краткое содержание

• Модель восстановления — это свойство базы данных, которое управляет тем, как регистрируются транзакции.
• Модель восстановления может быть одной из следующих: SIMPLE, FULL и BULK_LOGGED.
• Используйте ПРОСТУЮ модель восстановления для баз данных, данные которых можно перезагрузить из других источников.
• Используйте модель ПОЛНОГО восстановления, если вы хотите восстановить базу данных в любой момент времени.
• Используйте модель восстановления BULK_LOGGED для операций с массовым протоколированием, таких как BULK INSERT.