Как организовать ежедневный бекап баз данных MS SQL Server на SFTP-сервер?

← На главную

Резервное копирование баз данных — это критически важная задача для любого системного администратора. Ситуация осложняется, когда хранилище бекапов находится на удаленном сервере и доступно только по протоколу SFTP. Рассмотрим, как решить эту задачу, учитывая, что MS SQL Server на уровне ядра T-SQL не поддерживает прямую выгрузку по SFTP.

В этой статье мы разберем несколько эффективных способов: от использования сторонних утилит до написания собственных скриптов на PowerShell и применения профессиональных решений для обслуживания баз данных.

Ограничения протоколов MS SQL Server

Прежде всего, проанализируем техническую сторону вопроса. Стандартная команда BACKUP DATABASE умеет работать только с ограниченным набором путей: локальные диски сервера, сетевые папки (UNC-пути) и облачные хранилища (например, Azure Blob Storage). Прямого способа отправить файл на SFTP в синтаксисе SQL не существует. Следовательно, нам всегда понадобится «посредник», который заберет локальный файл и передаст его на удаленный сервер.

Решение 1: Использование скриптов Олы Халленгрена (Ola Hallengren)

Это решение считается «золотым стандартом» в мире администраторов баз данных. Скрипты Ola Hallengren позволяют гибко настраивать бекапы, проверку целостности и оптимизацию индексов. Одной из ключевых особенностей является автоматическое создание структуры каталогов.

Рассмотрим, как это помогает решить задачу с именованием папок по датам:

Устанавливаем решение на сервер (создается набор хранимых процедур и задач в SQL Agent).
Запускаем задачу бекапа с параметрами, которые автоматически создадут структуру Имя_Сервера\Имя_Базы\Тип_Бекапа.
После выполнения локального бекапа мы можем использовать второй шаг задачи для передачи данных по SFTP.

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

Решение 2: Автоматизация через PowerShell и WinSCP

Если нам нужно полное управление процессом, разберем связку SQL Server Agent и консольной утилиты WinSCP. Проанализируем алгоритм работы такого решения:

Шаг 1: Создание бекапа во временную папку. Мы можем написать скрипт на T-SQL, который будет генерировать имя файла динамически. Посмотрим на пример кода для формирования пути с датой:


DECLARE @fileName VARCHAR(255);
DECLARE @date VARCHAR(20);
SELECT @date = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '_');
SET @fileName = 'C:\Backup\MyBase_' + @date + '.bak';
BACKUP DATABASE [MyDatabase] TO DISK = @fileName WITH COMPRESSION, CHECKSUM;

Шаг 2: Передача файла на SFTP. Для этого отлично подходит WinSCP. Мы создаем текстовый файл со сценарием для WinSCP и запускаем его через Operating System (CmdExec) в задаче SQL Agent:


winscp.com /command ^
    "open sftp://user:password@sftp.yourserver.com/ -hostkey=""ssh-rsa 2048 xxxxxxxxxxx...""" ^
    "put C:\Backup\* /RemotePath/Backups/" ^
    "exit"

Важно помнить, что использование xp_cmdshell внутри SQL для запуска внешних команд считается небезопасным. Рекомендуется настраивать эти шаги именно через SQL Server Agent, запуская их от имени сервисной учетной записи.

Решение 3: Монтирование SFTP как локального диска (Rclone)

Выясним, как сделать процесс максимально прозрачным для SQL сервера. Существует утилита Rclone, которая позволяет смонтировать удаленное SFTP-хранилище как виртуальный диск в операционной системе Windows.

Проанализируем преимущества этого метода:

SQL Server будет «видеть» диск (например, Z:\) как обычный локальный накопитель.
Вам не нужно писать сложные скрипты для передачи файлов после бекапа — файл сразу будет записываться на удаленный сервер (через кеш Rclone).
Легко настроить автоматическую очистку старых бекапов средствами самой системы или SQL Agent.

Решение 4: Создание сложной структуры папок через PowerShell

В сообщении №9 автор просил создавать папку с текущей датой и помещать туда бекапы всех баз. Разберем, как это реализовать современными средствами PowerShell. Этот скрипт можно запускать по расписанию через планировщик Windows.


$dt = Get-Date -Format "yyyy-MM-dd"
$backupDir = "C:\LocalBackups\$dt"
if (-Not (Test-Path $backupDir)) { New-Item -Path $backupDir -ItemType Directory }

# Получаем список всех баз данных, кроме системных
$databases = Invoke-Sqlcmd -Query "SELECT name FROM sys.databases WHERE database_id > 4"

foreach ($db in $databases) {
    $dbName = $db.name
    $filePath = "$backupDir\$($dbName)_$dt.bak"
    Invoke-Sqlcmd -Query "BACKUP DATABASE [$dbName] TO DISK = '$filePath' WITH COMPRESSION"
}

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

Важные рекомендации по безопасности и надежности

Выполняя бекап на удаленный SFTP-сервер, мы должны учитывать риски сетевых сбоев и безопасности данных:

Сжатие (Compression): Всегда используйте WITH COMPRESSION. Это не только экономит место на SFTP (где может быть лимит в 10ТБ), но и значительно сокращает время передачи данных по сети.
Проверка целостности (CHECKSUM): При передаче по протоколу SFTP файлы могут повредиться. Использование параметра WITH CHECKSUM при бекапе гарантирует, что сам файл был корректно записан SQL-сервером.
Шифрование (Encryption): Поскольку данные покидают ваш периметр и уходят на внешний сервер, настоятельно рекомендуем использовать встроенное шифрование бекапов в MS SQL Server. Это защитит информацию, если доступ к SFTP-серверу получат третьи лица.
Очистка (Cleanup): Не забывайте удалять старые бекапы. В скриптах Ola Hallengren за это отвечает параметр @CleanupTime, в самописных скриптах PowerShell это реализуется командой Remove-Item с фильтром по дате создания.

Таким образом, мы выяснили, что наиболее надежным и профессиональным способом является использование Ola Hallengren Maintenance Solution в сочетании с WinSCP или Rclone для транспорта файлов. Это обеспечивает автоматизацию, структурирование папок и безопасность данных.