Настройка MS SQL Server бэкапирования (Full/Differential/Log)

Настройка MS SQL Server бэкапирования (Full/Differential/Log)

Самая частая причина потери данных в SQL Server — не hardware failure и не ransomware. Это отсутствие проверенной стратегии бэкапов: бэкапы делались, но никто не проверял восстановление, и в нужный момент оказалось, что последний рабочий бэкап — трёхнедельной давности. Или что бэкап transaction log не работал, потому что базы были в модели восстановления SIMPLE, и все log backups тихо падали с This command is not supported with simple recovery model. Мы сталкивались с такими ситуациями десятки раз — поэтому построили процесс, исключающий подобные сюрпризы. Наш опыт в SQL Server — более 5 лет, более 50 проектов по настройке и восстановлению.

Три типа бэкапов и когда что нужно

Full backup — снимает всю базу целиком. Может идти часами на больших базах. Является базой для differential и log бэкапов.

Differential backup — только страницы, изменённые с момента последнего полного бэкапа. Растёт со временем: если после full прошла неделя, differential может быть почти таким же по размеру. Ускоряет восстановление по сравнению с применением всей цепочки log-бэкапов.

Transaction Log backup — архивирует записи transaction log с момента последнего log backup. Обязательное условие — модель восстановления FULL или BULK_LOGGED. Позволяет восстановить базу до конкретной точки во времени (point-in-time recovery).

Типичная стратегия для продакшн OLTP базы:

• Full backup: раз в неделю (воскресенье ночью)
• Differential backup: раз в сутки (будни в 02:00)
• Log backup: каждые 15–30 минут

При такой стратегии максимальная потеря данных при аварии — 15–30 минут.

Почему важен бэкап журнала транзакций?

Без log backup восстановление возможно только на момент последнего полного или дифференциального бэкапа. Это означает потерю данных за несколько часов или дней. С log backup можно откатиться до любой секунды внутри интервала. Особенно критично для финансовых и ERP-систем, где каждая транзакция на счету. Если база в SIMPLE recovery model, log backup невозможен — нужно переключить модель на FULL. Это требует downtime, но в итоге окупается.

Настройка через SQL Server Agent Jobs (T-SQL)

Maintenance Plans из GUI хрупкие и плохо управляются из git. Правильный путь — T-SQL скрипты в Agent Jobs.

-- Пример job для полного бэкапа с компрессией
DECLARE @BackupPath NVARCHAR(500) = 'D:\SQLBackup\Full\AppDB_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.bak';
BACKUP DATABASE [AppDB] TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM, STATS = 10, DESCRIPTION = 'Weekly Full Backup';
-- Проверка целостности после бэкапа
RESTORE VERIFYONLY FROM DISK = @BackupPath WITH CHECKSUM;

-- Transaction Log backup
DECLARE @LogPath NVARCHAR(500) = 'D:\SQLBackup\Log\AppDB_log_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.trn';
BACKUP LOG [AppDB] TO DISK = @LogPath WITH COMPRESSION, CHECKSUM, STATS = 5;

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

Ola Hallengren Maintenance Solution

Для большинства проектов разумнее использовать готовое решение Ola Hallengren (бесплатно, де-факто стандарт в сообществе) вместо написания своих скриптов. Это более чем в 5 раз сокращает время на поддержку — не нужно отлаживать свои велосипеды. Поддерживает Always On, автоматически пропускает log backup для SIMPLE-баз.

-- Пример вызова для полного бэкапа всех пользовательских баз
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @Verify = 'Y',
    @CleanupTime = 168, -- удалять файлы старше 168 часов (7 дней)
    @LogToTable = 'Y';

-- Log backup с очисткой старых файлов
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 48,
    @LogToTable = 'Y';

Решение автоматически пропускает log backup для баз в SIMPLE режиме, обрабатывает Always On AG (делает бэкап на предпочтительной реплике) и пишет историю в таблицу для мониторинга.

Как часто нужно тестировать восстановление?

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

-- Восстановление с точкой во времени
RESTORE DATABASE [AppDB_Test] FROM DISK = 'D:\SQLBackup\Full\AppDB_YYYYMMDD_HHMMSS.bak'
WITH NORECOVERY, REPLACE,
MOVE 'AppDB' TO 'E:\TestData\AppDB_Test.mdf',
MOVE 'AppDB_log' TO 'E:\TestData\AppDB_Test_log.ldf';

-- Применяем differential
RESTORE DATABASE [AppDB_Test] FROM DISK = 'D:\SQLBackup\Diff\AppDB_diff_YYYYMMDD_HHMMSS.bak'
WITH NORECOVERY;

-- Применяем log бэкапы до нужной точки
RESTORE LOG [AppDB_Test] FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn'
WITH NORECOVERY;

RESTORE LOG [AppDB_Test] FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn'
WITH RECOVERY, STOPAT = 'YYYY-MM-DD HH:MM:SS'; -- точная точка восстановления

Хранение и репликация бэкапов

Локальные бэкапы — только первая линия. Для защиты от потери сервера нужна внешняя копия:

• SQL Server Backup to URL (Azure Blob Storage) — встроенная функция с SQL Server 2012
• Robocopy/rsync на отдельный файловый сервер или NAS
• Сторонние решения: Veeam Backup & Replication, Commvault, Veritas

Правило 3-2-1: три копии, два разных носителя, одна вне площадки.

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

Мы предлагаем комплексную услугу под ключ:

• Аудит текущей инфраструктуры и модели восстановления
• Разработка стратегии backup с учётом RPO/RTO
• Создание и настройка SQL Agent Jobs (T-SQL) или внедрение Ola Hallengren
• Настройка алертов (email, Telegram) на сбои бэкапов
• Настройка репликации бэкапов во внешнее хранилище
• Документирование процедуры восстановления
• Обучение вашей команды (2 часа live demo)
• Гарантия на корректную работу бэкапов в течение 1 месяца после настройки

Мы на рынке более 5 лет, реализовали 50+ проектов по SQL Server. Свяжитесь с нами, чтобы получить консультацию и оценку вашего проекта за 1 рабочий день.