Backup SQL Server на SFTP

Снова решал задачу удалённого резервного копирования 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 как показано в примере.