Резервное копирование SQL Server

Резервное копирование SQL Server — это процесс создания резервных копий баз данных, поддерживаемых системой управления реляционными базами данных Microsoft SQL Server, с целью обеспечения возможности восстановления данных в случае их повреждения, потери или сбоя оборудования. Является критически важным компонентом стратегии обеспечения непрерывности бизнеса и аварийного восстановления. Резервное копирование позволяет вернуть базу данных в определённое состояние на заданный момент времени, минимизируя потерю данных (RPO — Recovery Point Objective) и время простоя (RTO — Recovery Time Objective).

Типы резервных копий

Microsoft SQL Server поддерживает несколько типов резервных копий, которые различаются объёмом сохраняемых данных, временем выполнения и сценариями восстановления. Выбор типа зависит от требований к RPO, RTO и размера базы данных.

Полная резервная копия (Full Backup)

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

Дифференциальная резервная копия (Differential Backup)

Дифференциальная копия содержит только те данные, которые изменились с момента последней полной резервной копии. Она меньше и создаётся быстрее, чем полная копия, но для восстановления требует наличия последней полной копии. Дифференциальные копии накапливаются: каждая последующая копия включает все изменения с момента последней полной. Это позволяет сократить время восстановления по сравнению с восстановлением только из цепочки журналов транзакций.

Резервная копия журнала транзакций (Transaction Log Backup)

Журнал транзакций записывает все изменения, произошедшие в базе данных. Резервная копия журнала транзакций фиксирует эти изменения с момента последней операции резервного копирования журнала. Данный тип доступен только для баз данных, использующих модель восстановления «с полным протоколированием» (Full Recovery Model) или «с неполным протоколированием» (Bulk-Logged Recovery Model). Регулярное резервное копирование журнала транзакций позволяет выполнять восстановление на момент времени (Point-in-Time Recovery), например, на момент за секунду до сбоя. Для восстановления требуется последовательная цепочка копий журналов, начиная с последней полной или дифференциальной копии.

Копия только для копирования данных (Copy-Only Backup)

Это специальный тип резервной копии, который не влияет на последовательность обычных операций резервного копирования. Обычные полные и дифференциальные копии изменяют метаданные, на основании которых определяется, какие данные войдут в следующую дифференциальную или журнальную копию. Copy-Only Backup позволяет создать снимок базы данных без прерывания этой цепочки. Используется для создания тестовых копий, выгрузки данных или для временных целей, когда не требуется сохранение точки восстановления в стратегии.

Резервная копия файлов и файловых групп (File/Filegroup Backup)

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

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

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

Полное протоколирование (Full Recovery Model)

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

Неполное протоколирование (Bulk-Logged Recovery Model)

Эта модель минимизирует объём протоколирования для массовых операций (например, BULK INSERT, CREATE INDEX, операции с большими текстовыми/бинарными данными). В результате повышается производительность при выполнении таких операций, но снижается уровень защищённости: резервная копия журнала транзакций, созданная после массовой операции, не позволяет восстановить базу данных на момент времени внутри этой операции — возможно только восстановление на момент завершения копии. Используется как временная модель, например, при выполнении длительных массовых загрузок данных.

Простое протоколирование (Simple Recovery Model)

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

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

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

Типовые схемы

1. Ежедневное полное копирование. Простая схема, при которой каждая ночь создаётся полная копия базы данных. Минимальные требования к управлению, но приводит к значительному объёму хранимых данных и длительному времени создания копии. RPO составляет до 24 часов.
2. Полная + дифференциальная. Еженедельная полная копия (например, в воскресенье) и ежедневная дифференциальная копия (с понедельника по субботу). Позволяет сэкономить место и сократить время создания копии по сравнению с ежедневным полным копированием. RPO — до 24 часов.
3. Полная + дифференциальная + журнал транзакций. Наиболее гибкая и защищённая схема. Еженедельная полная копия, ежедневная дифференциальная и, например, каждые 15–60 минут — резервная копия журнала транзакций. Позволяет восстанавливать базу данных с точностью до минуты, минимизируя потери данных.

Рекомендации

• Регулярно тестировать процесс восстановления на тестовом сервере, чтобы убедиться в работоспособности резервных копий.
• Хранить резервные копии на отдельном физическом или логическом устройстве (в идеале — в другом географическом регионе) для защиты от катастроф, затрагивающих основной сервер.
• Настроить оповещения о сбоях в выполнении задания по резервному копированию.
• Определить политику хранения (Retention Policy) для различных типов копий (например, ежедневные — на 7 дней, еженедельные — на 4 недели, ежемесячные — на 12 месяцев).
• Шифровать резервные копии, содержащие конфиденциальные данные, с помощью прозрачного шифрования данных (TDE) или встроенного шифрования резервных копий.

Восстановление (Restore)

Восстановление — это процесс возврата базы данных в рабочее состояние из резервной копии. SQL Server поддерживает несколько сценариев восстановления:

Полное восстановление (Full Restore)

Используется для восстановления всей базы данных из одной или нескольких резервных копий. Последовательность:

1. Восстановление последней полной копии (RESTORE DATABASE ... WITH NORECOVERY).
2. Восстановление последней дифференциальной копии (если применяется) (RESTORE DATABASE ... WITH NORECOVERY).
3. Последовательное восстановление всех резервных копий журнала транзакций, созданных после дифференциальной (или полной) копии, в порядке их создания (RESTORE LOG ... WITH NORECOVERY).
4. Восстановление последней копии журнала с опцией WITH RECOVERY для приведения базы данных в оперативное состояние.

Восстановление на момент времени (Point-in-Time Restore)

Специфическая операция для баз данных в модели полного протоколирования. Позволяет остановить последовательность восстановления журналов на определённый момент времени (например, STOPAT='2023-12-01 14:35:00'). Это крайне полезно для отмены случайного изменения данных или удаления таблицы в определённый момент.

Частичное восстановление (Piecemeal Restore)

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

Инструменты и автоматизация

SQL Server Management Studio (SSMS)

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

Transact-SQL (T-SQL)

Команды BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE, RESTORE LOG и другие. Используются в скриптах и автоматизированных заданиях. Обеспечивают максимальную гибкость, управление опциями и возможность программного контроля.

SQL Server Agent

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

Система хранения и сжатие

• Сжатие резервных копий (Backup Compression). Начиная с SQL Server 2008 Enterprise Edition, поддерживается сжатие данных при создании копии, что уменьшает объём хранилища и время копирования (прирост использования процессора). Доступно во всех редакциях, начиная с SQL Server 2008 R2 SP1.
• Шифрование резервных копий (Backup Encryption). Начиная с SQL Server 2014, поддерживается шифрование данных при создании копии с использованием алгоритмов AES.
• Разделение на несколько файлов. Одна резервная копия может быть записана на несколько файлов на разных дисках для повышения производительности.
• Копии на ленту и в облако. SQL Server поддерживает резервное копирование на ленточные устройства, а также в облачные хранилища (Azure Blob Storage, Amazon S3) через сторонние решения или встроенную поддержку.

Источники

• Документация Microsoft по резервному копированию и восстановлению SQL Server (ссылки на technet / docs.microsoft.com / learn.microsoft.com).
• Книга Inside Microsoft SQL Server 2008: T-SQL Programming (Itzik Ben-Gan), разделы по резервному копированию.
• Материалы конференции PASS Summit за 2023 год по стратегии резервного копирования.
• Статья «Backup and Restore of SQL Server Databases» в Microsoft TechNet.