Резервное копирование баз данных в MS SQL Server

Резервное копирование базы данных в MS SQL Server — это создание автономной копии данных и журнала транзакций командой BACKUP, из которой базу можно восстановить после сбоя, ошибки или потери. Копия не работает в реальном времени: это снимок на момент бэкапа, а не постоянная синхронная реплика (для нее есть Always On и репликация).

Дальше разберем три вещи, без которых бэкапы в SQL Server не складываются в рабочую схему: модель восстановления базы, типы бэкапов и их цепочку, команды BACKUP/RESTORE, а также как считать RPO/RTO и проверять, что копия рабочая.

Модель восстановления решает, что вообще можно восстановить

Модель восстановления (recovery model) — свойство базы, которое определяет, как ведется журнал транзакций и какие типы бэкапов доступны. Это не то же самое, что тип бэкапа: сначала выбирают модель, и уже она разрешает или запрещает бэкап журнала.

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

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = N'Shop';
-- recovery_model_desc вернет FULL, SIMPLE или BULK_LOGGED

ALTER DATABASE [Shop] SET RECOVERY FULL;

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

Типы бэкапов и в чем разница дифференциального и журнального

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

• Полный бэкап (full) — копия всех данных базы плюс части журнала, нужной для согласованности. Полный бэкап НЕ усекает журнал транзакций (журнал усекает только бэкап журнала в моделях FULL/BULK_LOGGED). База восстанавливается из него за один шаг.
• Дифференциальный бэкап (differential) — экстенты, изменившиеся с момента последнего ОБЫЧНОГО полного бэкапа: именно он служит точкой отсчета (differential base), и полный copy-only эту базу не меняет. Он кумулятивный: каждый следующий дифференциальный включает изменения предыдущего, поэтому для восстановления нужен только полный плюс один последний дифференциальный.
• Бэкап журнала транзакций (log) — записи журнала с момента предыдущего бэкапа журнала. Он инкрементальный: восстанавливать нужно всю последовательность подряд, без пропусков. Именно бэкап журнала дает восстановление на точный момент времени.

Старое обиходное «дифференциальный = инкрементальный» — ошибка: инкрементальна как раз цепочка журнала, а дифференциальный — кумулятивный.

Есть и служебные типы: copy-only (разовая копия, не сбивающая цепочку дифференциальных и журнальных бэкапов), файловые и на уровне групп файлов, частичный. Для типовой базы среднего размера достаточно связки full + differential + log.

RPO и RTO: как эти два параметра задают расписание

RPO и RTO — разные величины, и путать их дорого.

• RPO (recovery point objective) — сколько данных допустимо потерять, измеряется во времени. RPO = 15 минут означает бэкап журнала не реже чем раз в 15 минут.
• RTO (recovery time objective) — за сколько база должна снова работать. RTO влияет не на частоту бэкапов, а на стратегию восстановления: сжатие, скорость дисков, наличие свежего дифференциального бэкапа сокращают время наката цепочки.

Простой ориентир расписания под FULL: полный бэкап раз в сутки, дифференциальный — каждые несколько часов, журнала — под требуемый RPO (например, каждые 10-15 минут). Числа подбирают под объем базы и бизнес-требования, а не по универсальному правилу.

Команды BACKUP: полный, дифференциальный, журнала

Опции CHECKSUM (контроль целостности) и COMPRESSION (сжатие) стоит включать по умолчанию; INIT перезаписывает содержимое файла-приемника. COMPRESSION доступна во всех редакциях начиная с SQL Server 2016 SP1 (раньше — только Enterprise, а с 2008 R2 и Standard), поэтому на старых Express-инсталляциях опцию из примеров ниже нужно убрать.

-- Полный бэкап
BACKUP DATABASE [Shop]
TO DISK = N'D:\backup\Shop_full.bak'
WITH INIT, CHECKSUM, COMPRESSION, NAME = N'Shop-full';

-- Дифференциальный: те же данные, но ключевое слово DIFFERENTIAL
BACKUP DATABASE [Shop]
TO DISK = N'D:\backup\Shop_diff.bak'
WITH DIFFERENTIAL, INIT, CHECKSUM;

-- Бэкап журнала транзакций (только в модели FULL/BULK_LOGGED)
BACKUP LOG [Shop]
TO DISK = N'D:\backup\Shop_log.trn'
WITH INIT, CHECKSUM;

BACKUP выполняется онлайн: обычные чтение и изменение данных во время копирования продолжаются. «Онлайн» не значит «без любых ограничений» — BACKUP несовместим со сжатием базы (DBCC SHRINKDATABASE/SHRINKFILE) и операциями управления файлами и может ждать их завершения, поэтому тяжелые административные работы разводят по расписанию. Отдельно опасны опции, стирающие носитель: FORMAT и INIT перезаписывают существующие наборы. Проверяйте путь-приемник до запуска — FORMAT на боевом файле сотрет все ранее сделанные копии в нем.

Для автоматизации ту же инструкцию удобно вызвать через sqlcmd в планировщике (в командной строке — только ASCII-дефисы в ключах):

sqlcmd -S localhost -E -Q "BACKUP DATABASE [Shop] TO DISK = N'D:\backup\Shop_full.bak' WITH INIT, CHECKSUM"

Права BACKUP DATABASE и BACKUP LOG по умолчанию есть у ролей sysadmin, db_owner и db_backupoperator. Полезный флажок трассировки 3226 отключает запись об успешных бэкапах в журнал ошибок SQL Server, чтобы частые копии не засоряли лог.

RESTORE: цепочка восстановления безопасно, в отдельную базу

Восстановление — разрушающая операция: она перезаписывает базу. Поэтому проверять бэкапы и учиться на них нужно, восстанавливая в ОТДЕЛЬНУЮ базу (Shop_test) через WITH MOVE, не трогая боевую Shop. На всех шагах цепочки, кроме последнего, ставится NORECOVERY — база остается в состоянии restoring и ждет следующий бэкап; RECOVERY на последнем шаге открывает ее.

-- Шаг 1: полный, в отдельную базу, с переносом файлов
RESTORE DATABASE [Shop_test]
FROM DISK = N'D:\backup\Shop_full.bak'
WITH MOVE N'Shop'     TO N'D:\data\Shop_test.mdf',
     MOVE N'Shop_log' TO N'D:\data\Shop_test.ldf',
     NORECOVERY, REPLACE;

-- Шаг 2: последний дифференциальный (кумулятивный, нужен только один)
RESTORE DATABASE [Shop_test]
FROM DISK = N'D:\backup\Shop_diff.bak'
WITH NORECOVERY;

-- Шаг 3: бэкапы журнала по порядку; RECOVERY открывает базу
RESTORE LOG [Shop_test]
FROM DISK = N'D:\backup\Shop_log.trn'
WITH RECOVERY;

Для восстановления на точный момент (например, до ошибочного DELETE) на последнем бэкапе журнала указывают STOPAT:

RESTORE LOG [Shop_test]
FROM DISK = N'D:\backup\Shop_log.trn'
WITH STOPAT = N'2026-09-18T14:30:00', RECOVERY;

Если восстанавливать приходится боевую базу после сбоя, сначала снимают бэкап «хвоста» журнала — транзакций, попавших в журнал после последнего лог-бэкапа. Это шаг с изменением состояния (база переходит в restoring), поэтому его делают осознанно и первым:

-- Спасает последние транзакции перед восстановлением.
-- База переходит в restoring и станет недоступной до наката цепочки.
BACKUP LOG [Shop]
TO DISK = N'D:\backup\Shop_tail.trn'
WITH NORECOVERY;

WITH NORECOVERY подходит, когда база еще онлайн (типичный случай — откат ошибочного DELETE). Если поврежден файл ДАННЫХ, а журнал цел, но база не стартует, обычный бэкап хвоста не пройдет — тогда снимают его в аварийном режиме WITH NO_TRUNCATE (без усечения и без проверки состояния БД). Если же поврежден сам журнал и NO_TRUNCATE не проходит, применяют CONTINUE_AFTER_ERROR: это разные режимы под разное повреждение (взамен друг друга), а не опции, добавляемые вместе. Перевести базу в restoring можно, добавив NORECOVERY.

Проверка: бэкап без проверки не считается бэкапом

Файл .bak на диске — еще не гарантия восстановления. Минимум три уровня проверки:

• RESTORE VERIFYONLY — проверяет, что набор полон и читаем, и сверяет имеющиеся контрольные суммы, ничего не восстанавливая. Он НЕ проверяет логическую структуру данных внутри копии, поэтому тестовое восстановление не заменяет.
• Тестовое восстановление в отдельную базу (как выше) — единственная настоящая проверка, что из копии поднимется рабочая база.
• DBCC CHECKDB на восстановленной базе — проверяет логическую и физическую целостность данных.

RESTORE VERIFYONLY
FROM DISK = N'D:\backup\Shop_full.bak'
WITH CHECKSUM;
-- Сообщение: "The backup set on file 1 is valid." при успехе

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

Хранение и защита копий

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

• Ограничить доступ к каталогу бэкапов на уровне прав файловой системы: файл .bak без шифрования прочитает любой, кто до него дотянется.
• Где редакция поддерживает функцию — включать BACKUP ... WITH ENCRYPTION (алгоритм + сертификат или асимметричный ключ). Без этого сертификата или ключа зашифрованную копию восстановить невозможно, поэтому его хранят ОТДЕЛЬНО от самой копии и тоже резервируют.
• Проверять восстановление зашифрованной копии на другом экземпляре SQL Server — убедиться, что сертификат перенесен и копия поднимется не только там, где создавалась.

Выводы

• Сначала выбирается модель восстановления: SIMPLE запрещает бэкап журнала и восстановление на момент времени, FULL — разрешает и требует его.
• Дифференциальный бэкап кумулятивный (нужен только последний), бэкап журнала инкрементальный (нужна вся цепочка) — это разные типы, а не синонимы.
• RPO задает частоту бэкапов журнала, RTO — стратегию восстановления; это разные параметры.
• BACKUP работает онлайн (обычные чтение и запись продолжаются), но несовместим со сжатием базы и операциями управления файлами; разрушают данные только RESTORE, FORMAT и INIT, поэтому тренируются на отдельной базе.
• Копия считается рабочей только после тестового восстановления и DBCC CHECKDB, а не после успешного BACKUP или RESTORE VERIFYONLY (он проверяет читаемость, но не структуру данных).
• Вынесенная копия — чувствительный актив: ограничить доступ к каталогу, при поддержке включить шифрование и хранить сертификат/ключ отдельно.

Где применяется / связь с практикой

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

Смежные темы: Как исправить ошибки MS SQL и восстановить базу данных, SQL: описание и особенности, Привилегии в SQL.

FAQ

Можно ли делать бэкап работающей базы, не останавливая ее?
Да. BACKUP DATABASE выполняется онлайн — пользователи продолжают читать и писать, копия остается согласованной на момент завершения операции. Останавливать базу не нужно. Но «онлайн» не значит «без ограничений»: BACKUP несовместим со сжатием базы (shrink) и операциями управления файлами, поэтому такие административные работы разводят с бэкапом по времени.

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

Почему в модели FULL журнал транзакций не уменьшается после полного бэкапа?
Полный бэкап журнал не усекает — он только помечает данные как сохраненные. Освобождает место в журнале и позволяет его переиспользовать именно BACKUP LOG; без регулярных бэкапов журнала в модели FULL он будет расти до заполнения диска.