Как сохранить план обслуживания ms sql в файл

Обновлено: 05.07.2024

Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 1С Предприятия, размещенных в MS SQL.

Настройка плана обслуживания баз данных MS SQL Server выполняется через программу Microsoft SQL Management Studio. Рассмотрим задачи, которые мы будем выполнять в рамках регулярного обслуживания баз данных:

(раз в неделю, в воскресенье в 2:00); (раз в день, с понедельника по субботу в 2:00); (раз в день); (раз в день в 4:00); (раз в день).

В чем отличие полного бэкапа от разностного?

Полное резервное копирование сохраняет всю базу данных целиком.

Разностное резервное копирование сохраняет все изменения созданные в базе данных с момента последнего полного бэкапа.

Такой подход к резервному копированию позваляет экономить свободное пространство на носителях информации.

Создание полного бэкапа базы.

В обозревателе объектов переходим к пункту "Управление \ Планы обслуживания". В контекстном меню выбираем "Создать план обслуживания".

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

В созданном плане нажимаем кнопку "Добавление вложенного плана"

Вводим название "Полный бэкап" и описание. Задаем расписание для выполнения задания: Раз в неделю в воскресенье в 2:00.

Добавляем в созданный план задание. Для этого с панели элементов перетаскиваем в поле заданий вложенного плана элемент с названием Задача "Резервное копирование базы данных".

Открываем задание на редактирование: правой клавишей мыши по заданию, выбираем пункт "Изменить".

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

Создание разностного бэкапа.

Создание плана на выполнение разностного бэкапа выполняется аналогично полному бэкапу.

Отметим некоторые отличия в настройке:

Расписание выполнения заданий: с понедельника по субботу в 2:00;
Тип резервной копии выбираем "Разностное"

Очистка устаревших бэкапов.

Для очистки устаревших бэкапов баз 1С Предприятия в MS SQL выбираем на панели элементов плана обслуживания Задачу "Очистка после обслуживания".

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

Перетаскиваем задачу с Панели элементов в план и задаем такие настройки:

Удалить файлы следующего типа: Файлы резервных копий;
Удалить из папки файлы с определенным расширением: указываем папку хранения бэкапов баз 1С;
Включить вложенные папки первого уровня: отмечаем галочкой, потому-что у нас для бэкапов баз создаются отдельные папки
Удалить файлы на основе возраста во время выполнения задачи: здесь все ограничивается лишь вашими потребностями и объемом жесткого диска, а мне достаточно 4 недель.

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

Через стрелки можно задавать условие, при котором будет выполнять следующее задание: ошибка, успешное завершение, выполнение. Изменить условие можно щелкнув правой клавишей мыши по стрелке.

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

Переходим к очень важному и ответственному пункту: Перестроение индекса и обновление статистики.

Дефрагментация индекса (реорганизация или перестроение).

В процессе работы базы данных 1С Предприятия, в результате постоянной записи и удаления данных, образуются пустые (фрагментированные) области. По этой причине может увеличиваться бесполезный объем БД и замедляться скорость взаимодействия с ней.

Для устранения фрагментированных областей баз данных в MS SQL существует возможность проведения Реорганизации индекса и Перестроение индекса.

В чем разница между реорганизацией и перестроением?

Перестроение индекса означает, что фрагментация будет устранена путем удаления и пересоздания индексов.

При Реорганизации индекска происходит перестроение индексов в соответствии с логическим порядком. Этот способ наименее ресурсозатратный и является более предпочтительным для регулярного обслуживания баз данных.

В каких случаях требуется реорганизация индекса?

Уровень фрагментации от 5% до 30%, то проводим реорганизацию.
Фрагментация свыше 30% необходимо проводить перестроение индекса

Под выполнение этих задач очень подходит инструкция Transact-SQL со следующим содержимым:

Создаем вложенный план с названием "Дефрагментация индекса и обновление статистики" с расписанием раз в день в 4:00 и перетаскиваем в него из Панели элементов Задачу "Выполнение инструкции T-SQL".

Вставляем в задачу приведенную выше инструкцию T-SQL.

Обновление статистики.

Обновление статистики в базах данных MS SQL, как и дефрагментация индекса, имеет большое значение для повышения производительности работы SQL сервера. Благодаря обновлению статистики SQL Server способен более эффективно выполнять планы запроса.

Выбираем на панели элементов Задача "Обновление статистики" и добавляем ее во вложенный план "Дефрагментация индекса и обновление статистики".

Базы данных: все пользовательские базы данных;
Обновить: вся собранная статистика;
Тип просмотра: полный просмотр.

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

Я пытаюсь экспортировать простой план обслуживания из экземпляра SQL Server.

Я хочу проверить экспорт в систему контроля версий и применить экспортированный план обслуживания к функционально идентичным экземплярам.

StackOverflow и SQL Server Newbie рекомендуют использовать службы Integration Services для экспорта плана обслуживания.

Когда я пытаюсь подключиться к Integration Services на цели экспорта, я получаю следующую ошибку:

Соединение со службой Integration Services на компьютере «WEBSERVER» завершилось со следующей ошибкой: Указанная служба не существует в качестве установленной службы.

Мы решили отключить службы Integration Services на WEBSERVER, потому что мы используем это поле только для предоставления данных пользовательским приложениям. Все данные на WEBSERVER реплицируются из серверного экземпляра. Службы Integration Services активно используются для обработки данных на внутреннем экземпляре.

Есть ли документированный способ экспорта плана обслуживания без использования служб Integration Services? Microsoft поддерживает это?

Планы обслуживания хранятся в msdb.dbo.sysssispackages, как и любые другие пакеты служб SSIS, которые хранятся на SQL Server. У меня есть удобная статья о пакете SSIS Extract from MSDB, которая должна вылечить то, что вас беспокоит.

Это работает только в том случае, если у вас полностью установлен SSIS, потому что dtutil - который построен на нем - в основном отключен, даже если он присутствует. Некоторые версии SQL Server (например, Web Edition) не позволяют полностью установить SSIS, хотя в планах обслуживания по существу используются почти все функции SSIS. (Однако есть способ обойти это, если у вас есть две версии SQL Server, одна из которых не заблокирована - см. Мой ответ ниже.)

Есть способ сделать это.

Предположим, что, как и у OP, у вас есть два экземпляра SQL Server, на одном из которых установлен SSIS, а на другом - нет (вероятно, нет, например, если это SQL Server Web Edition).

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

Вам нужно было бы написать этот SP, чтобы он id сначала удалял все строки с соответствующими , а затем вставлял последние версии (или аналогичный подход, например, UPDATE соответствующие id s, а затем INSERT отсутствующие id s). И вам нужно будет настроить связанный сервер на одной или другой стороне, чтобы вы могли писать SQL, который адресован обоим серверам.

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

Конечно, это огромный взлом, но на самом деле это работает. (Я полагаю, что очень важно, чтобы номер версии SQL Server был одинаковым с обеих сторон, чтобы данные msdb.dbo.sysssispackages были настолько совместимы между различными экземплярами сервера, насколько это действительно кажется).

Конечно, вы всегда можете просто сделать резервную копию соответствующих строк из таблицы базы данных служб SSIS. Это сработало бы в любом случае - как полный ответ на оригинальный вопрос. Как уже говорилось, это не имеет ничего общего с предположением о SSIS - это просто предполагает планы обслуживания!

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

Я боролся с точно такой же проблемой. Вот основной вынос:

На вашем веб-сервере не требуется никаких услуг по интеграции. Одним из документированных способов является использование DTUTIL. Просто используйте ЛЮБОЙ SQL Server (даже бесплатную версию для разработчиков со всеми функциями Enterprise), на котором установлены службы Integration Services, чтобы скопировать пакеты обслуживания SQL Server из источника в цель, даже если это не источник или цель пакета, как показано в Пример А.

Пример A: Запустите DTUTIL на SQL Server MySSISServerA, чтобы скопировать пакет обслуживания SQL из MySourceServerB в MyDestServerC .

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

Для начала нужно в management studio нужно подключиться к SSIS, при этом используется доменная аутентификация:

Далее раскрыть папку базы MSDB, где и находятся наши планы обслуживания:

При подключении можно получить ошибку не существующего SSIS на данном instance:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 Jump
------------------------------
ADDITIONAL INFORMATION:

The SQL Server instance specified in SSIS service configuration is not present or is not available.
This might occur when there is no default instance of SQL Server on the computer.
For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2012 Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (MsDtsSrvr)
------------------------------
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (Microsoft SQL Server Native Client 11.0)

В интернете полно ссылок на одни и те же решения, в файле C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml не обходимо указать имя сервера и инстанса.
Но в моем случае это не помогло, может потому что используется кластер, при чем я указывал и имя сервера и имя кластера, а инстанс используется по умолчанию MSSQLSERVER.

Решение оказалось еще проще, в xml файле необходимо указать только имя кластера, без имени инстанса.

Далее на конкретном плане обслуживания нажимаем ПКМ и выбираем "Export package", в окне экспорта лучше выбрать "Package location - File System", почему? А потому что при выборе "SQL Server" не правильно переносится параметр "connection", и ваши планы просто не будут работать.

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

Следующий шаг это импорт в SSIS нового сервера - производится аналогично. Подключаемся к SSIS, на папке MSDB жмем ПКМ и жмем "Import package", и выбираем "Package location - File System" и наш измененный файл. Теперь наш план появится в списке Мaintenance plans.

Важный момент - это указание пароля sa или пользователя из-под которого выполняются планы в connection manager. Открываем на редактирование план и вводим учетные данные, выбрав "Local server connection" (это коннект по умолчанию).

Довольно часто бывает необходимо перенести задания Агента на другой экземпляр MS SQL Server. Восстановление базы данных msdb невсегда именно то решение, которое подойдет, т к нередки случаи, когда нужно перенести именно только задания Агента, а также при переходе на более новую версию MS SQL Server. Так как же можно перенести задания Агента без восстановления базы данных msdb?

В данной статье будет разобран пример реализации скрипта T-SQL, который копирует задания Агента с одного экземпляра MS SQL Server на другой. Данное решение было опробовано при переносе заданий Агента с MS SQL Server 2012-2016 на MS SQL Server 2017.

Решение

Опишем сначала саму последовательность действий:

1) создать список заданий, который переносить не нужно
2) перенести сами задания
3) перенести шаги перенесенных заданий
4) перенести расписания перенесенных заданий
5) перенести связку расписания-задания для перенесенных заданий
6) перенести целевые сервера для перенесенных заданий
7) регистрируем задания и активизируем их расписания, переведя в неактивный режим эти задания (выключением заданий)
8) назначаем владельца для всех перенесенных заданий (например, sa)

Теперь для каждого пункта приведем реализацию на T-SQL.

1) собираем те задания, которые переносить не нужно:

2) перенести сами задания:

3) перенести шаги перенесенных заданий:

4) перенести расписания перенесенных заданий:

5) перенести связку расписания-задания для перенесенных заданий:

6) перенести целевые сервера для перенесенных заданий:

Важно отметить, что если в заданиях присутствуют целевые сервера, отличные от локального (т е идентификатор не равен нулю), то необходимо сначала перенести сами определения этих целевых серверов, а потом уже производить п.6 алгоритма.

7) регистрируем задания и активизируем их расписания, переведя в неактивный режим эти задания (выключением заданий)

8) назначаем владельца для всех перенесенных заданий (например, sa)

Приведем код всего скрипта:

Результат

5 способов сделать резервные копии в SQL Server

В прошлый раз мы обсуждали 5 типов резервных копий. Сейчас я хочу представить вам пять способов сделать бэкап в SQL Server. Я не смогу продемонстрировать все доступные опции каждого из этих шести методов. Здесь много чего есть даже для такой простой темы как бэкапы.

Метод 1: Использование графического интерфейса в SSMS для создания бэкапа

Вы попадете на страницу General Backup Menu page в SSMS. Здесь вы можете получить доступ к множеству настроек, относящихся к создаваемому бэкапу.

В выпадающем списке “Backup type” вы можете выбрать тип создаваемого бэкапа - полный, дифференциальный или журнала.

В разделе “Backup component” можно уточнить, какой бэкап будет делаться - файлов и файловых групп или базы данных (по умолчанию).

В разделе Destination (назначение) вы выбираете, где будет создан бэкап - диск (по умолчанию) или, если выбрать из списка “URL”, то на Azure. При выборе Disk вам предлагается место и имя для бэкапа. Этим местом будет каталог по умолчанию для бэкапов, указанный при установке SQL Server. Если вас не устраивает это место, просто нажмите “Remove” (удалить), а потом “Add” (добавить) для выбора места, которое вы хотите использовать. В меню “Add” можно использовать общие пути.

Раздел Media Options на “Select a Page” позволяет выбрать такие варианты, как хотите ли вы добавить этот бэкап к имеющемуся набору или начать заново.

Мне кажется, что эти опции среды перешли из прошлого, когда бэкапы записывались на физические ленты. Эти ленты тогда должны были перематываться время от времени. Не лучший способ добавлять бэкапы в набор, поскольку все файлы бэкапов добавляются в единый набор. Если что-то случится с этим набором, и он станет непригодным для использования, то и все бэкапы станут недоступными.

В разделе Reliability (надежность) вы можете установить опции “Verify backup when finished” (проверить бэкап по завершению) и “Perform checksum before writing to media.” (посчитать контрольную сумму перед записью на носитель). Эти опции увеличат время создания бэкапа, но помогут с проверкой его целостности по время записи.

В Backup Options меню “Select a page” есть одна очень важная особенность, которую следует отметить.

Здесь имеется опция, связанная со сжатием бэкапа. В более старых версиях SQL Server, например, 2005 и 2008 эта опция была доступна только для Enterprise Edition. Начиная с SQL Server 2008R2, она доступна в Standard Edition. Чтобы сделать использование сжатия по умолчанию для всех ваших бэкапов, просто выполните нижеприведенный код на вашем SQL Server. Затем, когда вы перейдете к этой опции в графическом интерфейсе SSMS, просто оставьте её установленной в “Use the default server setting.” Вам захочется сэкономить пространство, которое предлагает сжатие. Зачем использовать больше пространства на вашем отдельном хранилище бэкапов, чем это необходимо? Я имею в виду, что вы храните свои резервные копии где-то еще, а не на SQL Server, верно?!

Установив необходимые опции, просто щелкните "ОК", и SQL Server сделает вам бэкап. Вы можете также щелкнуть опцию “Script” наверху окна мастера, чтобы SQL Server показал код T-SQL, который будет исполнен. Вы сможете сохранить его в качестве примера для дальнейшего использования.

Метод 2: Использование T-SQL для создания резервной копии на SQL Server

T-SQL - проверенный и надежный метод резервного копирования баз данных. При использовании T-SQL доступно больше опций для создания бэкапов, чем при использовании графического интерфейса. Большинство этих опций являются более продвинутыми. Очень базовый пример команды backup, которая создает полную резервную копию, представлен ниже. Затем следуют примеры дифференциального бэкапа и бэкапа журнала.

Стоит отметить два параметра Buffer Count и maxtransfersize. Вы можете поэкспериментировать с этими параметрами T-SQL, чтобы ускорить создание бэкапов. Значение Buffer Count управляет числом буферов ввода/вывода, которые используются для обработки бэкапа, а maxtransfersize отвечает за то, сколько данных перемещается за один раз.

Ниже я предоставил 3 примера моих тестов, выполненных на домашнем ПК. Исходные данные buffercount и maxtransfersize были получены с помощью установки флагов 3605 и 3213 с последующим обращением к журналу ошибок после выполнения первого бэкапа. После чего я просто экспериментировал со значениями. Имейте в виду, что слишком сильное увеличение числа буферов может вызвать ошибку нехватку памяти.

Как вы можете видеть начальная пропускная способность составляла 219,412Мб/с, а прошедшее время для этой части было 39 секунд. Это были настройки по умолчанию SQL Server.

Увеличение числа буферов до 8 увеличило пропускную способность до 258,653Мб/с, и время выполнения упало примерно на 6 секунд. Сочетание второго изменения с размером maxtransfersize 4Мб увеличило пропускную способность до 270,095 и еще сократило время на 1,4 секунды. Я скинул 8 секунд времени бэкапа. Это была небольшая база данных размером около 14Гб. Для бОльших баз данных увеличение пропускной способности может дать значительную экономию времени.

Метод 3: Использование Powershell для создания резервных копий

Если вы не используете Powershell с SQL Server, то это того стоит. Если вы не используете модуль DBATools с SQL Server, получите его сейчас. PowerShell может делать фантастические, чудесные вещи, а DBATools может сделать для вас мощные, удивительные вещи во всем, что связано с SQL Server. Ниже простой пример использования команды DBATools Backup-DbaDatabase для создания полного бэкапа. Эта команда имеет полный набор опций, включая резервирование всех баз данных на SQL Server, если не передавать параметр -Database. Проверьте это прямо сейчас.

Метод 4: Использование планов обслуживания для создания резервных копий

Тут я лишь поделюсь с вами несколькими мыслями относительно использования планов обслуживания. Во-первых, планы обслуживания (Maintenance Plans) представляют собой еще один метод с графическим интерфейсом для настройки резервных копий. В этом отношении они простой способ «указать и щелкнуть» для обработки хранения резервных копий, о чем мы еще не говорили. Во-вторых, в силу природы этого метода, который позволяет вам выбрать Backups как вариант плана, а затем пройти по шагам каждую часть мастера процесса, Maintenance Plans может стать общим подходом для ИТ-профессионалов, вышедших из системных администраторов. Например, нет необходимости знать или понимать опции, представленные в мастере SSMS Backup.

Читайте также: