Backup SQL Server на SFTP
Снова решал задачу удалённого резервного копирования SQL Server на коленке. На этот раз, по SFTP.
Потребуется WinSCP (в т.ч. PuTTYgen). Исходим из того, что локальный диск для резервных копий — R:.
Предварительные действия:
- Создать папку R:\ssh.keys, скрыть, дать туда доступ учетной записи SQL Agent на чтение, забрать доступ у всех прочих учетных записей (кроме своей).
- Сгенерировать пару ключей с помощью PuTTYgen.
Положить приватный ключ (без парольной фразы) в формате SSH2-RSA в R:\ssh.keys\backup.ppk
Ассоциировать публичный ключ с нужной учетной записью на сервере SFTP. - Создать папку для предварительного локального резервного копирования (файлы отсюда будут перемещаться на SFTP сервер). В примере R:\Hotcopy. Права на эту папку предоставить учетной записи SQL Agent и учетной записи службы SQL Server.
- Выяснить 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 как показано в примере.