Как уменьшить размер резервной копии базы данных 1С на MS SQL Server?

← На главную

При администрировании баз данных 1С:Предприятия на платформе MS SQL Server системные администраторы часто сталкиваются с парадоксальной ситуацией. Сама база данных в формате выгрузки 1С (файл .dt) занимает всего несколько сотен мегабайт, реальный объем полезных данных в СУБД составляет около 1–2 гигабайт, но файл резервной копии .bak внезапно разрастается до 20–30 гигабайт и более. В этой статье мы подробно разберем, почему так происходит, развеем популярные мифы о работе SQL-сервера с дисковым пространством и рассмотрим практические шаги по уменьшению размера бэкапов.

Почему резервная копия SQL Server получается такой большой?

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

Резервная копия MS SQL Server — это физический бэкап. Он создается на уровне страниц данных (размером 8 КБ) и экстентов (блоков по 64 КБ). SQL Server копирует физическую структуру базы данных, чтобы гарантировать мгновенное восстановление в точно такое же состояние без необходимости перестраивать индексы или заново инициализировать структуру таблиц.

Заблуждение о копировании пустого места в файлах данных

Среди начинающих администраторов и участников форумов существует популярное заблуждение: якобы MS SQL Server при создании обычного бэкапа без сжатия копирует абсолютно все файлы базы данных целиком, включая все нераспределенное (пустое) пространство. Это техническая ошибка. На самом деле процесс устроен следующим образом:

При создании полного бэкапа (Full Backup) SQL Server обращается к картам распределения страниц (GAM и SGAM) и копирует только те экстенты, которые были выделены (allocated) под данные.
Если файл базы данных .mdf физически занимает на диске 100 ГБ, но реально данными заполнено только 2 ГБ, то несжатый бэкап будет весить чуть более 2 ГБ. Остальные 98 ГБ нераспределенного свободного пространства (unallocated space) в файл резервной копии не записываются.
Однако, если внутри выделенных страниц данных присутствует так называемая внутренняя фрагментация (когда страницы заполнены лишь частично из-за настроек параметров индексации), это пустое пространство внутри страниц считается выделенным и попадает в бэкап.

Рассмотрим основные причины, по которым размер бэкапа базы 1С может многократно превышать реальный объем данных, и разберем способы решения этой проблемы.

Причина №1: Накопление нескольких резервных копий в одном файле

Самая частая причина лавинообразного роста файла бэкапа — это использование режима дозаписи. По умолчанию, если в настройках резервного копирования (в T-SQL скрипте или в Плане обслуживания) не указана принудительная перезапись, SQL Server использует параметр NOINIT. Это приводит к тому, что при каждом запуске резервного копирования новый бэкап дописывается в конец уже существующего файла .bak. Если база весит 1 ГБ, а резервное копирование выполняется ежедневно в течение месяца, размер файла закономерно достигнет 30 ГБ.

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


DECLARE @DatabaseName NVARCHAR(100) = N'TradeManagement'
DECLARE @BackupPath NVARCHAR(500) = N'D:\SQL_Backups\TradeManagement_Full.bak'

BACKUP DATABASE @DatabaseName
TO DISK = @BackupPath
WITH FORMAT,
     INIT,
     NAME = N'Полный бэкап Управление торговлей',
     SKIP,
     STATS = 10;
GO

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

Причина №2: Отсутствие сжатия резервных копий

По умолчанию в старых версиях MS SQL Server (или при стандартных настройках новых версий) резервные копии создаются без сжатия. Поскольку базы данных 1С содержат огромное количество текстовой информации, табличных частей и строковых полей, они обладают колоссальным потенциалом для сжатия. Использование встроенного сжатия SQL Server позволяет уменьшить итоговый размер файла бэкапа в 5–10 раз.

Рассмотрим, как задействовать сжатие резервных копий. Для разового создания сжатого бэкапа мы можем использовать параметр WITH COMPRESSION в нашей команде резервного копирования. Перепишем наш скрипт с добавлением сжатия:


DECLARE @DatabaseName NVARCHAR(100) = N'TradeManagement'
DECLARE @BackupPath NVARCHAR(500) = N'D:\SQL_Backups\TradeManagement_Compressed.bak'

BACKUP DATABASE @DatabaseName
TO DISK = @BackupPath
WITH FORMAT,
     INIT,
     COMPRESSION,
     NAME = N'Сжатый бэкап Управление торговлей',
     SKIP,
     STATS = 10;
GO

Если резервное копирование настраивается через графический интерфейс СУБД SQL Server Management Studio (SSMS), выполним следующие действия:

Нажмите правой кнопкой мыши на нужную базу данных, выберите Задачи (Tasks) -> Создать резервную копию (Back Up).
Перейдите на вкладку Параметры резервного копирования (Backup Options).
В самом низу в поле Сжатие резервной копии (Set backup compression) выберите вариант Сжимать резервную копию (Compress backup) вместо настройки по умолчанию.

Чтобы не прописывать параметр сжатия в каждом скрипте или плане обслуживания, мы рекомендуем включить сжатие бэкапов по умолчанию на уровне всего SQL-сервера. Для этого выполним следующий системный запрос под учетной записью с правами администратора (sysadmin):


EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

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

Причина №3: Избыточный рост журнала транзакций (.ldf)

Еще один важный фактор, влияющий на общие накладные расходы и размер бэкапа — это состояние журнала транзакций. Каждая база данных SQL Server работает в определенной модели восстановления. Проанализируем две основные модели, применяемые для баз данных 1С:

Модель восстановления FULL (Полная): В этом режиме все изменения данных непрерывно записываются в файл .ldf. Журнал транзакций будет расти до бесконечности, пока вы не сделаете бэкап самого журнала транзакций (командой BACKUP LOG). Обычный полный бэкап базы данных (BACKUP DATABASE) не усекает и не очищает журнал транзакций. Если бэкапы логов не настроены, файл лога раздувается до сотен гигабайт, и хотя сам он целиком в бэкап базы не копируется, активная часть транзакций на момент создания резервной копии может значительно увеличить размер бэкапа.
Модель восстановления SIMPLE (Простая): В этой модели SQL Server автоматически усекает журнал транзакций сразу после фиксации операций (чекпоинта). Журнал не растет бесконтрольно, и его размер остается стабильно небольшим. Данная модель идеально подходит для баз 1С, где допустима потеря данных за текущий день в случае аварии, а восстановление производится из ежедневных ночных копий.

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


ALTER DATABASE [TradeManagement] SET RECOVERY SIMPLE;
GO

После изменения модели восстановления рекомендуется выполнить операцию усечения (сжатия) файла журнала транзакций до разумных пределов (например, до 1024 МБ), чтобы освободить место на жестком диске:


USE [TradeManagement];
GO
-- Выясним логическое имя файла журнала транзакций с помощью запроса
SELECT name FROM sys.database_files WHERE type_desc = 'LOG';
GO
-- Сжимаем файл журнала до 1024 МБ (замените TradeManagement_log на полученное имя)
DBCC SHRINKFILE (N'TradeManagement_log', 1024);
GO

Причина №4: Сильная фрагментация индексов и параметр Fill Factor

В базах данных 1С происходит постоянное добавление, изменение и удаление документов, проводок и записей регистров. Это приводит к сильной фрагментации индексов. Для борьбы с этим администраторы настраивают регулярную переиндексацию.

Однако здесь кроется неочевидная деталь — параметр Fill Factor (коэффициент заполнения страниц). Если при переиндексации установлен низкий Fill Factor (например, 80% или ниже), это означает, что SQL Server при перестроении индекса намеренно оставляет на каждой странице данных 20% свободного пространства под будущие записи. С точки зрения СУБД эти страницы помечены как выделенные (allocated), поэтому при создании резервной копии SQL Server будет обязан скопировать их в файл бэкапа целиком, вместе со всем внутренним пустым пространством. Это приводит к необоснованному увеличению размера базы данных и ее бэкапов.

Для оптимизации баз данных 1С мы рекомендуем придерживаться следующих правил:

Для таблиц с невысокой интенсивностью записи держите параметр Fill Factor на уровне 95–100%.
Для высоконагруженных таблиц (например, регистров накопления или сведений, куда постоянно пишутся новые данные) допускается снижение Fill Factor до 85–90%.
Регулярно проводите дефрагментацию индексов (Reorganize) вместо полного перестроения (Rebuild), если уровень фрагментации не превышает 30%. Реорганизация не приводит к раздуванию выделенных страниц и не вызывает лавинообразного роста журнала транзакций.

Использование профессиональных решений для обслуживания баз данных

Для настройки эффективного и бережного резервного копирования стандартные Планы обслуживания (Maintenance Plans) в среде MS SQL Server часто оказываются слишком грубыми и неэффективными. Общепризнанным стандартом среди администраторов баз данных 1С является бесплатный пакет скриптов Олы Халленгрена (Ola Hallengren's SQL Server Maintenance Solution).

Этот комплекс скриптов позволяет:

Интеллектуально оценивать текущую фрагментацию индексов перед началом обслуживания и выполнять перестроение (Rebuild) или реорганизацию (Reorganize) только для тех таблиц, где это действительно необходимо. Это предотвращает излишнюю генерацию логов транзакций и экономит ресурсы процессора.
Автоматически создавать сжатые резервные копии баз данных с динамическим именованием файлов, ведением подробных логов операций и автоматическим удалением устаревших архивов (ротацией бэкапов) по истечении заданного жизненного цикла.

Выводы и рекомендации

Резюмируя все вышесказанное, для минимизации размера резервных копий баз данных 1С на MS SQL Server нам необходимо выполнить следующий комплекс действий:

Включить сжатие резервных копий по умолчанию на уровне всего SQL-сервера с помощью процедуры sp_configure 'backup compression default', 1.
Использовать параметр перезаписи INIT (или FORMAT) в скриптах резервного копирования, чтобы избежать накопления нескольких копий баз внутри одного файла .bak.
Перевести неоригинальные или тестовые базы данных 1С в простую модель восстановления (SIMPLE), а для рабочих баз в полной модели восстановления (FULL) настроить регулярное резервное копирование логов транзакций не реже одного раза в час.
Проводить регулярный мониторинг фрагментации индексов и оптимизировать коэффициент заполнения страниц Fill Factor, удерживая его в диапазоне 90–100%, чтобы избежать переноса внутренней пустоты страниц в бэкап.

Применение этих простых системных настроек позволит вам сократить затраты на дисковые хранилища резервных копий в несколько раз, повысить скорость создания бэкапов и минимизировать время восстановления баз данных в случае нештатных ситуаций.