Как настроить автоматический бэкап базы данных MS SQL Server

Автоматический бэкап в MS SQL Server можно сделать несколькими способами. Самый простой для лицензий и редакций с агентом - через SQL Server Agent. Для SQL Server Express (агент обычно не включён) обычно используют связку: sqlcmd + командный файл .bat + Планировщик заданий Windows.

Ниже - рабочие варианты под типичный сценарий: делать полные и/или разностные бэкапы по расписанию и хранить файлы в папке, а при желании - копировать их на другой сервер.

Выберите подходящий вариант

Автоматический бэкап в SQL Server Agent (если агент доступен)

1. Откройте SQL Server Management Studio и подключитесь.
2. Создайте устройство резервного копирования (если используете подход “устройства”):
3. Раздел Объекты сервера → ПКМ → Создать устройство резервного копирования
4. Задайте имя и путь к файлу.
5. Создайте задание:
6. Агент SQL Server → ПКМ → Задания → Создать задание
7. Добавьте шаг с командой резервного копирования:
8. Тип шага: Сценарий Transact-SQL (T-SQL)
9. Команда вида:
   sql BACKUP DATABASE [YourDB] TO [YourBackupDevice] WITH INIT, NOFORMAT, SKIP, NOUNLOAD;
10. (Опционально) Добавьте второй шаг - копирование файла на другой server:
11. Тип шага: Операционная система (CmdExec)
12. Команда копирования, например:
    bat XCOPY D:\MSSQL\BACKUP\YourDB_monday.bak \\BACKUP_SERVER\Folder\*.* /Y
13. Перейдите на вкладку Расписания:
14. Создайте расписание Повторяющееся
15. Укажите дни и время запуска (обычно ночное окно).
16. Протестируйте: ПКМ по заданию → Запустить (и проверьте папку с файлами и журнал).

Важно про права: учетная запись, от имени которой работает SQL Server Agent, должна иметь права на чтение/запись в каталог с резервными файлами и доступ к целевой файловой системе (включая сетевую папку).

Автоматический бэкап для SQL Server Express: Планировщик Windows + sqlcmd

В Express чаще всего нет SQL Server Agent, поэтому задача решается через Windows.

Шаг 1. Создайте хранимую процедуру sp_BackupDatabases

Скачайте готовый скрипт Microsoft для Express (он создаёт процедуру, которую можно вызывать из командной строки). Затем выполните его в вашей master базе на server с Express.

Суть: после этого вы сможете запускать бэкап командой EXEC sp_BackupDatabases ....

(Источник: скрипт и подход описаны в Microsoft Learn для Express.)

Шаг 2. Убедитесь, что доступен sqlcmd

Для SQL Server 2016 и новее sqlcmd обычно доступен отдельной загрузкой, для более старых - может быть установлен по умолчанию.

Шаг 3. Сформируйте .bat файл для бэкапа

Создайте файл, например Sqlbackup.bat, и укажите свои параметры.

Пример для полного бэкапа выбранной базы (Windows-аутентификация):

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @databaseName='YourDB', @backupLocation='D:\SQLBackups\', @backupType='F'"

Пример для разностного бэкапа:

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @databaseName='YourDB', @backupLocation='D:\SQLBackups\', @backupType='D'"

Если нужно бэкапить все базы (в пределах процедуры), можно вызвать sp_BackupDatabases без @databaseName - смотрите параметры в созданной процедуре.

Примечание: если используете SQL-аутентификацию и пароль в командной строке, это небезопасно. Постарайтесь по возможности использовать Windows-аутентификацию или храните секреты аккуратно.

Шаг 4. Запланируйте запуск в Планировщике заданий

1. Откройте taskschd.msc (Планировщик заданий).
2. Создать задачу (не “базовую” - так удобнее управлять правами).
3. На вкладке Общие:
4. Назовите задачу, например SQLBackup
5. Укажите учетную запись, от имени которой будет выполняться задача
6. На вкладке Триггеры:
7. Создать
8. Тип: Ежедневно (или Еженедельно)
9. Укажите время (обычно ночью)
10. На вкладке Действия:
11. Создать → Запуск программы
12. Программа: путь к Sqlbackup.bat (файл .bat)

Запустите тестом: ПКМ по задаче → Выполнить. Проверьте, что файл .bak реально появился в папке и что бэкап не падает по правам.

Как организовать расписание Full/Diff/Log (пример)

Если вы делаете Full и разностные копии, логика обычно такая: сначала Full, потом Diff несколько раз, а лог - если вы ведёте модель восстановления с журналированием.

Ниже - типовой вариант “чтобы работало и было понятно”:

Пример расписания (часто встречающийся подход):

Если у вас SIMPLE модель восстановления (частый вариант для прикладных сценариев), то лог-цепочки обычно не нужны так, как в модели с point-in-time. Тогда чаще достаточно Full/Diff.

Проверка бэкапа и “живость” восстановления

Самое частое “попадание на мину” - бэкап создаётся, но потом не восстанавливается.

Минимальные меры:

• В сценариях резервного копирования полезно делать проверку средствами SQL:
• RESTORE VERIFYONLY
• Если бэкапы уходят на server по сети, проверьте права записи в сетевую папку и доступность пути.

Пример проверки (используется в самописных скриптах):

RESTORE VERIFYONLY FROM DISK = @path WITH FILE = @backupSetId;

Хранение: папка, ротация и удаление старых файлов

Автоматический запуск бесполезен, если диск забьётся.

Чаще всего делают так:
- Бэкап сохраняется в папку на диске, например D:\SQLBackups
- Отдельной задачей (или вторым шагом) удаляются файлы старше N дней

Если вы делаете Full/Diff разными заданиями, используйте:
- одинаковую структуру папок (например, Full/, Diff/)
- очистку отдельно по каждому типу

Что важно учесть заранее

1. Права учетной записи (для Windows-планировщика или SQL Agent):
2. доступ к sqlcmd
3. доступ к файлам (чтение/запись)
4. доступ к server и database для операции резервного копирования
5. Место на диске: заранее оцените размер и скорость роста.
6. Путь и доступность: сетевые пути должны быть доступны всегда, а не “только когда работает сервер”.
7. Версии SQL Server: бэкап созданный на более новой версии может не восстановиться на старой.

Быстрая шпаргалка: когда какой способ

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