Рассмотрим, как организовать две наиболее часто встречающиеся задачи администрирования SQL Server’а:

• Автоматическое резервное копирование баз данных;
• Удаление старых резервные копии.

Планирование резервных копий базы данных

• Откройте SQL Management Studio и подключитесь к требуемой базе данных. Убедитесь, что SQL Server Agent работает;
• Разверните узел Management – Maintenance (для этого у Вас должна быть роль «SYSADMIN») – щелкните правой кнопкой и выберите «New Maintenance Plan»;
• Введите имя нового плана обслуживания;
• Щелкните по иконке календаря справа в единственной строке. В открывшемся окне сконфигурируйте время выполнения задания. Выберите такое время, когда база данных меньше загружена;
• Из раздела Toolbox перетащите задачу Backup Database Task в основную область;
• Дважды щелкните по Backup Database Task – откроется окно настроек задачи резервного копирования – задайте нужные настройки;
• Щелкните ОК – теперь резервные копии будут создаваться в соответствии с запланированным временем;

Удаление старых резервных копий

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

• Из панели Toolbox перетащите в основную область задачу Maintenance Cleanup Task;
• Дважды щелкните по Maintenance Cleanup Task, чтобы открыть окно свойств. В нем Вы должны определить расположение резервных копий, их расширение и определить возраст файлов подлежащих удалению. Хорошей практикой является хранение бэкапов до одного месяца;
• Жмите ОК и сохраняете план обслуживания;
• Дальше можете либо дождаться следующего времени выполнения плана обслуживания, либо выполнить его вручную (правой кнопкой мыши по плану обслуживания в Object Explorer).

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

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

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

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

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

1. Операционная система семейства Windows (в моем примере используется )
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 ).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 поддерживает данную функцию.

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

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

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

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

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

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

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

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

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

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

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

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

Помогла ли Вам данная статья?

Буквально вчера мне была поставлена задача настроить резерное копирование базы данных SQL Server 2008 Express. Как известно, эта редакция SQL Server не поддерживает Агент SQL Server, поэтому пришлось немного погуглить. После недолгих поисков, наткнулся на программу SQLBackupAndFTP (sqlbackupandftp.com). Бесплатная редакция программы поддерживает резервное копирование баз данных (при использования расписания есть возможность бэкапить только две базы данных), ZIP-сжатие резервной копии, сохрание резервной копии на локальном или сетевом диске и даже отправка резервной копии на FTP-сервер. Но обо всем по порядку…
После скачивания и установки программы открывается следующее окно:

Для изменения параметров подлючения к SQL Server и способа аутентификации, нажимаем на кнопку Connect to SQL Server :

Если подключение прошло успешно, то Вы сможете увидеть список всех баз данных, подключённых к серверу. Выбрав необходимую базу данных, в разделе Store backups in a Local/network folder указываем локальную или сетевую папку для хранения резервных копий. При необходимости можно настроить копирование бэкапов на FTP-сервер. Для этого включаем функцию Send Backups to FTP Serve r, указываем в поле Host address адрес Вашего сервера, а также вводим логин и пароль для доступа к FTP-серверу.

Нажав кнопку Run now можно проверить, как всё работает…
Единственная проблема, с которой я столкнулся – не получается настроить отправку уведомлений на почту о работе задания.
Дальше необходимо указать время проведения резервного копирования и сохранить задание. Очень приятно, что для создания резервных копий по расписанию SQLBackupAndFTP использует стандартный планировщик заданий Windows:

И последнее, в разделе Advanced можно указать учетную запись для запуска задания на резервное копирование, временную папку программы, папку для сохранения log-файлов, а также уровень сжатия файла резервной копии.

Восстановление базы данных из резервной копии

Для восстановления базы данных из резервной копии предусмотрено приложение One-Click SQL Restore :

Как видно из рисунка для восстановления базы данных Вам необходимо указать файл бэкапа в формате bak или zip , выбрать базу данных и нажать Restore. И всё!

Программа SQLBackupAndFTP поддерживает работу с следующими вермиями и редакциями СУБД: SQL Server 2005/2008 Express, SQL Server 2008 Enterprise/Workgroup/Standard/Web, SQL Server 2005 Enterprise/Workgroup/Standard, SQL Server 2000 и со следующими операционными системами: Windows 2000/XP/Vista/7, Windows Server 2003/2008.

Теги: SQL Server, SQL Server 2008 Express, SQLBackupAndFTP, backup

Данная статья не подлежит комментированию, поскольку её автор ещё не является

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

Для этого можно использовать либо встроенный в SQL Server планировщик заданий - «SQL Server Agent» (в бесплатную версию не входит), либо стандартный «Планировщик Windows» в сочетании с утилитой SQLCMD.EXE, которая позволяет выполнять запросы к SQL Server из командной строки. В планировщике необходимо создать как минимум семь заданий (по одному на каждый день недели), каждое из которых будет (раз в неделю) заменять один из семи файлов, содержащих соответствующую резервную копию базы данных.

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

С помощью «Планировщика Windows» (для бесплатной версии)

Чтобы создать задание в «Планировщике Windows» надо:

Запустить программу «Блокнот» (Пуск->Все программы->Стандартные->Блокнот) и ввести следующие две строки, после чего сохранить их в виде командного файла (*.BAT):

SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "D:\BACKUP\ AltaSVHDb_monday.bak" WITH INIT, NOFORMAT, SKIP, NOUNLOAD"
XCOPY D:\BACKUP\ AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

где «(local)» - имя сервера (в случае установки именованного экземпляра SQL Server надо указать имя полностью: «ИМЯ_КОМПА\SQLEXPRESS»), «AltaSVHDb» - имя базы данных, «D:\BACKUP\ AltaSVHDb_monday.bak» - имя файла для создания в нем резервной копии (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться дополнительное копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ).

Запустить мастер планирования заданий (Панель управления->Назначенные задания->Добавить задание) и нажать кнопку «Далее»:

Нажать кнопку «Обзор» и указать путь к командному файлу (*.BAT), созданному на шаге a):

Указать имя для задания, выбрать вариант запуска «еженедельно» и нажать кнопку «Далее»:

Поставить галочку возле нужного дня недели, а в поле «Время начала» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью), затем нажать кнопку «Далее»:

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

Внимание! Чтобы задание успешно выполнялось необходимо предоставить указанной здесь учетной записи (домена или локального компьютера) права записи в вышеупомянутую папку «\\BACKUP_SERVER\Folder» , а также настроить доступ к самому SQL Server.

Нажать кнопку «Готово»

Примечание . Чтобы проверить работоспособность созданного задания, необходимо в списке заданий (Панель управления->Назначенные задания) нажать правой кнопкой мыши на интересующем задании и в контекстном меню выбрать пункт «Выполнить», затем убедиться, что файл резервной копии БД успешно создался по тем путям, которые были указаны на шаге a).

С помощью «SQL Server Agent» (в бесплатную версию не входит)

Чтобы создать задание в «SQL Server Agent» надо:

Запустить утилиту SQL Server Management Studio и подключиться к серверу под учетной записью администратора.

В левой части окна нажать правой кнопкой мыши на разделе «Объекты сервера/Устройства резервного копирования» и в контекстном меню выбрать пункт «Создать устройство резервного копирования»:

В поле «Имя устройства» ввести имя, которое будет ассоциироваться с файлом резервной копии БД, при необходимости изменить путь в поле «Файл» и нажать «ОК»:

В левой части окна нажать правой кнопкой мыши на разделе «Агент SQL Server/Задания» и в контекстном меню выбрать пункт «Создать задание»:

В поле «Имя» ввести имя задания:

На странице «Шаги» нажать кнопку «Создать»:

В появившемся окне ввести имя в поле «Имя шага», проверить, что в поле «Тип» выбрано «Сценарий Transact-SQL (T-SQL)», а в поле «Команда» ввести строку:

BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD

где «AltaSVHDb» - имя базы данных, «AltaSVHDb_monday» - имя устройства резервного копирования, созданного на шаге c) (будет различаться по дням недели):

В предыдущем окне нажать кнопку «ОК», в результате на странице «Шаги» должна появиться строка:

Чтобы файл резервной копии БД сразу копировался на другой компьютер в сети необходимо повторить пункты f) - h), в окне «Создание шага задания» выбрав в поле «Тип» значение «Операционная система (CmdExec)», а в поле «Команда» указав строку:

XCOPY D:\MSSQL\BACKUP\AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

где «D:\MSSQL\BACKUP\AltaSVHDb_monday.bak» - путь, указанный на шаге c) (будет различаться по дням недели), «BACKUP_SERVER» - имя компьютера, на который будет выполняться копирование, «Folder» - папка на этом компьютере (к ней должен быть предоставлен общий доступ):

Примечание . Чтобы копирование файла успешно выполнялось необходимо запускать «SQL Server Agent» под учетной записью домена Windows, для которой предоставлены права записи в вышеупомянутую папку (см. также «SQL2005_installation.doc» или «SQL2008_installation.doc»), а также настроен доступ к самому SQL Server (см. раздел «Настройка прав доступа к БД», включить эту учетную запись надо в роль «sysadmin» на странице «Серверные роли», а на страницах «Сопоставление пользователей» и «Защищаемые объекты» ничего не делать).

На странице «Расписания» нажать кнопку «Создать»:

Ввести имя в поле «Имя», проверить, что в поле «Тип расписания» выбрано значение «Повторяющееся задание», а в поле «Выполняется» - «Еженедельно». Поставить галочку возле нужного дня недели (остальные снять), а в поле «Однократное задание» указать время, когда должен запускаться процесс резервного копирования (обычно это делается ночью):

В предыдущем окне нажать кнопку «ОК», в результате на странице «Расписания» должна появиться строка:

Нажать кнопку «ОК».

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

После изучения множества информации с разных источников, решил описать процесс настройки резервного копирования БД MS SQL Server для полной модели восстановления, какую модель использовать решать Вам, но от себя добавлю, что если в вашей БД большой поток информации (например создаются десятки, сотни или тысячи документов в 1 час), то потеря информации за день работы будет просто неприемлемой, в таком случае только полная модель обеспечит сохранность ваших данных. Данна статья предназначена для начинающих системных администраторов и содержит по моему мнению минимальный набор действий для резервного копирования БД 1С. Установка\Настройка самого SQL сервера и развертывание БД на нём в не рамках данной статьи.

Все настройки будем производить с помощью SQL Management Studio. Для начала нужно создать Устройство резервного копирования, можно и не создавать, но на мой взгляд это гораздо удобнее и правельнее. в оснастке SQL Management Studio -> Объекты сервера-> Устройства резервного копирования. Нужно указать имя устройства и файл в котором будут храниться резервные копии (лучше с расширением BAK), в дальнейшем можно посмотреть содержимое носителя, там будут перечислены все резервные копии.

Теперь можно приступать к настройки Плана обслуживания (Maintenance Plan). План Обслуживания можно создать сразу для всех БД, но удобнее для каждой БД создать свой план обслуживания.

В нашем Плане обслуживания будет три подплана: 1 - резервное копирование БД (Полное); 2 - резервное копирование БД (Разностное); 3 - Резервное копирование Журнала транзакций. У каждого подплана есть свое расписание выполнения. Раписание каждый настраивает по своим усмотрениям, в моём же случае полное копирование делается раз в неделю в воскресенье, Разностное копирование каждый день кроме воскресенья, ЖТ - журнал транзакций каждый час. При такой модели резервирования можно восстановить искомую БД на любую дату и час, причем экономим пространство на жёстком диске т.к. полное резервирование выполняется фактически раз в неделю, а в течении недели только изменения.

Настройка дневного расписания. Недельное отличается только установленной галочкой "Воскресенье" и снятыми с "понедельника" по "Субботу"

Расписание для ЖТ. Красным выделено время сохранения в течении дня, имеет смысл например, если пользователи работают с БД в определённый период, если режим работы 24х7, то оставляем по уполчанию.

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

Задачи. В каждую задачу нужно зайти и выбрать БД, для которой она будет выполняться и ряд других настроеек (если есть). Рассмотрим, какие задачи содержит недельный подплан нашего плана обслуживания.

1. "Проверка целостности БД" (Check Database Integrity Task). Следующая задача будет выполняться, только если БД не содержит ошибок. (Замем резервировать БД с ошибками?)

2. "Восстановить индекс" (Rebuild Index Task). Восстановить (Перестроить) индекс необходимо каждый день, т.к. при работе с индексами они сильно фрагментируются и при фрагментации более 25% SQL начинает заметно "тормозить". Эта операция довольно ресурсоёмка, поэтому её можно делать хотябы раз в неделю, а в дневном подплане заменить её менее ресурсоёмкую задачу "Реорганизация индекса".

3. "Обновить статистику" (Update Statistics Task). Для оптимизации... Кстати эту задачу можно выполнять несколько раз в течении дня, если ваша БД сильно нагружена.

4. После обновления статистики ОБЯЗАТЕЛЬНО нужно очистить процедурный кэш. Для этого перетаскиваем в редактор задачу "Выполнение инструкции T-SQL" и в поле "инструкция T-SQL:" написать процедуру DBCC FREEPROCCACHE . Но нужно учесть, что эта процедура очищает кэш у ВСЕХ БД, а мы обновили статистику по одной! Как очистить процедурный кэш для определённой БД, читаем . Вкратце: DBCC FLUSHPROCINDB(ID_БД)

5. "Резервное копирование БД" (Back Up Database Task). В этой задаче указываем какую БД мы резервируем, тип резервной копии (Для недельного подплана - Полное, для дневного - Разностное, для часового - Журнал транзакций.) Ставим переключатель в положение "Создать резервную копию баз данных в одном или нескольких файлах" и добавляем ранее созданное устройство резервного копирования. В таком случае ВСЕ копии сохраняются в один файл, который указали при создании устройства резервного копирования, если переключатель оставить в "Создать файл резервной копии для каждой базы данных", то на каждое резервное копирование будет создаваться отдельный файл и на Полное и на Разностное и на ЖТ, что очень неудобно при восстановлении, зато удобно при хранении. Не забываем укзать что нужно сжимать резервные копии!

6. "Очистка Журнала" Очищает записи, создаваемые при выполнении задач. Также можно включить задачу "Очистка после обслуживания" и настроить её для удаления текстовых логов или устаревших резервных копий.

Подплан для резервирования ЖТ, состоит из одной задачи "Резервное копирование базы данных".ЖТ для меня удобнее сохранять не в Устройство резервного копирования, а в отдельный файл, что необходимо указать в настройке задачи.