Создание плана обслуживания в Microsoft SQL Server 2008 R2 на примере автоматического резервирования баз данных

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

В описанный ниже план будут входить всего 2 задачи.

  • Резервное копирование базы данных.
  • Проверка целостности базы данных.

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

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

  1. Исходные данные
  2. Проверка работы Агента SQL Server
  3. Создание плана обслуживания
  4. Запуск выполнения плана обслуживания

1. Исходные данные

  1. Операционная система семейства Windows (в моем примере используется Microsoft Windows Server 2008 R2)
  2. Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать здесь)
  3. Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте здесь)
  4. Настроенная компонента Database Mail, в случае если требуется уведомлять по электронной почте операторов о результатах выполнения плана обслуживания (о том как настроить компоненту Database Mail и создать оператора системы я писал здесь).

2. Проверка работы Агента SQL Server

Первое что нам необходимо сделать, это убедиться что Агент SQL Server установлен и работает. Для этого запустим оснастку «Службы» («Пуск» (Start) — «Администрирование» (Administrative Tools) — «Службы» (Services) ) и в списке служб найдем службу «Агент SQL сервер» (SQL Server Agent).

sozdanie-plana-na-obslujivanie_001

Откроем свойства этой службы (кликнув по ней 2 раза) и убедимся что:

  • Тип запуска стоит «Автоматически» (Startup type: Automatic);
  • Состояние «Работает» (Service status: Started);

sozdanie-plana-na-obslujivanie_002

В противном случае, необходимо изменить параметры как на скриншоте выше и сохранить настройки нажав «Применить» (Apply) .

Теперь запустим программу «Среда SQL Sever Management Studio» ( «Пуск» (Start) — «Все программы» (All programs) — «Microsoft SQL Server 2008 R2» — «Средства SQL Server 2008 R2«) и введем данные для авторизации.

sql_full_backup_01

После чего, еще раз убедимся что Агент SQL Server работает (в обозревателе объектов должна быть вкладка «Агент SQL Server» (SQL Server Agent) с зеленой иконкой слева.

sozdanie-plana-na-obslujivanie_003

3. Создание плана обслуживания

Теперь перейдем непосредственно к созданию плана обслуживания. В обозревателе объектов (Object Explorer) раскроем вкладку «Управление» (Management), кликнем правой кнопкой мыши по вкладке «Планы обслуживания» (Maintenance Plans) и в контекстном меню выберем «Мастер планов обслуживания» (Maintenance Plan Wizard) .

sozdanie-plana-na-obslujivanie_004

В запустившемся мастере планов обслуживания на странице приветствия нажимаем «Далее» (Next) и в следующем окне вводим имя и описание нового плана.

Затем необходимо определиться с расписанием, по которому будет выполняться данный план обслуживания. Для этого установим переключатель на «Единое расписание для всего плана или без расписания» (Single schedule for the entire plan ore no schedule) и нажмем «Изменить…» (Change…) для назначения расписания.

sozdanie-plana-na-obslujivanie_005

Откроется окно «Свойства расписания задания» . Здесь зададим те параметры, согласно которым должен выполняться план обслуживания и нажмем «ОК» . В моем примере это:

  • Выполняется — «Еженедельно»  (Occurs — Weekly);
  • Повторяется каждые — «1 нед.» в «Воскресенье» (Recurs every: 1 week(s) on Sunday);
  • Выполняться один раз в день в: — «2:00:00» (Occurs onсe at: «2:00:00»);

sozdanie-plana-na-obslujivanie_006

Еще раз убедимся, что расписание задано верно, и нажмем «Далее» (Next) .

sozdanie-plana-na-obslujivanie_007

Здесь выберем те задачи, которые будет выполнять наш план обслуживания. В моем примере это:

  1. Проверка целостности базы данных (Check Database Integrity);
  2. Резервное копирование базы данных (полное) (The Back Up Database (Full));

Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее» (Next) .

sozdanie-plana-na-obslujivanie_008

Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх…» (Move Up)  и «Вниз…» (Move Down). Установив порядок, жмем «Далее» (Next) .

sozdanie-plana-na-obslujivanie_009

Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное)» (Back Up Database (Full)).

Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных» (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК» .

sozdanie-plana-na-obslujivanie_010

Ниже зададим размещение и срок хранения резервных копий, а также установим дополнительные параметры:

  1. Если установить переключатель «Создать файл резервной копии для каждой базы данных» (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных» (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
  2. Установка флага «Срок действия резервного набора данных истекает» (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
  3. Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии» (Verify backup integrity).
  4. Также рекомендую выбрать режим «Сжимать резервные копии» (Compress backup) для экономии дискового пространства, если используемая версия SQL Server поддерживает данную функцию.

sozdanie-plana-na-obslujivanie_021

Если дисковое пространство ограничено, можно также выбрать один файл для хранения резервной копии, который будет перезаписываться после каждого выполнения плана обслуживания. Для этого установим соответствующий переключатель на «Создать резервную копию баз данных в одном или нескольких файлах» (Back up databases across one ore more files) и указжем соответствующее имя файла (будьте внимательны, файл резервной копии следует задавать с расширением .bak), а также выберем режим «Перезаписать» в случае, если файлы резервной копии существуют (If backup files exist: Overwrite).

Определившись с настройками жмем «Далее» (Next).

sozdanie-plana-na-obslujivanie_011

Теперь очередь задачи «Проверка целостности базы данных» (Database Check Integrity). Для нее всего лишь необходимо выбрать базу данных. В моем примере это все та же база данных, что и на предыдущем шаге. Определившись с базами, жмем «Далее» (Next).

sozdanie-plana-na-obslujivanie_012

На следующей странице возможно выбрать директорию, куда будет сохраняться лог выполнения задания, а также указать оператора SQL Server для отправки отчета по электронной почте. Задав параметры, снова жмем «Далее» (Next).

sozdanie-plana-na-obslujivanie_013

Проверим еще раз все настройки плана обслуживания, и если все верно, нажимаем «Готово» (Finish).

sozdanie-plana-na-obslujivanie_014

Мастер начнет построение плана обслуживания. Если мастер не обнаружит ошибок, то  увидим сообщение об успешном построении плана. В противном случае необходимо устранить ошибки и повторить процедуру снова. Закроем окно, нажав «Закрыть» (Close).

4. Запуск выполнения плана обслуживания

Для запуска выполнения плана обслуживания перейдем в программу «Среда Microsoft SQL Server Management Studio». Здесь, раскрыв вкладку «Планы обслуживания» (Maintenance Plans) увидим наш только что созданный план. Чтобы проверить его работу, кликнем по нему правой кнопкой мыши, и в контекстном меню выберем пункт «Выполнить» (Execute) .

sozdanie-plana-na-obslujivanie_016

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

sozdanie-plana-na-obslujivanie_017

А в соответствующих директориях должны появиться файл резервной копии

sozdanie-plana-na-obslujivanie_018

и файл лога выполнения плана.

sozdanie-plana-na-obslujivanie_019

Открыв, этот файл, вы должны увидеть примерно следующее:

sozdanie-plana-na-obslujivanie_020

Если все так, поздравляю! План обслуживания SQL Server создан и работает.

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


20 комментариев на «Создание плана обслуживания в Microsoft SQL Server 2008 R2 на примере автоматического резервирования баз данных»

  1. bchen говорит:

    У вас ошибка в переводе:

    а также выберем режим «Перезаписать» в случае, если файлы резервной копии существуют (If backup files exist: Append).

    Append это «присоединить», не «перезаписать».

  2. Алексей говорит:

    А если у меня  служба SQL агент при запуске говорит что запущена и остановлена потому что ей нечего делать? что делать) ? 

    • Александр Трапезников говорит:

      От имени какого пользователя запускается служба? Попробуйте изменить пользователя.

  3. Алексей говорит:

    Здравствуйте, Виталий

    Получается SQL backup формируется, только на диске где был установлен SQL Server. Можно ли автоматически настроить формирование или копирование резервной копии на выделеный сетевой ресурс на сервере?

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

      Доброго времени суток.
      Почему же, можно указать путь хранения копий вручную, вида:

      \\< ИмяКомпьютера>\< ИмяРесурса>\

      Однако необходимо убедиться, что у пользователя из-под которого запускается Агент SQL Server, есть права на запись на данном сетевом ресурсе.
      Если в вашей сети поднят домен, разумно запускать Агент SQL Server из-под доменной учетной записи, и уже для нее настраивать права доступа к сетевым ресурсам.

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

    Доброго времени суток! Очень хорошая статья, спасибо Вам!

    Возникла такая проблема — при отработке планов обслуживания не создается лог выполнения плана. В журнале выдает ошибку — «Отказано в доступе по пути //полный путь до местоположения лога//». Лог создается на локальном диске.

  5. Степан говорит:

    Здравствуйте.
    Спасибо за полезную статью.

    По ней получилось создать план резервного копирования и он работает. Но не происходит перезапись более старых файлов (более 7 дней).
    В свойствах задания указана опция
    Срок действия резервного набора данных истекает:
    и значение — после 7 дней

    Но тем не менее, за месяц ни разу не перезаписались файлы 🙂
    Места конечно много выделено, но хотелось бы хранить только ограниченное количество файлов с копией БД.

    Снимок экрана с настройками тут — https://dl.dropboxusercontent.com/u/68023791/SQL_back_settings.png

  6. Rost говорит:

    Поддерживаю Степана, циклической перезаписи не происходит, вопрос почему? Продолжает плодить резервные копии!

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

    Аналогичная проблема, как у Степана и Rost’a. Что мы делаем не так?

    • Серёга говорит:

      Параметр «срок действия резервного набора» — означает через сколько дней файлики бекапов «можно» удалять, но это не означает, что они будут удаляться сами автоматически!!! Внутри задачи нужно настроить «Maintence cleanup task» который будет в указанной папке удалять «устаревшие» файлики.

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

        Не совсем так. Если вы делаете каждую резервную копию в отдельный файл, то да, сами файлы удаляться не будут. Однако если вы сохраняете резервные копии в один и тот же набор, то как раз при соответствующих настройках резервного копирования, «просроченные» копии (по свойству «срок действия резервного набора») будут автоматически удаляться из набора.

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

          Может быть имеет смысл дополнить статью пунктом об удалении устаревших файлов? Судя по всему вопрос распространенный…

  8. Кайзер говорит:

    Спасибо за статью. Очень полезное руководство. Побольше бы таких.

  9. Ержан говорит:

    я дико извиняюсь, но у меня во вкладках где у вас есть значок агент скуль сервер он отсутствует?и агент не запускается, просто никак не хочет. помогите пжста

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

      Может быть агент не установлен. Посмотрите в службах Windows. Есть такая служба?

  10. Юра говорит:

    Здравствуйте, спасибо за статью! У меня тоже возникла проблема с удалением старых копий бэкапа, сделал устройсво бэкапа, настроил бэкап в один файл со сроком в две недели и все равно файл растет и растет, не зависимо что стоит «Maintence cleanup task» со сроком две недели..
    Не подскажете может какой-нибудь ресурс где подробно описано как правильно удалять «старые» резервные копии?

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

    Добрый день.
    У меня настроен примерно аналогичный план обслуживания:
    1. Каждое воскресенье делается полный бэкап баз
    2. В остальные дни недели делается добавочный бэкап баз
    3. Каждые несколько часов делается бэкап журнала транзакций определенных баз
    4. Удаляются бэкапы, старше определенного возраста.
    Но вот на что обратил внимание.
    Если делать бэкап БД вручную — размер получившегося файла в разы больше, чем размер файла полного бэкапа, сделанного по регламентному заданию.
    К примеру.
    Есть БД размером 768,8 ГБ
    Размер ручного бэкапа — 41,6 ГБ.
    Размер регламентного бэкапа — 10,8 ГБ.
    Смущает меня это.
    Попробую восстановить из регламентного бэкапа базу. Но вообще — как такое может быть?

    • Сергей говорит:

      Все, я кажется понял.
      В регламентном задании стоит параметр «Сжимать резервную копию».
      Если при создании бэкапа вручную поставить этот параметр аналогично, размер совпадает.

Добавить комментарий

Ваш e-mail не будет опубликован.