м. Київ, вул. Кирилівська 102

26 жовтня 2026 р.

Резервне копіювання SQL Server

Резервне копіювання SQL Server

Резервна копія SQL Server має цінність лише тоді, коли з неї можна відновити дані в межах прийнятного часу. Наявність кількох файлів `.bak` на тому самому диску, де працює база, ще не створює надійного захисту. Потрібна цілісна схема: визначені RPO та RTO, правильна модель відновлення, автоматичний розклад, контроль виконання, окреме сховище та регулярні тести відновлення.

RPO визначає, скільки даних допустимо втратити після збою. Якщо журнал транзакцій копіюється кожні 15 хвилин, теоретичний RPO становить до 15 хвилин. RTO показує, за який час команда повинна повернути базу до робочого стану. Ці показники впливають на частоту копіювання, строк зберігання та вибір носіїв.

Резервування SQL Server потрібно включати до загального процесу обслуговування серверів, а не залишати окремим завданням адміністратора баз даних. Стан дисків, права облікового запису служби, доступність мережевого сховища та сповіщення про помилки безпосередньо впливають на результат.

Що підготувати перед налаштуванням резервних копій

Спочатку потрібно перевірити, де розташовані файли даних, скільки місця займають бази та як швидко вони зростають. Для первинної оцінки можна виконати запит у SQL Server Management Studio:

sql
SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_file_name,
    type_desc,
    physical_name,
    CAST(size * 8.0 / 1024 AS decimal(18, 2)) AS size_mb
FROM sys.master_files
ORDER BY database_name, type_desc;

Каталог для резервних копій не варто створювати всередині директорії з файлами MDF і LDF. Для локального проміжного зберігання можна використати окремий том, наприклад `D:\SQLBackups`, але фінальна копія повинна потрапляти на інший сервер, об’єктне сховище або спеціалізовану систему резервування. Втрата одного фізичного вузла не повинна знищити одночасно базу та всі її копії.

Важливий нюанс: доступ до каталогу потрібен обліковому запису, під яким працює служба SQL Server Database Engine. Права користувача, який відкрив SSMS, у цьому випадку не визначають можливість запису. Для мережевого шляху на кшталт `\\backup01\sql$` потрібно надати службовому обліковому запису доступ і на рівні спільного ресурсу, і в NTFS.

Окремо перевіряють модель відновлення кожної бази:

sql
SELECT
    name AS database_name,
    recovery_model_desc,
    state_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Модель `SIMPLE` не підтримує резервні копії журналу транзакцій. Вона підходить для баз, де допустима втрата змін після останньої повної або диференційної копії. Модель `FULL` потрібна, якщо необхідне відновлення до конкретного моменту часу. Після переведення бази в `FULL` слід зробити повну резервну копію, яка започаткує ланцюжок журналу:

sql
ALTER DATABASE [Sales] SET RECOVERY FULL;
GO

BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_full_initial.bak'
WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;
GO

Параметр `CHECKSUM` змушує SQL Server перевіряти контрольні суми сторінок під час читання та додавати контрольні дані до backup-файлу. `COMPRESSION` зменшує обсяг копії та навантаження на сховище, хоча потребує додаткових ресурсів процесора. `INIT` перезаписує вказаний носій, тому його безпечно застосовувати лише тоді, коли ім’я файлу унікальне або попередній файл більше не потрібен.

Як побудувати схему Full, Differential і Log Backup

Для більшості робочих баз практичною основою є комбінація трьох типів копій:

- повна копія раз на добу або раз на тиждень залежно від розміру бази; - диференційна копія між повними резервуваннями; - копія журналу транзакцій кожні 5–30 хвилин для баз у моделі `FULL`.

Повна копія містить усі сторінки даних, необхідні для відновлення бази на момент завершення операції. Щоб не перезаписувати попередні файли, ім’я краще формувати з часовою позначкою:

sql
DECLARE @file nvarchar(4000);

SET @file = N'D:\SQLBackups\Sales_FULL_'
    + CONVERT(char(8), GETDATE(), 112)
    + N'_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108), ':', '')
    + N'.bak';

BACKUP DATABASE [Sales]
TO DISK = @file
WITH COMPRESSION, CHECKSUM, STATS = 10;

Диференційна копія містить зміни відносно базової повної копії. Вона скорочує час відновлення, оскільки після Full Backup достатньо застосувати лише останній актуальний Differential Backup, а не довгу послідовність проміжних файлів:

sql
DECLARE @file nvarchar(4000);

SET @file = N'D:\SQLBackups\Sales_DIFF_'
    + CONVERT(char(8), GETDATE(), 112)
    + N'_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108), ':', '')
    + N'.bak';

BACKUP DATABASE [Sales]
TO DISK = @file
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

Для відновлення до точки в часі потрібні копії журналу транзакцій. Вони повинні виконуватися без розривів ланцюжка:

sql
DECLARE @file nvarchar(4000);

SET @file = N'D:\SQLBackups\Sales_LOG_'
    + CONVERT(char(8), GETDATE(), 112)
    + N'_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108), ':', '')
    + N'.trn';

BACKUP LOG [Sales]
TO DISK = @file
WITH COMPRESSION, CHECKSUM, STATS = 10;

Частота Log Backup впливає не лише на RPO. Якщо журнал не копіювати, SQL Server не зможе повторно використовувати його неактивну частину за нормальних умов, тому LDF-файл може продовжувати зростати. Зменшувати його за розкладом командою `DBCC SHRINKFILE` не потрібно: це не заміна коректного копіювання журналу й часто створює повторні цикли розширення файлу.

Для позапланової повної копії, яка не повинна змінювати базу диференційного резервування, використовується параметр `COPY_ONLY`:

sql
BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_before_update_copy_only.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT, STATS = 10;

Такий варіант зручний перед оновленням застосунку або зміною схеми. Для планової базової Full Backup параметр `COPY_ONLY` використовувати не слід, інакше наступні диференційні копії не спиратимуться на неї.

Як автоматизувати резервування та контролювати розклад

На повній редакції SQL Server завдання зазвичай запускають через SQL Server Agent. Замість одного великого job доцільно створити окремі завдання для Full, Differential і Transaction Log Backup. Це спрощує розклад, повторний запуск та пошук причини помилки.

Приклад розкладу для бази середнього розміру:

- Full Backup щонеділі о 01:00; - Differential Backup з понеділка до суботи о 01:00; - Transaction Log Backup кожні 15 хвилин; - копіювання файлів до віддаленого сховища після завершення локальної операції; - автоматичне видалення лише тих файлів, строк зберігання яких завершився.

Строк зберігання не варто визначати лише за вільним місцем. Наприклад, можна зберігати щоденні копії 14 днів, щотижневі — 8 тижнів, а щомісячні — відповідно до внутрішніх вимог. Видалення старих файлів виконується після перевірки, що нові копії створені й передані до незалежного сховища.

Для очищення локального каталогу можна застосувати PowerShell. Команда нижче видаляє файли `.bak` і `.trn`, старші за 14 днів, тому шлях і строк необхідно адаптувати до затвердженої політики:

powershell
$BackupPath = 'D:\SQLBackups'
$RetentionDate = (Get-Date).AddDays(-14)

Get-ChildItem -Path $BackupPath -File -Include *.bak, *.trn |
    Where-Object { $_.LastWriteTime -lt $RetentionDate } |
    Remove-Item -Force

Параметр `-Include` може поводитися неочікувано без маски в шляху, тому для передбачуваного виконання також можна фільтрувати розширення явно:

powershell
Get-ChildItem -Path 'D:\SQLBackups' -File |
    Where-Object {
        $_.Extension -in '.bak', '.trn' -and
        $_.LastWriteTime -lt (Get-Date).AddDays(-14)
    } |
    Remove-Item -Force

SQL Server зберігає історію резервувань у базі `msdb`. Наступний запит показує останні операції, їхній тип, тривалість і коефіцієнт стиснення:

sql
SELECT TOP (100)
    bs.database_name,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        ELSE bs.type
    END AS backup_type,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,
    CAST(bs.backup_size / 1048576.0 AS decimal(18, 2)) AS backup_mb,
    CAST(bs.compressed_backup_size / 1048576.0 AS decimal(18, 2)) AS compressed_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_finish_date DESC;

Успішний статус SQL Server Agent Job ще не гарантує доступності копії через кілька днів. Потрібно контролювати вік останнього Full і Log Backup, розмір файлів, вільне місце, помилки доступу до сховища та надсилання копій за межі основного сервера. Ці перевірки зручно інтегрувати в загальний процес адміністрування серверів Windows і Linux.

Як перевіряти копії та виконувати тестове відновлення

Команда `RESTORE VERIFYONLY` перевіряє структуру backup-файлу й можливість його прочитати, але не замінює повноцінного відновлення. Її варто запускати після створення та після передавання копії до іншого сховища:

sql
RESTORE VERIFYONLY
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260912_010000.bak'
WITH CHECKSUM;

Перед відновленням корисно переглянути заголовок копії та логічні імена файлів:

sql
RESTORE HEADERONLY
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260912_010000.bak';
GO

RESTORE FILELISTONLY
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260912_010000.bak';
GO

Тест потрібно проводити на окремому SQL Server або під іншою назвою бази. Приклад відновлення Full Backup із перенесенням файлів:

sql
RESTORE DATABASE [Sales_RestoreTest]
FROM DISK = N'D:\SQLBackups\Sales_FULL_20260912_010000.bak'
WITH
    MOVE N'Sales' TO N'E:\SQLData\Sales_RestoreTest.mdf',
    MOVE N'Sales_log' TO N'F:\SQLLogs\Sales_RestoreTest_log.ldf',
    NORECOVERY,
    CHECKSUM,
    STATS = 10;

Параметр `NORECOVERY` залишає базу в стані відновлення, щоб можна було застосувати Differential Backup і журнали. Наступним кроком відновлюють останню диференційну копію, створену після використаної повної:

sql
RESTORE DATABASE [Sales_RestoreTest]
FROM DISK = N'D:\SQLBackups\Sales_DIFF_20260912_130000.bak'
WITH NORECOVERY, CHECKSUM, STATS = 10;

Після цього журнали відновлюють у точній хронологічній послідовності. Для останнього файлу застосовують `RECOVERY`:

sql
RESTORE LOG [Sales_RestoreTest]
FROM DISK = N'D:\SQLBackups\Sales_LOG_20260912_131500.trn'
WITH NORECOVERY, CHECKSUM, STATS = 10;
GO

RESTORE LOG [Sales_RestoreTest]
FROM DISK = N'D:\SQLBackups\Sales_LOG_20260912_133000.trn'
WITH RECOVERY, CHECKSUM, STATS = 10;
GO

Якщо потрібно повернути дані до моменту перед помилковим видаленням, для останньої журнальної копії використовується `STOPAT`:

sql
RESTORE LOG [Sales_RestoreTest]
FROM DISK = N'D:\SQLBackups\Sales_LOG_20260912_133000.trn'
WITH
    STOPAT = '2026-09-12T13:24:00',
    RECOVERY,
    CHECKSUM,
    STATS = 10;

Після тестового відновлення недостатньо побачити стан `ONLINE`. Варто виконати `DBCC CHECKDB`, перевірити ключові таблиці, кількість записів, доступ користувачів і запуск застосунку на відновленій базі:

sql
DBCC CHECKDB (N'Sales_RestoreTest') WITH NO_INFOMSGS;

Результат тесту потрібно фіксувати: які файли використовувалися, скільки тривало копіювання, скільки часу зайняло відновлення та чи виконано цільовий RTO. Якщо база велика, тест може показати, що резервна копія створюється швидко, але її передавання або відновлення займає неприйнятно багато часу. Під час планування також корисно врахувати рекомендації з матеріалу про оптимізацію Microsoft SQL Server, оскільки стан дискової підсистеми та надмірний розмір файлів впливають на тривалість операцій.

Типові помилки

**Зберігання копій лише на сервері SQL Server.** Вихід із ладу дискового масиву, шифрувальник або помилка адміністратора можуть знищити і базу, і каталог резервування. Потрібна щонайменше одна ізольована копія на іншому вузлі або в об’єктному сховищі.

**Відсутність Log Backup у моделі FULL.** Журнал транзакцій зростає, а відновлення до потрібної точки залишається неможливим. Після ввімкнення `FULL` необхідно зробити повну копію та запустити регулярне резервування журналу.

**Перезапис одного й того самого файлу.** Постійне використання `backup.bak` разом із `INIT` залишає лише останню версію. Унікальна часова позначка в імені файлу спрощує ротацію та побудову послідовності відновлення.

**Перевірка тільки факту створення файлу.** Файл може бути неповним, пошкодженим після передавання або недоступним під час аварії. Мінімальний контроль включає `RESTORE VERIFYONLY`, а надійний — регулярне тестове відновлення з `DBCC CHECKDB`.

**Розрив ланцюжка журналів.** Перехід у `SIMPLE`, втрата одного `.trn` або неправильна послідовність файлів унеможливлюють відновлення до бажаного моменту. Ланцюжок слід контролювати, а всі журнальні копії — зберігати до завершення їхнього строку утримання.

**Використання COPY_ONLY у плановій повній копії.** Така копія не стає новою базою для Differential Backup. `COPY_ONLY` призначений для позапланових операцій, які не повинні змінювати звичайний цикл резервування.

**Відсутність сповіщень.** Завдання може не виконуватися кілька днів через заповнений диск, зміну пароля службового облікового запису або недоступність мережевого ресурсу. SQL Server Agent повинен надсилати повідомлення про помилки, а система моніторингу — контролювати вік останньої успішної копії.

**Передчасне видалення старих файлів.** Якщо очищення запускається до завершення нової копії або до її передавання в незалежне сховище, короткий збій може залишити сервер без придатної точки відновлення. Спочатку створюють і перевіряють нову копію, а лише потім виконують ротацію.

**Ігнорування захисту конфіденційних даних.** Backup-файл містить ті самі дані, що й робоча база. Доступ до каталогу потрібно обмежити, передавання — захистити, а для чутливих систем застосувати шифрування резервних копій і окремо зберігати ключі або сертифікати, необхідні для відновлення.

Висновок

Надійне резервне копіювання SQL Server — це не одна команда `BACKUP DATABASE`, а керований процес. Спочатку визначають RPO та RTO, обирають Recovery Model, після чого будують ланцюжок із Full, Differential і Transaction Log Backup. Завдання автоматизують через SQL Server Agent, результати контролюють у `msdb` та системі моніторингу, а файли передають до незалежного сховища.

Ключова перевірка — регулярне відновлення на окремому сервері. Саме воно підтверджує, що команда має повний комплект файлів, знає правильну послідовність дій і здатна повернути базу до робочого стану в межах визначеного RTO. Якщо тестове відновлення ніколи не виконувалося, резервну схему не можна вважати перевіреною незалежно від кількості створених `.bak` і `.trn` файлів.

📞Резервне копіювання SQL Server — Full, Diff і Log Backup | ITheal