Налаштування 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 проєктів з налаштування та відновлення.
Три типи бекапів і коли що потрібно
| Тип бекапу | Частота | Розмір | Час відновлення | RPO |
|---|---|---|---|---|
| Full | Щотижня | Найбільший | Довге (години) | До тижня |
| Differential | Щодня | Середній (зростає до full) | Середнє | До доби |
| Transaction Log | Кожні 15-30 хв | Маленький | Швидке (хвилини) | 15-30 хв |
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 робочий день.
