Backup и восстановление SQL Server: файлы .bak без Agent

Backup в SQL Server - это одно выражение: BACKUP DATABASE [appdb] TO DISK = N'/path/appdb.bak' WITH CHECKSUM, INIT;. Оно записывает согласованную копию базы в один файл .bak, пока база продолжает обслуживать трафик, а RESTORE DATABASE возвращает её на место - на том же сервере или на любом другом SQL Server той же или более новой версии. Спотыкаются люди не на самом выражении, а на том, что вокруг него: в Express нет SQL Server Agent, чтобы запускать его по расписанию, файл оказывается на диске сервера, а не на вашем, восстановление на другой сервер требует WITH MOVE, а восстановленные логины приходят отвязанными от своих пользователей.

Это руководство разбирает всё это для SQL Server 2022 и отдельно отмечает ограничения редакции Express там, где они важны. Большая часть без изменений относится к версиям с 2016 по 2019, а также и к Windows, и к Linux.

Что содержит нативный backup#

BACKUP DATABASE - это не копирование файла. SQL Server читает каждую выделенную страницу данных, а затем дописывает столько журнала транзакций, сколько нужно, чтобы копия была согласованной на момент окончания backup. При восстановлении движок проигрывает эту часть журнала и откатывает всё незафиксированное. В результате получается база, существовавшая в один конкретный момент, со всеми целыми внешними ключами - ровно то, чего не может обещать копия файла .mdf, снятая на работающем сервере.

Типов backup три, и Express поддерживает все:

Разностный backup накопительный, а не инкрементный: разностный backup среды содержит всё, что изменилось после полного backup воскресенья, включая то, что уже было в backup понедельника и вторника. Для восстановления нужен полный backup плюс один самый свежий разностный. Backup журнала устроены наоборот - каждый содержит только свой отрезок времени, и для восстановления на момент времени нужна непрерывная цепочка. Нужны ли они вам вообще, зависит от модели восстановления, которую подробно разбирает статья модели восстановления SQL Server и рост журнала. Для базы меньше 10 ГБ с ночным полным backup и допустимой потерей в час одних полных backup вполне достаточно для приличного плана.

Чего backup не содержит, так это всего, что находится вне базы. Логины живут в master, задания - в msdb, настройки сервера - в экземпляре. Файл .bak базы appdb, восстановленный в другом месте, приносит с собой пользователей базы, но не логины, к которым они привязаны. Это самый частый сюрприз после восстановления, и ему посвящён отдельный раздел ниже.

Полный backup#

Подключитесь как sa или под любым логином с ролью db_backupoperator в базе и выполните:

BACKUP DATABASE [appdb]TO DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH CHECKSUM, INIT, STATS = 10,     NAME = N'appdb full 2026-10-08';

Что делает каждый параметр:

• CHECKSUM - проверяет контрольные суммы страниц при чтении и записывает контрольную сумму всего backup. Если страница уже повреждена, backup завершается ошибкой, а не тихо сохраняет повреждение. Хорошей причины его не указывать нет.
• INIT - перезаписывает наборы backup, уже лежащие в файле. Без него SQL Server дописывает в конец файла, и .bak, в который год каждую ночь что-то дописывали, превращается в сюрприз на 300 ГБ с 365 backup внутри. Используйте новое имя файла для каждого backup и INIT либо одно имя и INIT, но никогда не дописывайте случайно.
• STATS = 10 - выводит прогресс каждые 10 процентов, и так можно отличить медленный backup от зависшего.
• COPY_ONLY - добавляйте его к разовому backup вне обычного расписания. Он не сбрасывает основу для разностных backup, так что следующий разностный по расписанию по-прежнему опирается на полный по расписанию, а не на вашу разовую копию.

Путь указывается на сервере. Это стоит повторить, потому что путает всех, кто впервые запускает backup из SSMS на своём ноутбуке: TO DISK разрешает процесс SQL Server на той машине, где SQL Server работает. Путь вроде C:\Backups\appdb.bak на Linux-сервере - это не ваш диск C:. Узнать, где сервер по умолчанию хранит backup, можно так:

SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS backup_dir,       SERVERPROPERTY('InstanceDefaultDataPath')   AS data_dir;

InstanceDefaultBackupPath существует начиная с SQL Server 2019. В стандартной установке на Linux оба значения обычно указывают на /var/opt/mssql/data/, если только кто-то не изменил их через mssql-conf. На Linux процесс SQL Server работает от пользователя mssql, и ему нужно право записи в тот каталог, который вы указываете.

Проверка backup до того, как он понадобится#

Файл backup, который ни разу не читали, - это гипотеза. SQL Server даёт три дешёвые проверки, и ни одна из них не трогает рабочую базу:

-- Is the file readable and complete? Checks the checksums too.RESTORE VERIFYONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH CHECKSUM;-- What is in the file: database name, type, dates, version.RESTORE HEADERONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak';-- Which data and log files the database had, by logical name.RESTORE FILELISTONLYFROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak';

VERIFYONLY доказывает, что файл цел. Он не доказывает, что здорова база внутри - база, логически повреждённая до backup, сохраняется в backup со всей точностью. Единственная настоящая проверка - восстановить файл под новым именем и запустить DBCC CHECKDB на копии:

DBCC CHECKDB (N'appdb_verify') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Отсутствие вывода означает отсутствие ошибок. После этого удалите копию. На базе Express в 10 ГБ это занимает минуты, и делать так раз в месяц - это разница между тем, чтобы иметь backup, и тем, чтобы верить, что они есть. Общие доводы приводит статья проверка восстановления до того, как оно понадобится.

История backup хранится в msdb, и по ней удобно проверять, что задание по расписанию действительно отработало:

SELECT TOP (20) database_name, type, backup_start_date,       backup_finish_date, backup_size / 1048576 AS size_mb,       physical_device_nameFROM msdb.dbo.backupset AS bJOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_idORDER BY backup_finish_date DESC;

type равен D для полного, I для разностного и L для backup журнала.

Восстановление: тот же сервер, новое имя или другой сервер#

При восстановлении важны логические имена файлов из FILELISTONLY. У базы есть как минимум один файл данных и один файл журнала, у каждого - логическое имя (например, appdb и appdb_log) и физический путь. Если не сказать иначе, восстановление пытается вернуть файлы по исходным физическим путям, и это не удаётся, если эти пути заняты или не существуют на этой машине.

Чтобы восстановить базу рядом с исходной под новым именем - безопасный способ вернуть удалённую таблицу, не трогая рабочую базу:

RESTORE DATABASE [appdb_restore]FROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH MOVE N'appdb'     TO N'/var/opt/mssql/data/appdb_restore.mdf',     MOVE N'appdb_log' TO N'/var/opt/mssql/data/appdb_restore_log.ldf',     RECOVERY, STATS = 10;

Затем перенесите нужные строки обратно обычным INSERT ... SELECT между двумя базами и удалите appdb_restore.

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

USE master;ALTER DATABASE [appdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE [appdb]FROM DISK = N'/var/opt/mssql/data/appdb-2026-10-08.bak'WITH REPLACE, RECOVERY, STATS = 10;ALTER DATABASE [appdb] SET MULTI_USER;

ROLLBACK IMMEDIATE отключает всех и откатывает их открытые транзакции. REPLACE сообщает SQL Server, что вы действительно хотите перезаписать существующую базу; без него восстановление поверх базы, журнал которой не был сохранён в backup, останавливается с ошибкой 3159 и просит сначала сделать backup хвоста журнала.

Backup, сделанный на Windows, восстанавливается на Linux, и наоборот. Формат файла одинаковый, отличаются только пути, так что каждый файл нужно перенести через MOVE на путь Linux. Правило версий строгое в одну сторону: backup восстанавливается на ту же версию или на более новую, но никогда на более старую. Backup SQL Server 2022 не восстановится на 2019, что бы вы ни пробовали. Чтобы спуститься на версию ниже, нужен BACPAC или скрипты - оба варианта разобраны в статье перенос базы на хостинг SQL Server.

Параметр RECOVERY завершает восстановление и открывает базу. NORECOVERY оставляет её в состоянии «Restoring...», чтобы можно было применить следующие backup - разностный, затем backup журнала. Если забыть финальный RECOVERY, база так и останется в этом состоянии и будет казаться зависшей; её завершает отдельное выражение RESTORE DATABASE [appdb] WITH RECOVERY;.

Логины, пользователи и проблема осиротевших пользователей#

Внутри базы пользователь связан с логином сервера через идентификатор безопасности (SID). Логины с SQL-аутентификацией получают на каждом сервере случайный SID. Восстановите базу на другой сервер, создайте логин с тем же именем - и пользователь с логином всё равно не совпадут: пользователь осиротел, и приложение получает Login failed for user 'app', хотя существуют оба.

Найти осиротевших пользователей в восстановленной базе:

SELECT dp.name AS user_name, dp.sidFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type = 'S'  AND sp.sid IS NULL  AND dp.authentication_type_desc = 'INSTANCE';

Исправить каждого, привязав его к логину:

ALTER USER [app] WITH LOGIN = [app];

Старая процедура sp_change_users_login делает то же самое, но объявлена устаревшей; используйте ALTER USER. Чтобы при запланированном переезде вообще избежать проблемы, создайте логин на новом сервере с исходным SID (CREATE LOGIN [app] WITH PASSWORD = '...', SID = 0x...), взяв SID из sys.server_principals на старом. Модель как следует объясняет статья логины, пользователи и роли SQL Server.

Backup по расписанию, когда нет SQL Server Agent#

В Standard и Enterprise ночной backup запускает задание SQL Server Agent или план обслуживания. В Express SQL Server Agent нет вообще, ни на Windows, ни на Linux, поэтому расписание должно приходить извне движка. Это меньшее ограничение, чем кажется: backup - это одно выражение, и всё, что умеет запускать sqlcmd по таймеру, умеет его сделать.

Поместите backup в скрипт, который даёт файлу имя по дате:

DECLARE @file nvarchar(400) =    N'/var/opt/mssql/data/appdb-' + CONVERT(char(8), SYSUTCDATETIME(), 112) + N'.bak';BACKUP DATABASE [appdb] TO DISK = @fileWITH CHECKSUM, INIT, STATS = 25;

Затем запускайте его с любой машины, которая видит сервер, - с вашего собственного Linux-сервера, небольшого сервера приложений, из задания CI:

$ export SQLCMDPASSWORD='the-generated-sa-password'$ sqlcmd -S db.example.net,14330 -U sa -C -b -i backup.sql -o backup.log

-b заставляет sqlcmd при ошибке завершаться с ненулевым кодом, так что cron или раннер CI отличат неудавшийся backup от удачного. -C доверяет сертификату сервера - это нужно с инструментами версии 18, потому что они по умолчанию шифруют соединение; флаги разобраны в статье sqlcmd и bcp. Строка crontab после этого самая обычная:

15 3 * * * /usr/local/bin/sql-backup.sh >> /var/log/sql-backup.log 2>&1

На Windows с этим справляется Task Scheduler, запускающий ту же строку sqlcmd. Для чего-то посложнее - несколько баз, разностные backup по будням, удаление старых файлов - стандартный ответ на Express это бесплатное решение для обслуживания от Ola Hallengren: один раз установите его хранимые процедуры, а затем вызывайте dbo.DatabaseBackup из sqlcmd по расписанию вместо голого BACKUP. SQL Server на Linux оно поддерживает уже много лет; какие параметры очистки там работают, смотрите в его документации.

В такой схеме легко упустить две вещи. Файл backup всё равно пишется на диск сервера базы данных, а не на машину, где запущен sqlcmd, так что чистить его нужно там же и копировать оттуда. А backup, лежащий на том же диске, что и база, защищает от неудачного DELETE, но не от потери сервера.

Как забрать .bak с сервера#

Backup на той же машине, что и база, - это половина backup. Нужна копия где-то ещё, в идеале там, где она переживёт удаление сервера. Реалистичных путей три:

1. Скачать файл. Если хостер даёт вам доступ к файлам сервера, забирайте .bak по SFTP после окончания backup и удаляйте старые на сервере. Это самый простой и самый переносимый вариант.
2. Делать backup сразу в объектное хранилище. В SQL Server 2022 появился BACKUP ... TO URL с адресом s3:// для S3-совместимого хранилища и аутентификацией через учётные данные с ключом доступа и секретом. Нужен HTTPS-endpoint, сертификату которого доверяет сервер, и до того, как на это полагаться, стоит проверить работу на вашей редакции.
3. Экспортировать логически. SqlPackage /Action:Export, запущенный на вашей машине, записывает .bacpac локально через обычное соединение. Это медленнее нативного backup и не согласовано транзакционно, если во время экспорта идут записи, зато доступ к файлам сервера не нужен вовсе.

В RE:NODE линейка SQL Server работает на SQL Server 2022 Express под Linux с одним-четырьмя слотами backup в зависимости от тарифа. Backup панели делаются по запросу или по расписанию на вкладке Schedules, хранятся вне защищаемой машины, скачиваются и восстанавливаются кнопкой - но это копия всего сервера, и удаление сервера удаляет и их. Разумная схема - запускать нативный BACKUP за несколько минут до backup панели по расписанию, чтобы в архиве всегда был согласованный .bak, и регулярно скачивать один из них туда, где всё принадлежит только вам. У каждого сервера также есть SFTP и файловый менеджер, чтобы забирать файлы напрямую. Почему две независимые копии ломаются лучше, чем одна хорошая, объясняет статья backup, из которых действительно восстанавливаются.

Решение проблем с backup и восстановлением#

Ошибка операционной системы 5 (Access is denied) или ошибка 2 (cannot find the file). Путь разрешает процесс SQL Server на сервере. На Linux пользователю mssql нужно право записи в каталог, и каталог должен существовать - BACKUP папки не создаёт. На Linux пути чувствительны к регистру, так что /var/opt/mssql/Data - это не /var/opt/mssql/data.

Ошибка 3154: набор backup содержит backup базы, отличной от существующей. Вы восстанавливаете поверх этой базы backup другой базы. Добавьте REPLACE, если вы действительно этого хотите, или восстановите под новым именем.

Ошибка 3101: не удалось получить монопольный доступ, потому что база используется. Что-то подключено - часто ваше же окно запросов. Выполните USE master, затем переведите базу в SINGLE_USER WITH ROLLBACK IMMEDIATE, как показано выше.

Ошибка 3169: backup базы сделан на сервере с более новой версией. Правило версий. Ничто не заставит этот .bak восстановиться на более старом сервере; используйте BACPAC или скрипты.

Восстановление не удаётся, потому что база превысила бы 10240 МБ. Express ограничивает файлы данных каждой базы 10 ГБ. Backup более крупной базы из Standard на Express не восстановится. Сначала заархивируйте или удалите данные на исходной стороне либо переходите на редакцию без этого потолка.

База застряла в состоянии «Restoring...». Последний шаг восстановления использовал NORECOVERY. Выполните RESTORE DATABASE [appdb] WITH RECOVERY;, чтобы перевести её в рабочее состояние.

FAQ#

Можно ли из SSMS сделать backup базы SQL Server на свой компьютер?

Напрямую нет. Backup записывает процесс сервера на собственный диск сервера, даже если вы прокликиваете диалог SSMS на своём ноутбуке. Сделайте backup на сервере и скачайте файл либо экспортируйте BACPAC - его SqlPackage и SSMS записывают на локальную машину.

Как часто делать backup небольшой базы SQL Server?

Ночные полные backup подходят большинству приложений, плюс дополнительный backup с COPY_ONLY перед каждым deploy или изменением схемы. Если потеря дня записей будет болезненной, добавьте разностные backup в течение дня или перейдите на модель восстановления full с backup журнала каждые 15-60 минут.

Блокирует ли BACKUP DATABASE таблицы?

Нет. Чтение и запись продолжаются во время backup. Он тратит чтение с диска и немного CPU, так что запускайте его, когда база не нагружена, но блокировок от самого backup пользователи не увидят. Несколько операций, например сжатие файла, не могут выполняться одновременно с backup.

Можно ли восстановить backup из SQL Server на Windows в SQL Server на Linux?

Да. Формат .bak одинаков на обеих системах. Прочитайте логические имена файлов через RESTORE FILELISTONLY, затем перенесите каждый файл WITH MOVE на путь Linux, например /var/opt/mssql/data/. Правило версий по-прежнему действует: только та же или более новая.

Почему backup в Express размером с саму базу?

Потому что Express не умеет писать сжатые backup. Файл примерно равен размеру занятых страниц, а не выделенному размеру файла. Если важны место или время передачи, сожмите его gzip или zstd после записи; на большинстве данных приложений ожидайте заметного уменьшения.

Является ли копия файлов .mdf и .ldf полноценным backup?

Только если при копировании SQL Server был остановлен или база отсоединена, и даже тогда она привязана к этой версии и редакции. Копия, снятая на работающем сервере, может не подключиться. Поддерживаемый способ - BACKUP DATABASE, и он ничего дополнительно не стоит.