Создание планов резервного копирования и обслуживания MS SQL

Создание резервных копий

• Заходим в SSMS - Управление - Планы обслуживания - ПКМ - Создать план обслуживания...

• Называем план Backup

  

• Щелкаем 2 раза на строчку ВложенныйПлан_1, там переназываем все на Backup

• Открываем расписание, настраиваем необходимое, как правило ежедневно 1 раз в день

• С панели инструментов перетаскиваем задачу «Резервное копирование базы данных» на серую область под вложенными планами

  

• Нажимаем 2 раза на добавленную задачу, откроются свойства

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

• Переходим на вкладку «Целевой объект», проставляем, куда сохранять, и ставим галочку, чтобы создавались вложенные каталоги для БД

• На вкладке «Параметры» ставим галку «Проверять целостность резервной копии» и жмем ОК
• Слева находим значок сохранить и сохраняем план, после можно закрывать редактор, бэкапы настроены

Также добавляем в это задание автоочистку файлов резервных копий старше 5-7 дней.

• В параметрах указываем папку, куда складываются резервные копии - (O:\SQL\SQL_bak)
• Расширение — (bak)
• Ставим галочку «Include first-level subfolders »
• Ставим галочку «Delete files based on the age of the file at task run time » и указываем количество дней хранения резервных копий - (5 days)

Настройка плана обслуживания MS SQL

1. Создаем новый план обслуживания, называем MaintenancePlan, вложенные называем:

• Update statistics - Обновление статистики
• Reorganize index - Реорганизация Индекса
• Rebuild index - Перестроение индекса

---

2. Update statistics — добавляем объекты в план и связываем как на скриншоте (нажимаем на элемент и тащим стрелочку к следующему элементу)

• Открываем «T-SQL Statement Task», и вставляем:

DBCC FREEPROCCACHE -- Этот запрос очищает процедурный кэш

• Открываем «Проверку целостности», выбираем пользовательские базы, ставим «Включить индексы»

• В «History Cleanup task» выбираем все пункты, и желаемый период хранения журналов.

  

• Расписание настраиваем на ежедневное выполнение, желательно вне рабочего времени компании.

---

3. Reorganize index - реорганизация индекса. Из-за обновления 1С - просто добавить блок реорганизации нельзя, необходимо использовать доработанный sql скрипт (подробнее тут: Реорганизация индексов базы данных 1c MSSQL)

   

• Открываем «T-SQL Statement Task», и вставляем:

DECLARE @DBName varchar(255)  -- Переменная для хранения имени базы данных
DECLARE @TEMPLATE VARCHAR(MAX)  -- Шаблон SQL-запроса для получения таблиц и индексов
DECLARE @SQL_SCRIPT VARCHAR(MAX)  -- Переменная для хранения SQL-запроса, который будет выполняться

DECLARE @TableName varchar(255)  -- Переменная для хранения имени таблицы
DECLARE @IndexName varchar(255)  -- Переменная для хранения имени индекса
DECLARE @TEMPLATE_DB VARCHAR(MAX)  -- Шаблон SQL-запроса для изменения индексов

-- Шаблон SQL-запроса для получения таблиц и индексов, где отключены страничные блокировки
SET @TEMPLATE = 
    'DECLARE contact_cursor CURSOR FOR
    SELECT
        tab.name as tabName,  -- Имя таблицы
        ind.name as indName  -- Имя индекса
    FROM [{DBNAME}].[sys].[tables] as tab  -- Таблицы текущей базы данных
        inner join [{DBNAME}].[sys].[indexes] as ind on tab.object_id = ind.object_id  -- Индексы, принадлежащие таблице
    WHERE ind.allow_page_locks = 0  -- Выбираются индексы, у которых отключены страничные блокировки
        and ind.name is not null'  -- Исключаются индексы с пустыми именами

-- Шаблон для изменения индекса: включение страничных и строчных блокировок, реорганизация и отключение страничных блокировок
SET @TEMPLATE_DB =
    'ALTER INDEX [{INDEXNAME}] ON [{DBNAME}].[dbo].[{TABLENAME}] SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON)
    ALTER INDEX [{INDEXNAME}] ON [{DBNAME}].[dbo].[{TABLENAME}] REORGANIZE WITH (LOB_COMPACTION = ON)
    ALTER INDEX [{INDEXNAME}] ON [{DBNAME}].[dbo].[{TABLENAME}] SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON)'

-- Курсор для перебора всех баз данных, кроме системных, находящихся в состоянии ONLINE
DECLARE db_cursor CURSOR FOR
SELECT
    db.name as dbName  -- Имя базы данных
FROM
    [master].[sys].[databases] as db  -- Таблица баз данных
WHERE db.owner_sid <> 0x01 -- Исключаем системные базы данных и
     AND db.state_desc = 'ONLINE'   -- Выбираем только те, которые в состоянии ONLINE
--   AND db.name IN ('Database1', 'Database2')  -- Фильтр для выбора только определённых баз данных

-- Открытие курсора для баз данных
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName  -- Извлечение имени первой базы данных
WHILE @@FETCH_STATUS = 0  -- Пока есть базы данных для обработки
BEGIN
    PRINT @DBNAme  -- Выводим имя текущей базы данных
    SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{DBNAME}', @DBName)  -- Заменяем в шаблоне имя базы данных
    EXECUTE(@SQL_SCRIPT)  -- Выполняем SQL-запрос для получения таблиц и индексов

    -- Открываем курсор для таблиц и индексов текущей базы данных
    OPEN contact_cursor
    FETCH NEXT FROM contact_cursor INTO @TableName, @IndexName  -- Извлекаем первую таблицу и индекс
    WHILE @@FETCH_STATUS = 0  -- Пока есть таблицы и индексы для обработки
    BEGIN
        PRINT '    Table: ' + @TableName + '; Index: ' + @IndexName  -- Выводим имя таблицы и индекса
        -- Подставляем значения имен базы данных, таблицы и индекса в шаблон для изменения индекса
        SET @SQL_SCRIPT = REPLACE(@TEMPLATE_DB, '{DBNAME}', @DBName)
        SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{INDEXNAME}', @IndexName)
        SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{TABLENAME}', @TableName)
        EXECUTE(@SQL_SCRIPT)  -- Выполняем изменения индекса
        FETCH NEXT FROM contact_cursor INTO @TableName, @IndexName  -- Извлекаем следующую таблицу и индекс
    END
    -- Закрываем и удаляем курсор для таблиц и индексов
    CLOSE contact_cursor
    DEALLOCATE contact_cursor

    FETCH NEXT FROM db_cursor INTO @DBName  -- Извлекаем следующую базу данных
END

-- Закрываем и удаляем курсор для баз данных
CLOSE db_cursor
DEALLOCATE db_cursor
GO

Если нужно выбрать только определенные базы, то необходимо раскомментировать строку и вместо «Database1», «Database2»- указать желаемые базы.

    AND db.name IN ('Database1', 'Database2')  -- Фильтр для выбора только определённых баз данных

• Расписание настраиваем на еженедельное выполнение, желательно вне рабочего времени компании.

---

4. Rebuild index — перестроение индекса

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

  

• Расписание настраиваем на ежемесячный режим в выходной день.
  

---

После настройки плана, не забываем его сохранить.

Итоговый вид плана обслуживания: