В данной статье речь пойдет о сохранении файлов резервных копий, полученных в результате работы планов обслуживания Microsoft SQL Server 2008 R2, на отдельный FTP-сервер. Эта операция позволит защитить данные от таких неприятных происшествий как выход из строя дискового накопителя, катастроф, кражи оборудования и прочего. К сожалению в программе «Среда Microsoft SQL Server Management Studio» штатными средствами не предусмотрено копирования файлов по FTP. Поэтому алгоритм автоматизации этого процесса будет следующий:
0. Оглавление
- Создание bat-файла для копирования на FTP-сервер (скачать все файлы одним архивом).
- Создание задания Агента SQL Server для запуска bat-файлов
- Добавление заданий в существующий план обслуживания 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%
Таким образом, полный текст файла 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» за сегодняшнее число, необходимо выполнить:
Аналогичным образом строятся файлы для удаления старых копий с 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», кликнем правой кнопкой мыши по вкладке «Задания» и в контекстном меню выберем «Создать задание» .
Откроется окно нового задания, введем Имя и Описание и перейдем на вкладку «Шаги» .
Здесь нажмем кнопку «Создать» , чтобы добавить шаг в задание.
Напишем Имя шага, тип выберем «Операционная система (CmdExec)» и пропишем команду для копирования базы на FTP-сервер.
Затем перейдем на вкладку «Дополнительно» и укажем:
- Действие при успехе: «Перейти к следующему шагу» ;
- Действие при ошибке: «Завершить задание с ошибкой» ;
После чего жмем «ОК» .
Аналогичным образом создадим еще один шаг, с командой удаления прошлогодней копии базы данных.
С той лишь разницей, что при успехе необходимо «Завершить задание с успехом» .
Таким образом получилось задание, состоящее из двух последовательных шагов.
Сохраним задание нажав «ОК» .
3. Добавление заданий в существующий план обслуживания SQL Server
Ну и наконец осталось добавить созданное задание в план обслуживания, который делает резервную копию базы данных master.
Для этого на вкладке «Планы обслуживания» выделим необходимый план обслуживания, в «Панели элементов» найдем пункт «Задача «Выполнение задания агента SQL Server»» и перетащим его в окно графического представления плана. В появившемся списке всех существующих задач, выберем необходимую нам задачу и нажмем «ОК» .
После чего останется только выставить последовательность действий таким образом, чтобы задание выполнялось сразу после успешного завершения задачи «Резервное копирование базы данных» .
Добрый день.
ваша статья очень помогла, но возникла необходимость делать тоже самое , но файл с датой вчерашней а не текущей, в связи с чем возник вопрос о вычислении вчерашней даты в таком же батнике.
надеюсь на вашу помощь
Приветствую! Очень нужна Ваша помощь!
По статье все работает, НО заметил такую неприятную вещь, в агенте создается задание на запуск батника (в котором собственно все команды для ftp), и даже если ftp-порты закрыты на сервер куда надо скопировать бэкап — то агент все равно завершает все шаги с успехом, просто пишет что «Нет связи», не удалось подключиться и в конце — «код завершения задания 0» задание завершено с успехом! вот такие дела, заметели спустя неделю что на ftp нет бэкапов, и что ОЧЕНЬ важно, в плане обслуживания стоит уведомление как в случае успеха, так и в случае ошибки (которой как раз таки и нет). Помогите пожалуйста разобраться!
Доброго времени суток.
Да, есть такой косяк. Дело в том, что в задании агента значится запуск батника для копирования файла. И с этим заданием агент справляется на успешно. Батник то он запустил.
А уж скопировалось или нет, уже не задача агента.
Для обхода такой ситуации я использую скрипты, где отправкой сообщений об успехе / неудаче занимается сам скрипт.
То есть в коде скрипта прописано, что если файл скопировался, то отправляется сообщение об успехе, в противном случае о неудаче.
План обслуживания таким образом заканчивается таким вот заданием.
Такие скрипты очень удобно писать с помощью бесплатной русской программы xStarter.
Не думаю, что в ближайшее время я напишу статью по этому вопросу, но думаю с xStarter вы самостоятельно справитесь. Там очень простой скриптовый язык, задания «накидываются мышкой». Такой скрипт можно скомпилировать в .exe файл и уже его вызывать в задании агента SQL Server.
Ссылка на программу: http://www.xstarter.com/rus/
Возникла такая проблема.
Файл transport.txt формируется в кодировке ASCII, и он не отрабатывает. Если кодировку поменять на ANSI, то бэкап капируется на ftp.
Можно ли как-то принудительно указать кодировку в copy_on_ftp.bat ?
Спасибо.
Виталий спасибо за статью, не раз обращался к ней.
Но вот не задача, на одном из серверов база не копируеться на FTP сервер
Перепробовал кучу вариантов по доступам но ничего не помогает
В ручном режиме запуска батника пишет
«Команда не выполнена для этого параметра»
Хотя на другом сервере все без проблем работает! На FTP через браузер заходит проблем нет.
Может проблема в брандмауэре ?
Можете подсказать?
Здравствуйте.
Все копируется на ftp-сервер успешно. Но вот при желании восстановить базу с этого ftp, появляется ошибка.
Копирую файл bak в корень диска С. После этого выбираю его как носитель, При попытке просмотреть Содержимое появляется ошибка: System.Data.SqlClient.SqlError: RESTORE HEADERONLY прервано с ошибкой.
Нужно менять Владельца файла?
В обычных резервных копиях, которые не копируются на ftp, владелец MSSQL$SQL
Или в чем может быть проблема?
Хм…
Для проверки ошибки с безопасностью, создайте на диске С новую папку, в него скопируйте ваш файл .bak и дайте на эту папку полные права всем пользователям («Все» или «Everyone»). Будет ли ошибка при загрузке в этом случае?
Да, ошибки не стало..
Но, если копируется файл с помощью скрипта, как пишите в статье (запускаю его планировщиком, после создания резервного набора), то появляется эта же ошибка.
Может еще каким-нибудь советом поможете?
Спасибо.
Подскажите, как можно настроить отправку бекапов в облако?
Здравствуйте Виталий!
Столкнулся с такой проблемой: bak-файл весит более 3 Гб, время отправки на сервер около 3,5 часов. Но загрузить удается только около 50 %, после этого связь обрывается и недокачанный файл с сервера исчезает. Можете что-нибудь посоветовать? Спасибо!
Здравствуйте.
Видно канал у вас не стабильный. Попробуйте какие-нибудь внешние ftp-клиенты с функцией докачки (вместо стандартного виндового
ftp.exe).Через FileZilla удается закачать один бэкап (на втором уже связь обрывается с теми же последствиями — файл исчезает). В принципе, это бы и устроило, но как автоматизировать процесс? Может быть порекомендуете какое-нибудь стороннее ПО для бэкапа MS SQL на FTP?
Добрый день!
А подскажите пожалуйста, как изменить скрипт, если у меня одним заданием делается бекап сразу нескольких баз?
Спасибо, полезная в администрировании статья, бэкапы наше все!)
Приветствую.
Перешёл на эту статью через комментарий автора
https://infostart.ru/public/73188/
Виталий, может подскажите по скрипту 73188. Работает. Создаются бекапы во временной папке, но на ФТП ничего. Даже не было подключения. СКУЛЬ пишет что задание выполнено. Ошибок и предупреждений ноль.
Ну, Агент SQL-сервера выполнил команду ftp, а дальше за успешностью загрузки он не следит. Вам необходимо отладить команду (bat-файл) отдельно, убедиться что он работает, а потом уже включать его в задание.