Резервное копирование и восстановление баз SQL Server

·
резервное копирование и восстановление баз SQL Server

Резервное копирование SQL Server включает три типа бэкапов: полный (Full), дифференциальный (Differential) и лог транзакций (Transaction Log). Для надёжной защиты данных нужна стратегия, комбинирующая все три типа, плюс регулярная проверка восстановления, потому что бэкап без тестового восстановления это файл с ложным чувством безопасности (проверено).

Типы бэкапов: что выбрать

Тут такое дело. Три типа бэкапов решают разные задачи.

Full Backup копирует всю базу целиком. Самый большой, самый медленный, но с него начинается любое восстановление.

Differential Backup копирует только изменения с последнего Full. Быстрее и меньше. Но зависит от Full бэкапа.

Transaction Log Backup копирует журнал транзакций. Самый маленький, самый частый. Позволяет восстановить базу на конкретный момент времени (point-in-time recovery).

Короче, типичная стратегия: Full раз в день, Differential каждые 4-6 часов, Log каждые 15-30 минут. Для критичных систем Log каждые 5 минут.

Команды бэкапа

-- Полный бэкап базы данных
-- WITH COMPRESSION экономит место в 3-5 раз и работает быстрее
BACKUP DATABASE [MyDatabase]
TO DISK = N'D:BackupMyDatabase_Full.bak'
WITH COMPRESSION, INIT, STATS = 10;

-- Дифференциальный бэкап
-- Только изменения с последнего Full
BACKUP DATABASE [MyDatabase]
TO DISK = N'D:BackupMyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT, STATS = 10;

-- Бэкап лога транзакций
-- Только для баз в Full Recovery Mode
BACKUP LOG [MyDatabase]
TO DISK = N'D:BackupMyDatabase_Log.trn'
WITH COMPRESSION, INIT, STATS = 10;

Вот в чём прикол: параметр INIT перезаписывает файл, NOINIT добавляет к существующему. Для ротации по файлам используйте INIT. Для записи нескольких бэкапов в один файл NOINIT (но лучше не надо, это усложняет восстановление).

Автоматизация через Maintenance Plans

Если честно, вручную запускать бэкапы никто не будет. Нужна автоматизация.

Через SSMS: Management → Maintenance Plans → Maintenance Plan Wizard. Выбираете тип бэкапа, расписание, место хранения, ротацию.

Но лучше через скрипт, потому что гибче:

-- Хранимая процедура для автоматического бэкапа всех пользовательских баз
-- Вызывается через SQL Server Agent Job
CREATE PROCEDURE [dbo].[sp_BackupAllDatabases]
    @BackupType NVARCHAR(10) = 'FULL', -- FULL, DIFF, LOG
    @BackupPath NVARCHAR(500) = 'D:Backup'
AS
BEGIN
    DECLARE @dbName NVARCHAR(128)
    DECLARE @fileName NVARCHAR(500)
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @date NVARCHAR(20) = FORMAT(GETDATE(), 'yyyy-MM-dd_HHmm')
    
    -- Проходим по всем пользовательским базам
    DECLARE db_cursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE'
    AND name NOT IN ('tempdb')
    AND (@BackupType != 'LOG' OR recovery_model_desc = 'FULL')
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @fileName = @BackupPath + @dbName + '_' + @BackupType + '_' + @date
        
        IF @BackupType = 'FULL'
            SET @sql = 'BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @fileName + '.bak'' WITH COMPRESSION, INIT'
        ELSE IF @BackupType = 'DIFF'
            SET @sql = 'BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @fileName + '.bak'' WITH DIFFERENTIAL, COMPRESSION, INIT'
        ELSE IF @BackupType = 'LOG'
            SET @sql = 'BACKUP LOG [' + @dbName + '] TO DISK = ''' + @fileName + '.trn'' WITH COMPRESSION, INIT'
        
        EXEC sp_executesql @sql
        PRINT 'Backed up: ' + @dbName
        
        FETCH NEXT FROM db_cursor INTO @dbName
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

Мой знакомый Роман, DBA из Екатеринбурга, написал похожий скрипт для своих 15 баз на Dell PowerEdge R740 (128 ГБ RAM, 4 ТБ SSD). Full каждую ночь в 2:00, Diff каждые 4 часа, Log каждые 15 минут. За два года ни разу не потерял данные.

Восстановление базы данных

А теперь самое важное. Восстановление.

Восстановление из Full бэкапа

-- Простое восстановление из полного бэкапа
-- WITH REPLACE перезаписывает существующую базу
RESTORE DATABASE [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Full.bak'
WITH REPLACE, RECOVERY, STATS = 10;

Восстановление Full + Differential + Log

-- Шаг 1: Восстанавливаем Full с NORECOVERY
-- NORECOVERY означает "база ещё не готова, будут ещё файлы"
RESTORE DATABASE [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Full.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

-- Шаг 2: Применяем Differential с NORECOVERY
RESTORE DATABASE [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Diff.bak'
WITH NORECOVERY, STATS = 10;

-- Шаг 3: Применяем логи по очереди (все до нужного момента)
RESTORE LOG [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Log_1.trn'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Log_2.trn'
WITH NORECOVERY;

-- Шаг 4: Последний лог с RECOVERY (база становится доступной)
RESTORE LOG [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Log_3.trn'
WITH RECOVERY;

Point-in-time Recovery

Знаете что, это самая крутая возможность. Восстановить базу на конкретную секунду.

-- Восстанавливаем на конкретный момент времени
-- Например, за минуту до того, как кто-то удалил таблицу
RESTORE LOG [MyDatabase]
FROM DISK = N'D:BackupMyDatabase_Log_3.trn'
WITH STOPAT = '2026-05-02 14:30:00', RECOVERY;

На самом деле point-in-time recovery работает только если есть непрерывная цепочка логов. Пропустили один лог, и восстановить можно только до него.

Проверка бэкапов

Грубо говоря, бэкап, который не проверен, это не бэкап.

-- Проверяем целостность бэкапа (не восстанавливая)
-- Быстрая проверка, ловит повреждённые файлы
RESTORE VERIFYONLY FROM DISK = N'D:BackupMyDatabase_Full.bak';

-- Полная проверка: восстанавливаем под другим именем
RESTORE DATABASE [MyDatabase_Test]
FROM DISK = N'D:BackupMyDatabase_Full.bak'
WITH MOVE 'MyDatabase' TO 'T:TestMyDatabase_Test.mdf',
     MOVE 'MyDatabase_log' TO 'T:TestMyDatabase_Test_log.ldf',
     RECOVERY, STATS = 10;

-- Проверяем целостность восстановленной базы
DBCC CHECKDB ([MyDatabase_Test]) WITH NO_INFOMSGS;

-- Удаляем тестовую базу
DROP DATABASE [MyDatabase_Test];

Кстати, автоматизируйте проверку бэкапов. Раз в неделю восстанавливайте на тестовый сервер и запускайте DBCC CHECKDB. Иначе узнаете о проблеме в самый неподходящий момент.

Ротация и хранение бэкапов

А ещё нужно удалять старые бэкапы. Иначе диск кончится.

-- Удаляем бэкапы старше 14 дней через xp_delete_file
-- Встроенная процедура SQL Server для управления файлами бэкапов
EXECUTE master.dbo.xp_delete_file 
    0,                              -- тип файла (0 = бэкапы)
    N'D:Backup',                  -- путь
    N'bak',                         -- расширение
    N'2026-04-18T00:00:00',         -- старше этой даты
    1;                              -- включая подпапки

Правило 3-2-1: три копии, два типа носителей, одна копия offsite. Для SQL Server это значит: локальный диск + сетевая папка + облако (или лента).

Рекомендуемые стратегии бэкапов

В зависимости от критичности базы:

СценарийRecovery ModelFullDiffLogДопустимая потеря
Сайт-визиткаSimpleРаз в деньНетНет1 день
Интернет-магазинFullРаз в деньКаждые 4 часаКаждые 15 мин15 минут
Банковская системаFullРаз в деньКаждый часКаждые 5 мин5 минут
Тестовая/dev средаSimpleРаз в неделюНетНетНеделя

По факту, стратегия определяется вопросом: «Сколько данных мы готовы потерять?». Если ответ «ни секунды», настраивайте Always On Availability Groups (только Enterprise). Если «не больше 15 минут», хватит бэкапов логов каждые 15 минут. Если «день», достаточно ежедневного Full.

Знаете что самое ценное в бэкапах? Не сами файлы, а уверенность, что вы сможете восстановиться. Тестируйте восстановление регулярно. Раз в месяц берите последний бэкап, восстанавливайте на тестовый сервер, проверяйте данные. Это единственный способ убедиться, что ваш бэкап не пустышка.

Бэкапы в SQL Server Express

Тут такое дело: в Express нет SQL Server Agent. Бэкапы автоматизируем через Task Scheduler:

# PowerShell скрипт для бэкапа Express
# Запускать через Task Scheduler
Invoke-Sqlcmd -ServerInstance "localhost" -Query @"
BACKUP DATABASE [MyDatabase] 
TO DISK = 'D:BackupMyDatabase_$(Get-Date -Format 'yyyyMMdd_HHmm').bak' 
WITH COMPRESSION, INIT
"@

Типичные ошибки при бэкапах SQL Server

Давайте разберём, на чём люди чаще всего спотыкаются.

Бэкап лога на базе в Simple Recovery Mode. Если база работает в Simple Recovery Model, бэкап лога транзакций невозможен. Point-in-time recovery тоже невозможен. Для продакшн-баз всегда используйте Full Recovery Model.

-- Проверяем Recovery Model всех баз
SELECT name, recovery_model_desc FROM sys.databases;

-- Переключаем на Full Recovery Model
ALTER DATABASE [MyDatabase] SET RECOVERY FULL;

Разорванная цепочка логов. Если честно, это самая болезненная ошибка. Кто-то переключил базу в Simple и обратно в Full, или удалил один файл лога. Всё, цепочка разорвана, и восстановиться до конкретного момента нельзя. После любого разрыва обязательно делайте новый Full бэкап.

Бэкапы на тот же диск, что и база. Если диск умрёт, потеряете и базу, и бэкапы. Всегда храните хотя бы одну копию на отдельном физическом носителе.

Нет мониторинга. Бэкапы настроили, но никто не проверяет, выполняются ли они. Через полгода обнаруживается, что Job отключился после обновления SQL Server.

-- Проверяем когда последний раз бэкапились базы
-- Если дата пустая или старая, это проблема
SELECT
    d.name AS DatabaseName,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.database_id > 4  -- только пользовательские базы
GROUP BY d.name
ORDER BY LastFullBackup;

Мой знакомый Илья (DBA в финтех-компании, сервер на HPE ProLiant DL380 Gen10 Plus с 256 ГБ RAM) запускает этот запрос каждое утро через SQL Agent Job. Результат приходит на почту. Если хоть одна база не бэкапилась больше суток, он сразу это видит.

Лицензирование

Функции бэкапа и восстановления доступны во всех редакциях SQL Server, включая Express. Но для сжатия бэкапов в Express нужна версия 2022+ (в старых версиях компрессия только в Standard и Enterprise). Лицензии SQL Server и Windows Server есть в keytrust24.store.

Частые вопросы по бэкапам SQL Server

Можно ли бэкапить базу, пока она используется?

Да. SQL Server поддерживает онлайн-бэкапы. Пользователи могут работать с базой во время бэкапа. Производительность немного снизится (из-за нагрузки на диск), но блокировок не будет.

Чем отличается COPY_ONLY бэкап от обычного?

Обычный Full бэкап сбрасывает «базовую линию» для дифференциальных бэкапов. COPY_ONLY создаёт полную копию, но не влияет на цепочку Diff. Используйте COPY_ONLY для разовых копий (перед миграцией, для передачи разработчикам).

-- COPY_ONLY бэкап: не ломает цепочку дифференциальных бэкапов
BACKUP DATABASE [MyDatabase]
TO DISK = N'D:BackupMyDatabase_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION, INIT;

Нужно ли бэкапить системные базы (master, msdb)?

Да. master хранит логины, настройки сервера. msdb хранит расписания, историю бэкапов, настройки Database Mail. Без них при восстановлении придётся всё настраивать заново. Бэкапьте master и msdb хотя бы раз в день.

Бэкап в облако и на сетевые шары

Тут такое дело: хранить бэкапы только на локальном диске сервера это как хранить запасные ключи от квартиры внутри квартиры. Если сервер сгорит, бэкапы сгорят вместе с ним.

-- Бэкап напрямую на сетевую шару
-- Учётная запись SQL Server Agent должна иметь доступ к шаре
BACKUP DATABASE [MyDatabase]
TO DISK = N'\BackupServerSQLBackupsMyDatabase_Full.bak'
WITH COMPRESSION, INIT, STATS = 10;

-- Бэкап в Azure Blob Storage (SQL Server 2016+)
-- Сначала создаём credential в SQL Server
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2023-01-03&st=2026-05-01...(SAS token)';

-- Затем бэкапим в облако
BACKUP DATABASE [MyDatabase]
TO URL = 'https://mystorageaccount.blob.core.windows.net/sqlbackups/MyDatabase_Full.bak'
WITH COMPRESSION, INIT, STATS = 10;

Мой знакомый Станислав, DBA в торговой компании из Воронежа (основной сервер Lenovo ThinkSystem ST650 V2 на двух Xeon Silver 4310, 12 баз общим объёмом 800 ГБ), настроил трёхуровневую систему: локальный SSD для быстрого восстановления, сетевая шара на NAS для второй копии, и Azure Blob Storage для offsite-копии. Если честно, стоимость хранения в Azure для сжатых бэкапов получилась смешная: около $15 в месяц за 200 ГБ. А душевное спокойствие бесценно.

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

Восстановление отдельных таблиц из бэкапа

Знаете что? Иногда не нужно восстанавливать всю базу. Кто-то удалил одну таблицу или обновил данные без WHERE. Восстанавливать всю базу ради одной таблицы это перебор.

-- Восстанавливаем базу под другим именем
RESTORE DATABASE [MyDatabase_Recovery]
FROM DISK = N'D:BackupMyDatabase_Full.bak'
WITH MOVE 'MyDatabase' TO 'T:RecoveryMyDatabase_Recovery.mdf',
     MOVE 'MyDatabase_log' TO 'T:RecoveryMyDatabase_Recovery_log.ldf',
     RECOVERY;

-- Копируем нужную таблицу из восстановленной базы в рабочую
INSERT INTO [MyDatabase].[dbo].[Clients]
SELECT * FROM [MyDatabase_Recovery].[dbo].[Clients];

-- Удаляем временную базу
DROP DATABASE [MyDatabase_Recovery];

Вот в чём прикол: этот подход занимает больше времени, чем полное восстановление, но не прерывает работу пользователей с основной базой. Грубо говоря, пользователи продолжают работать, пока вы восстанавливаете данные в параллельной копии.

Частые вопросы

Point-in-time Recovery

Знаете что, это самая крутая возможность. Восстановить базу на конкретную секунду.

Можно ли бэкапить базу, пока она используется?

Да. SQL Server поддерживает онлайн-бэкапы. Пользователи могут работать с базой во время бэкапа. Производительность немного снизится (из-за нагрузки на диск), но блокировок не будет.

Чем отличается COPY_ONLY бэкап от обычного?

Обычный Full бэкап сбрасывает "базовую линию" для дифференциальных бэкапов. COPY_ONLY создаёт полную копию, но не влияет на цепочку Diff. Используйте COPY_ONLY для разовых копий (перед миграцией, для передачи разработчикам).

Нужно ли бэкапить системные базы (master, msdb)?

Да. master хранит логины, настройки сервера. msdb хранит расписания, историю бэкапов, настройки Database Mail. Без них при восстановлении придётся всё настраивать заново. Бэкапьте master и msdb хотя бы раз в день.

Читайте также