Снова решал задачу удалённого резервного копирования SQL Server на коленке. На этот раз, по SFTP.

Потребуется WinSCP (в т.ч. PuTTYgen). Исходим из того, что локальный диск для резервных копий — R:.

Предварительные действия:

  1. Создать папку R:\ssh.keys, скрыть, дать туда доступ учетной записи SQL Agent на чтение, забрать доступ у всех прочих учетных записей (кроме своей).
  2. Сгенерировать пару ключей с помощью PuTTYgen.
    Положить приватный ключ (без парольной фразы) в формате SSH2-RSA в  R:\ssh.keys\backup.ppk
    Ассоциировать публичный ключ с нужной учетной записью на сервере SFTP.
  3. Создать папку для предварительного локального резервного копирования (файлы отсюда будут перемещаться на SFTP сервер). В примере R:\Hotcopy. Права на эту папку предоставить учетной записи  SQL Agent и учетной записи службы SQL Server.
  4. Выяснить fingerprint вашего SFTP сервера.

SQL Agent Job:

1) Локальное резервное копирование (Transact SQL script)

BACKUP DATABASE [DatabaseName] TO  DISK = N'R:\Hotcopy\DatabaseName.bak' WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N'DatabaseName -Full Database Backup', SKIP, NOREWIND, NOUNLOAD

BACKUP LOG [DatabaseName] TO  DISK = N'R:\Hotcopy\DatabaseName.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'DatabaseName -Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD

Опция COPY_ONLY используется для того, чтобы не нарушать нормальную последовательность логов, если вы ведете отдельное резервное копирование с возможностью восстановления в точку времени.

2) Копирование на удаленный SFTP сервер (CmdExec)

"C:\Program Files (x86)\WinSCP\winscp.exe" /console /command "open sftp://login@hostname -privatekey=R:\ssh.keys\backup.ppk -hostkey=""ssh-rsa 2048 87:a0:44:87:5f:cd:97:f9:37:6f:a9:db:ba:5b:a4:52""" "cd /remotebackupdir" "option confirm off" "put R:\Hotcopy\DatabaseName.bak -resume" "exit"

Опция confirm off нужна, что бы winscp не спрашивал перезаписать удаленный файл или нет. Fingerprint вашего SFTP сервера указывается в опции hostkey как показано в примере.