Резервное копирование базы данных: типы, методы и инструменты (SQL Server, PostgreSQL, MySQL, 1С)

В статье — конкретные команды для SQL Server, PostgreSQL, MySQL и 1С:Предприятие: как создать резервную копию, настроить расписание, зашифровать бэкап и восстановить данные на произвольную точку времени. Отдельно рассмотрим метрики RPO и RTO, которые определяют стратегию бэкапа.

Что такое резервное копирование базы данных

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

Три основные задачи:

1. Восстановление после логической ошибки. Администратор выполнил DELETE FROM orders без WHERE, разработчик запустил скрипт не в той среде — RAID и репликация не помогут. Реплика воспроизводит все операции, включая ошибочные: DELETE немедленно отражается в реплике, и данные исчезают там тоже. Только резервная копия фиксирует состояние до ошибки.

1. Восстановление после повреждения структур СУБД. Сбой диска в середине транзакции, некорректное завершение процесса СУБД, ошибка при обновлении версии — всё это может сделать файлы данных нечитаемыми.

1. Клонирование БД. Развёртывание тестовой или аналитической среды с реальными данными без влияния на production-систему.

Два принципа реализации: выгрузка данных (логический бэкап) и снимок файлов БД с сохранением журналов транзакций (физический бэкап). Физический подход используется в большинстве промышленных систем — он сохраняет внутреннюю структуру хранения и поддерживает PITR (восстановление на точку).

Рекомендуемая частота регулярного резервного копирования данных: малонагруженные системы — не реже раза в неделю; высоконагруженные — ежедневно с регулярными копиями журналов транзакций.

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

Способы резервного копирования данных различаются по объёму охватываемых изменений и скорости восстановления. По этим двум критериям выделяют четыре основных типа — каждый занимает своё место в стратегии бэкапа.

Полная резервная копия базы данных

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

Пример создания резервной копии базы данных в SQL Server:

BACKUP DATABASE [db] TO DISK = ‘db.bak’

WITH FORMAT, COMPRESSION, NAME = ‘Full Backup’;

Важно: FORMAT инициализирует носитель и уничтожает предыдущие данные в файле назначения. Чтобы добавить бэкап к существующему набору без перезаписи, используйте NOINIT.

Дифференциальная и инкрементальная резервная копия

Дифференциальная (разностная) резервная копия сохраняет только изменения, внесённые с момента последней полной. Восстановление — два шага: применить полную, затем дифференциальную. Объём каждой такой копии растёт по мере накопления изменений.

Инкрементальная резервная копия сохраняет изменения от любого предыдущего бэкапа. Объём меньше, но восстановление требует N последовательных шагов: полная → инкрементальная 1 → инкрементальная 2 → … → журналы.

Разные СУБД называют одни и те же концепты по-разному — это важно при сравнении документации:

При восстановлении с PITR порядок фиксированный: полная РК → инкрементальные копии → журналы транзакций до нужного момента. Пропускать шаги в цепочке нельзя.

Резервная копия журнала транзакций и copy-only

Журнал транзакций фиксирует все изменения в БД между плановыми бэкапами. Именно он обеспечивает восстановление на точку: зная момент ошибки, можно применить журналы строго до этого события и сохранить промежуточные данные.

Copy-only — специальный тип, который не нарушает цепочку. После copy-only-бэкапа следующая дифференциальная копия по-прежнему считается от предыдущей полной, а не от этой. В SSMS — галочка «Только копирование»; доступна для типов Full и Log, но не для Differential.

Когда использовать: copy-only удобен для разовой выгрузки перед тестированием или передачей копии на сторону — без влияния на плановый цикл бэкапов.

Методы создания резервных копий

Резервное копирование и восстановление базы данных строятся на двух принципах: логический бэкап (выгрузка данных в файл) и физический (копирование файлов самой СУБД). Для нагруженных production-систем предпочтительнее физический метод: он сохраняет внутреннюю структуру хранения и поддерживает восстановление на точку.

Холодное и горячее резервное копирование

Холодное резервное копирование выполняется при остановленном сервере СУБД. Алгоритм прост: остановить сервис → скопировать файлы данных и нужные журналы → запустить сервис. Преимущество — надёжность и гарантированная согласованность; недостаток — простой системы на время копирования.

Горячее резервное копирование позволяет снять согласованную резервную копию без остановки БД. Для этого СУБД выполняет четыре последовательных шага:

1. Зафиксировать позицию в журнале на момент начала копирования.
2. Выполнить контрольную точку (checkpoint) — сбросить незаписанные изменения из памяти на диск.
3. Перевести файлы данных в особый режим журналирования, блокирующий их заголовки.
4. Скопировать файлы данных, затем завершить режим и сохранить накопленные журналы.

Реализация в API СУБД: Oracle — ALTER DATABASE BEGIN BACKUP / END BACKUP; PostgreSQL — SELECT pg_start_backup(…) / pg_stop_backup(); SQL Server и DB2 реализуют тот же механизм неявно внутри команды BACKUP DATABASE.

Горячий бэкап снижает производительность на время копирования. Ускорение — через снимок файловой системы (ZFS snapshot) или разрыв дисковой пары (BCV-зеркало в решениях EMC/Dell). Для production-систем горячий бэкап предпочтительнее: он не требует простоя.

Выгрузка данных (логический бэкап)

Логический бэкап выгружает данные в текстовый формат (SQL-команды INSERT) или двоичный. Текстовый — портативен, читается в любом редакторе, переносится между версиями СУБД. Двоичный — быстрее за счёт параллельной обработки нескольких таблиц одновременно.

Стандартные инструменты выгрузки по СУБД:

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

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

SQL Server поддерживает три способа управления бэкапами: графический интерфейс SSMS, команды T-SQL (BACKUP DATABASE) и скрипты PowerShell (Backup-SqlDatabase). Хранить копии можно на диске (файл .bak), на ленте или в облаке — Azure Blob Storage и S3-совместимых хранилищах. Права на выполнение бэкапа предоставляют роли sysadmin, db_owner или db_backupoperator.

Освоить администрирование баз данных и резервное копирование можно в рамках федерального проекта «Активные меры содействия занятости» нацпроекта «Кадры» — бесплатно, без отрыва от работы. Доступны программы «1С программист» и «Специалист по аналитике и базам данных в информационных системах». Подробнее — в каталоге программ обучения.

Создание бэкапа через SSMS и T-SQL

В SSMS: правая кнопка на нужной БД → Задачи → Резервное копирование → выбрать тип Full → указать назначение (Disk или URL) → нажать OK. Кнопка Script в диалоге формирует готовый T-SQL-скрипт для дальнейшей автоматизации.

Тот же результат через T-SQL:

BACKUP DATABASE [SQLTestDB]

TO DISK = ‘c:\tmp\SQLTestDB.bak’

WITH FORMAT, COMPRESSION, NAME = ‘Full Backup’;

FORMAT инициализирует носитель и уничтожает предыдущие данные в файле назначения — используйте с осторожностью. NOINIT добавляет бэкап к существующему набору носителей без перезаписи.

Через PowerShell:

Backup-SqlDatabase -ServerInstance Computer\Instance -Database myDB -BackupAction Database

Оценить объём будущего бэкапа до его создания позволяет хранимая процедура sp_spaceused.

Шифрование резервных копий SQL Server

SQL Server поддерживает шифрование резервных копий с алгоритмами AES 128, AES 192, AES 256 и Triple DES 3-Key. Настройка выполняется в три шага:

— 1. Мастер-ключ базы данных master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘S3cur3P@ssw0rd’;

— 2. Сертификат для шифрования

CREATE CERTIFICATE MyCertificate WITH SUBJECT = ‘Backup Encryption’;

— 3. Бэкап с шифрованием

BACKUP DATABASE [myDB]

TO DISK = ‘C:\Backups\myDB_enc.bak’

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate),

COMPRESSION;

Сертификат необходимо сохранить отдельно от зашифрованных копий — без него восстановление невозможно физически. Параметры PASSWORD и MEDIAPASSWORD недоступны начиная с SQL Server 2012 (версия 11.x): шифрование бэкапов — единственный поддерживаемый способ защиты копий.

Резервное копирование PostgreSQL

PostgreSQL предлагает несколько инструментов резервирования БД: pg_dump и pg_dumpall для логических выгрузок, pg_basebackup для физического потокового бэкапа, pg_probackup — для инкрементального. Горячий бэкап реализуется через pg_start_backup() / pg_stop_backup(). В ванильном PostgreSQL block change tracking отсутствует — изменённые страницы определяются через анализ WAL-журналов (журналов предзаписи).

pg_dump и pg_basebackup

pg_dump создаёт логическую выгрузку одной базы. Поддерживает четыре формата:

pg_dump -U postgres -Fc mydb > mydb.dump # custom — рекомендуется

pg_dump -U postgres -Fd mydb -f /backup/ # directory — параллельная выгрузка

pg_dumpall > all.sql # весь кластер

Восстановление: pg_restore -d mydb mydb.dump (форматы custom/directory) или psql mydb < mydb.sql (текстовый формат).

pg_basebackup снимает физический потоковый бэкап без остановки сервера:

pg_basebackup -D /backup/mydb -Ft -z -P

-Ft — формат tar, -z — сжатие, -P — отображение прогресса. Инструмент подходит для полного бэкапа кластера и является основой для PITR: сохраняет WAL-сегменты, необходимые для восстановления на точку.

Выбор: pg_dump — для выборочной выгрузки схемы или отдельных таблиц; pg_basebackup — для полного физического бэкапа с поддержкой PITR.

pg_probackup — инкрементальный бэкап PostgreSQL

pg_probackup разработан компанией Postgres Professional (Россия) для задач инкрементального резервирования баз данных на PostgreSQL. В ванильном PostgreSQL инструмент анализирует WAL-журналы, чтобы определить изменившиеся страницы. При использовании PostgresPro с расширением ptrack изменённые страницы запрашиваются у СУБД напрямую — аналогично тому, как Oracle Recovery Manager (RMAN) работает с block change tracking.

Пример инкрементального бэкапа типа DELTA:

pg-probackup backup -B /backup —instance mydb -b DELTA

pg_probackup умеет объединять инкременты в новую полную копию — механизм, аналогичный Oracle ZDLRA incremental forever: один раз создаётся полная копия, далее только инкрементальные. Это снижает суммарный объём хранения и ускоряет восстановление по сравнению с регулярными полными бэкапами.

Резервное копирование MySQL

Для резервного копирования базы данных MySQL существуют два подхода: mysqldump — логический бэкап в текстовый SQL-файл, стандарт для небольших и средних систем; Percona XtraBackup — физический горячий бэкап без остановки сервера для нагруженных production-систем.

mysqldump и горячее резервное копирование MySQL

Базовая команда mysqldump:

mysqldump -u root -p —single-transaction mydb > mydb.sql

—single-transaction создаёт согласованный снимок таблиц InnoDB без блокировок других операций. —all-databases выгружает все БД сервера одновременно.

Восстановление:

mysql -u root -p mydb < mydb.sql

Percona XtraBackup снимает физический горячий бэкап MySQL без остановки сервера — аналог Oracle Recovery Manager для этой СУБД. Поддерживает инкрементальные бэкапы и PITR.

Автоматизация через cron:

0 3 * * * mysqldump -u root -p mydb > /backup/mydb_$(date +\%F).sql

Резервная копия 1С:Предприятие

1С:Предприятие работает в двух режимах хранения информационной базы (ИБ): файловый (данные в папке на диске) и клиент-серверный (данные в MS SQL Server или PostgreSQL). Метод резервирования зависит от режима.

Метод 1 — через Конфигуратор (файловый режим):

Конфигуратор → Администрирование → Выгрузить информационную базу → выбрать путь → сохранить в файл .dt.

Файл .dt — архив всей ИБ в формате 1С. Восстановление: Администрирование → Загрузить информационную базу. Метод подходит для файловых баз; для больших объёмов данных работает медленно.

Метод 2 — через SQL Server Agent Job (клиент-серверный режим):

При хранении ИБ в MS SQL Server используются те же команды BACKUP DATABASE, что описаны выше. Удобнее всего настроить SQL Server Agent Job: создать шаг типа T-SQL с командой бэкапа и выставить расписание.

Метод 3 — холодный бэкап (файловый режим):

Остановить сервер 1С → скопировать всю папку ИБ → запустить сервис. Надёжен, гарантированно согласован, но требует кратковременного простоя.

Рекомендуемая частота: ежедневно. Хранить не менее трёх последних копий. В файловом режиме автоматизацию настраивают через Windows Task Scheduler с bat-скриптом.

Восстановление базы данных из резервной копии

Резервное копирование и восстановление базы данных — единый процесс. Бэкап ценен только если восстановление проверено и работает. Базовые команды: RESTORE DATABASE (SQL Server), pg_restore / psql (PostgreSQL), mysql (MySQL). Порядок применения шагов в цепочке нарушать нельзя.

RESTORE DATABASE, pg_restore и восстановление на точку (PITR)

SQL Server — восстановление полной копии:

RESTORE DATABASE [myDB]

FROM DISK = ‘C:\Backups\myDB.bak’

WITH RECOVERY;

RECOVERY переводит БД в рабочий режим. NORECOVERY оставляет базу в режиме ожидания для применения следующих копий из цепочки.

Ограничение совместимости: бэкап SQL Server 2022 нельзя восстановить на SQL Server 2019 — обратная совместимость версий не поддерживается.

PostgreSQL:

pg_restore -d mydb mydb.dump # custom/directory формат

psql mydb < mydb.sql # текстовый формат

Процедура PITR:

1. Восстановить полную РК с параметром NORECOVERY — оставить БД в режиме ожидания.
2. Последовательно применить инкрементальные / дифференциальные копии.
3. Применить журналы транзакций до нужного момента времени.

Скорость PITR: применение бэкапа ограничено пропускной способностью диска; применение журналов — производительностью CPU (операции последовательны). Вывод практический: чем чаще бэкапы, тем меньше журналов нужно применять и тем быстрее восстановление.

Автоматизация и стратегия резервного копирования

Пропущенный бэкап — частая причина потери данных при инцидентах. Правило резервного копирования данных простое: бэкап создаётся автоматически, без участия человека. Для SQL Server и Windows — SQL Server Agent Job; для Linux — cron. Частоту и тип копий определяют через две метрики: допустимую потерю данных (RPO) и допустимое время простоя (RTO).

SQL Server Agent Job и cron: настройка расписания

SQL Server Agent Job: SQL Server Agent → New Job → добавить шаг типа T-SQL → вставить команду BACKUP DATABASE → настроить расписание (ежедневно / еженедельно) → сохранить.

Cron-пример для pg_dump (PostgreSQL):

0 2 * * * pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).dump

Cron-пример для mysqldump (MySQL):

0 3 * * * mysqldump -u root -p mydb > /backup/mydb_$(date +%F).sql

Хранение по правилу 3-2-1: три копии, два разных носителя (например, диск и объектное хранилище), одна копия вне офиса. Облачные варианты: Azure Blob Storage, S3-совместимые сервисы.

Ротация — автоматически удалять копии старше N дней:

find /backup -name «*.dump» -mtime +7 -delete

RPO, RTO и выбор частоты резервного копирования

Допустимая потеря данных (RPO, Recovery Point Objective) — определяет максимальный интервал между бэкапами. RPO = 1 час означает: копия журнала транзакций каждый час, не реже. RPO = 24 часа — достаточно ежедневного полного бэкапа.

Допустимое время простоя (RTO, Recovery Time Objective) — влияет на выбор типа бэкапа и структуру хранения. Чем меньше RTO, тем быстрее должна работать инфраструктура восстановления и тем ближе должны быть актуальные копии.

Практические ориентиры стратегии резервного копирования:

Выбор типа инкремента с учётом RTO: кумулятивный (Differential в SQL Server) восстанавливается за 2 шага — быстрее; инкрементальный даёт меньший объём каждой копии, но требует N шагов при восстановлении.

Часто задаваемые вопросы

Чем резервная копия базы данных отличается от репликации?

Реплика воспроизводит все операции, включая ошибочные: удалённые данные исчезнут и в реплике. Резервная копия фиксирует состояние на конкретный момент и позволяет восстановить данные после DROP TABLE, ошибочного DELETE или повреждения структур СУБД. Реплику без базовой резервной копии вообще нельзя инициализировать.

Как создать резервную копию базы данных SQL Server через SSMS?

Откройте SSMS → обозреватель объектов → правая кнопка на БД → Задачи → Резервное копирование. Выберите тип (Full), укажите назначение (Disk / URL). Нажмите OK. Кнопка Script генерирует готовый T-SQL для автоматизации.

Как настроить резервное копирование базы данных по расписанию?

SQL Server: SQL Server Agent → New Job → шаг T-SQL с BACKUP DATABASE → Schedule (ежедневно). Linux: crontab -e, добавить 0 2 * * * pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).dump. Настроить ротацию — удалять копии старше 7 дней.

Как сделать резервную копию PostgreSQL?

Логический бэкап: pg_dump -U postgres -Fc mydb > mydb.dump. Физический потоковый: pg_basebackup -D /backup/mydb -Ft -z -P. Восстановление: pg_restore -d mydb mydb.dump. Для инкрементального бэкапа — pg_probackup от Postgres Professional.

Как в 1С сделать резервное копирование базы данных?

Файловый режим: Конфигуратор → Администрирование → Выгрузить информационную базу → .dt-файл. Клиент-серверный режим (1С на MS SQL): BACKUP DATABASE через SQL Server Agent Job. Альтернатива — скопировать папку ИБ при остановленном сервере 1С (холодный бэкап).

Что такое восстановление на точку (point-in-time recovery)?

Восстановление на точку — возврат БД на произвольный момент времени, не только на момент бэкапа. Требует: базовый бэкап + непрерывно сохраняемые журналы транзакций. Порядок: RESTORE полной РК (NORECOVERY) → инкременты → журналы до нужного момента.

Чем дифференциальная резервная копия отличается от инкрементальной?

Дифференциальная (разностная) сохраняет изменения от последней полной — восстановление: 2 шага. Инкрементальная — от любого предыдущего бэкапа — восстановление: N шагов, но меньший объём каждой копии. Важно: SQL Server называет кумулятивную копию «Differential» — это другая терминология, чем у Oracle.

Как зашифровать резервную копию базы данных SQL Server?

1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘…’; 2) CREATE CERTIFICATE MyCertificate WITH SUBJECT = ‘Backup’; 3) добавьте в BACKUP DATABASE: WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate). Сохраните сертификат отдельно — без него восстановление невозможно.