Сохранение резервных копий баз данных в MS SQL Server 2008 R2 на FTP-сервер

sql_serverВ данной статье речь пойдет о сохранении файлов резервных копий, полученных в результате работы планов обслуживания Microsoft SQL Server 2008 R2, на отдельный FTP-сервер. Эта операция позволит защитить данные от таких неприятных происшествий как выход из строя дискового накопителя, катастроф, кражи оборудования и прочего. К сожалению в программе «Среда Microsoft SQL Server Management Studio» штатными средствами не предусмотрено копирования файлов по FTP. Поэтому алгоритм автоматизации этого процесса будет следующий:

0. Оглавление

  1. Создание bat-файла для копирования на FTP-сервер (скачать все файлы одним архивом).
  2. Создание задания Агента SQL Server для запуска bat-файлов
  3. Добавление заданий в существующий план обслуживания SQL Server

1. Создание bat-файла для копирования на FTP-сервер

Согласно стратегии резервного копирования, приведенной в статье «Копирование и восстановление баз данных в Microsoft SQL Server 2008 R2», нам необходимо сохранять ежедневные полные бэкапы производственных баз данных на FTP-сервере и хранить их там 1 месяц с момента создания.

О создании «батника» для копирования произвольного файла на FTP-сервер, я уже писал в статье «Скачивание, загрузка и удаление файлов с FTP-сервера из командной строки».

Но дело в том, что имя файла, полученного в результате резервного копирования в SQL Server Management Studio, имеет примерно следующий вид:

BASE1_backup_2013_06_25_030007_5153516.bak

состоящий из имени базы, слова backup, даты и времени создания копии. Поэтому имя файла необходимо «вычислять» каждый раз от даты копирования. В данном случае — от текущей даты. Для построении имени файла резервной копии будем использовать следующий код bat-файла:

rem ***** Вычислим параметры текущей даты и имена файлов *****
set year=%date:~6%
set month=%date:~3,-5%
set day=%date:~0,-8%
set file_name="%base_name%_backup_%year%_%month%_%day%_*.bak"
set file_name_on_ftp="%base_name%_backup_%year%_%month%_%day%.bak"

Здесь переменная %date% возвращает строковое представление даты, из которого мы извлекаем все необходимые параметры и собираем из них имя файла. Следует помнить, что формат возвращаемой строки зависит от региональных настроек системы. Поэтому чтобы вышеприведенный код работал, необходимо убедиться, что переменная %date% возвращает строку вида: «09.07.2013» . Проверить это можно выполнив:

echo %date%

backup_ftp_copy_001

Таким образом, полный текст файла copy_on_ftp.bat, для копирования резервной копии базы SQL-сервера на FTP, будет следующим:

rem ***** Отключаем вывод на экран *****
echo off

rem ***** Запишем все значения переменных *****
set ftp_host=
set ftp_username=
set ftp_pass=
set file_transport=transport.txt
set base_name=%1
set dir_from=%2
set dir_to=%3

rem ***** Вычислим параметры текущей даты и имена файлов *****
set year=%date:~6%
set month=%date:~3,-5%
set day=%date:~0,-8%
set file_name="%base_name%_backup_%year%_%month%_%day%_*.bak"
set file_name_on_ftp="%base_name%_backup_%year%_%month%_%day%.bak"

rem ***** Создаем файл с командами ftp ***** 
echo open %ftp_host%>%file_transport%
echo user %ftp_username% %ftp_pass%>>%file_transport%
echo cd %dir_to%>>%file_transport%
echo lcd %dir_from%>>%file_transport%
echo put %file_name% %file_name_on_ftp%>>%file_transport%
echo bye>>%file_transport%

rem ***** Запускаем на исполнение *****
ftp -v -n -s:%file_transport%

rem ***** Удаляем файл с командами ftp *****
del %file_transport%

Необходимо только указать параметры подключения к FTP-серверу. Имя базы данных, каталог с копией на локальном компьютере и каталог на сервере здесь задаются в качестве параметров. Соответственно, чтобы скопировать копию базы BASE1 из «C:\Backup\BASE1» на FTP-сервер в «Backup/SQL/BASE1» за сегодняшнее число, необходимо выполнить:

backup_ftp_copy_002

Аналогичным образом строятся файлы для удаления старых копий с FTP-сервера. Согласно выбранной стратегии резервного копирования, нам необходимы «батники» для удаления копий месячной del_on_ftp_last_month.bat и годичной del_on_ftp_last_year.bat давности.

2. Создание задания Агента SQL Server для запуска bat-файлов

Теперь перейдем к SQL-серверу. В моем примере это Microsoft SQL Server 2008 R2. Но для других версий SQL Server, действия будут аналогичными.

Создадим задание для запуска bat-файлов, которые будут копировать базу данных master и удалять соответствующую копию прошлого года. Для этого запустим SQL Sever Management Studio ( «Пуск» — «Все программы» — «Microsoft SQL Server 2008 R2» — «Средства SQL Server 2008 R2» ) и введем данные для авторизации.

Затем в обозревателе объектов раскроем вкладку «Агент SQL Server», кликнем правой кнопкой мыши по вкладке «Задания» и в контекстном меню выберем «Создать задание» .

backup_ftp_copy_003

Откроется окно нового задания, введем Имя и Описание и перейдем на вкладку «Шаги» .

backup_ftp_copy_004

Здесь нажмем кнопку «Создать» , чтобы добавить шаг в задание.

backup_ftp_copy_005

Напишем Имя шага, тип выберем «Операционная система (CmdExec)» и пропишем команду для копирования базы на FTP-сервер.

backup_ftp_copy_012

Затем перейдем на вкладку «Дополнительно» и укажем:

  • Действие при успехе: «Перейти к следующему шагу» ;
  • Действие при ошибке: «Завершить задание с ошибкой» ;

После чего жмем «ОК» .

backup_ftp_copy_006

Аналогичным образом создадим еще один шаг, с командой удаления прошлогодней копии базы данных.

backup_ftp_copy_007

С той лишь разницей, что при успехе необходимо «Завершить задание с успехом» .

backup_ftp_copy_008

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

backup_ftp_copy_009

Сохраним задание нажав «ОК» .

3. Добавление заданий в существующий план обслуживания SQL Server

Ну и наконец осталось добавить созданное задание в план обслуживания, который делает резервную копию базы данных master.

Для этого на вкладке «Планы обслуживания» выделим необходимый план обслуживания, в «Панели элементов» найдем пункт «Задача «Выполнение задания агента SQL Server»» и перетащим его в окно графического представления плана. В появившемся списке всех существующих задач, выберем необходимую нам задачу и нажмем «ОК» .

backup_ftp_copy_010

После чего останется только выставить последовательность действий таким образом, чтобы задание выполнялось сразу после успешного завершения задачи «Резервное копирование базы данных» .

backup_ftp_copy_011

Помогла ли Вам данная статья?
Да, спасибо, все получилось.
Немного помогла.
Совсем не помогла.
Не то, что я искал(а).
Смотреть результаты
Запись опубликована в рубрике cmd, Microsoft SQL Server 2008 с метками , , , , . Добавьте в закладки постоянную ссылку.


16 Responses to Сохранение резервных копий баз данных в MS SQL Server 2008 R2 на FTP-сервер

  1. Flame говорит:

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

  2. Дмитрий говорит:

    Приветствую! Очень нужна Ваша помощь!
    По статье все работает, НО заметил такую неприятную вещь, в агенте создается задание на запуск батника (в котором собственно все команды для ftp), и даже если ftp-порты закрыты на сервер куда надо скопировать бэкап — то агент все равно завершает все шаги с успехом, просто пишет что «Нет связи», не удалось подключиться и в конце — «код завершения задания 0» задание завершено с успехом! вот такие дела, заметели спустя неделю что на ftp нет бэкапов, и что ОЧЕНЬ важно, в плане обслуживания стоит уведомление как в случае успеха, так и в случае ошибки (которой как раз таки и нет). Помогите пожалуйста разобраться!

    • Виталий Онянов говорит:

      Доброго времени суток.

      Да, есть такой косяк. Дело в том, что в задании агента значится запуск батника для копирования файла. И с этим заданием агент справляется на успешно. Батник то он запустил.
      А уж скопировалось или нет, уже не задача агента.

      Для обхода такой ситуации я использую скрипты, где отправкой сообщений об успехе / неудаче занимается сам скрипт.
      То есть в коде скрипта прописано, что если файл скопировался, то отправляется сообщение об успехе, в противном случае о неудаче.
      План обслуживания таким образом заканчивается таким вот заданием.

      Такие скрипты очень удобно писать с помощью бесплатной русской программы xStarter.

      Не думаю, что в ближайшее время я напишу статью по этому вопросу, но думаю с xStarter вы самостоятельно справитесь. Там очень простой скриптовый язык, задания «накидываются мышкой». Такой скрипт можно скомпилировать в .exe файл и уже его вызывать в задании агента SQL Server.

      Ссылка на программу: http://www.xstarter.com/rus/

  3. Анатолий говорит:

    Возникла такая проблема.
    Файл transport.txt формируется в кодировке ASCII, и он не отрабатывает. Если кодировку поменять на ANSI, то бэкап капируется на ftp.
    Можно ли как-то принудительно указать кодировку в copy_on_ftp.bat ?

    Спасибо.

  4. Сергей говорит:

    Виталий спасибо за статью, не раз обращался к ней.
    Но вот не задача, на одном из серверов база не копируеться на FTP сервер
    Перепробовал кучу вариантов по доступам но ничего не помогает
    В ручном режиме запуска батника пишет
    «Команда не выполнена для этого параметра»
    Хотя на другом сервере все без проблем работает! На FTP через браузер заходит проблем нет.
    Может проблема в брандмауэре ?
    Можете подсказать?

  5. Евгений говорит:

    Здравствуйте.
    Все копируется на ftp-сервер успешно. Но вот при желании восстановить базу с этого ftp, появляется ошибка.

    Копирую файл bak в корень диска С. После этого выбираю его как носитель, При попытке просмотреть Содержимое появляется ошибка: System.Data.SqlClient.SqlError: RESTORE HEADERONLY прервано с ошибкой.

    Нужно менять Владельца файла?
    В обычных резервных копиях, которые не копируются на ftp, владелец MSSQL$SQL

    Или в чем может быть проблема?

    • Виталий Онянов говорит:

      Хм…
      Для проверки ошибки с безопасностью, создайте на диске С новую папку, в него скопируйте ваш файл .bak и дайте на эту папку полные права всем пользователям («Все» или «Everyone»). Будет ли ошибка при загрузке в этом случае?

      • Евгений говорит:

        Да, ошибки не стало..
        Но, если копируется файл с помощью скрипта, как пишите в статье (запускаю его планировщиком, после создания резервного набора), то появляется эта же ошибка.
        Может еще каким-нибудь советом поможете?
        Спасибо.

  6. Сергей говорит:

    Подскажите, как можно настроить отправку бекапов в облако?

  7. Денис говорит:

    Здравствуйте Виталий!
    Столкнулся с такой проблемой: bak-файл весит более 3 Гб, время отправки на сервер около 3,5 часов. Но загрузить удается только около 50 %, после этого связь обрывается и недокачанный файл с сервера исчезает. Можете что-нибудь посоветовать? Спасибо!

    • Виталий Онянов говорит:

      Здравствуйте.
      Видно канал у вас не стабильный. Попробуйте какие-нибудь внешние ftp-клиенты с функцией докачки (вместо стандартного виндового ftp.exe).

      • Денис говорит:

        Через FileZilla удается закачать один бэкап (на втором уже связь обрывается с теми же последствиями — файл исчезает). В принципе, это бы и устроило, но как автоматизировать процесс? Может быть порекомендуете какое-нибудь стороннее ПО для бэкапа MS SQL на FTP?

  8. Олег говорит:

    Добрый день!
    А подскажите пожалуйста, как изменить скрипт, если у меня одним заданием делается бекап сразу нескольких баз?

  9. Сергей говорит:

    Спасибо, полезная в администрировании статья, бэкапы наше все!)

  10. Александр говорит:

    Приветствую.
    Перешёл на эту статью через комментарий автора
    https://infostart.ru/public/73188/
    Виталий, может подскажите по скрипту 73188. Работает. Создаются бекапы во временной папке, но на ФТП ничего. Даже не было подключения. СКУЛЬ пишет что задание выполнено. Ошибок и предупреждений ноль.

    • Виталий Онянов говорит:

      Ну, Агент SQL-сервера выполнил команду ftp, а дальше за успешностью загрузки он не следит. Вам необходимо отладить команду (bat-файл) отдельно, убедиться что он работает, а потом уже включать его в задание.

Добавить комментарий для Евгений Отменить ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *