Автоматизація налаштування резервного копіювання MS SQL за допомогою .NET додатки

Я довго визрівав, щоб написати цю статтю і викласти свою програму. Сподіваюся вам буде цікаво.

Про що ця стаття


В ній описаний той спосіб, як за допомогою розробленого мною .NET додатки можна поширювати план резервного копіювання і проводити всі необхідні налаштування над БД засобами СУБД з повідомленням адміністратора про виконання завдань.

Максимум постараюся описати ті нюанси, з якими мені довелося зіткнутися в ході розробки програми та налаштування БД.
Для таких задач можна використовувати майстер планів обслуговування, але мені більше сподобався такий підхід. Основна перевага описаного мною методу, що цей спосіб можна застосовувати до всіх версіями MS SQL (крім Express, там трохи інший підхід). План обслуговування можна переносити, але у вас повинна бути відповідна версія MS SQL і все одно буде створено Job для запуску плану обслуговування.

Порівняти редакції MS SQL і їх функціонал ви можете ось тут.
Обережно: перфекціоністи можуть випробувати печіння і біль при перегляді коду мого програми, т. до. воно було написано на швидку руку і заточене під рішення конкретної задачі.

Кому підійде дана стаття:
  • Тим, у кого MS SQL Express і немає можливості запускати з допомогою Job завдання
  • Тим, хто в найближчому майбутньому планує перейти з MS SQL 2008 на більш нову версію і не хоче налаштовувати віддзеркалення БД, а відразу на новій версії налаштувати AlwaysOn
  • Тим, у кого немає коштів для підняття ще резервних серверів і доводиться обходитися тим, що є.
  • У кого немає стислих термінів на час відновлення БД. Головне – це результат
  • Кому лінь щось робити
  • Просто цікавим людям.

Список статей хабра, які я використовував
  1. Створення і зберігання резервних копій баз даних в MS SQL. Практичні поради
  2. Побудова ланцюжка відновлень баз даних MS SQL
  3. Налаштування Database Mail в MS SQL Server 2005 і старше
  4. SQL Server 2008: бэкапим з розумом. Частина 1: Теорія
  5. Все що ви соромилися запитати про бэкапах Microsoft SQL Server
Исходники на github для MS SQL Standart і для MS SQL Express
Якщо з'явиться бажаючі додати свої думки в код, приймаю pull request. Готовий вислухати конструктивну критику і доопрацювати програму, якщо це дійсно комусь треба буде.

Теорія про резервне копіювання

Все що описано в теорії, ви можете знайти самостійно. Конфігурації, які описані в даному розділі, автоматично будуть виконані моїми додатком при налаштуванні резервного копіювання.
MS SQL Server підтримує 3 моделі резервного копіювання.
  1. Просту
  2. Модель повного відновлення
  3. Модель повного відновлення з неповним протоколюванням
Я вибрав для додатка модель повного відновлення, оскільки мені необхідно було мати можливість завжди відновити останню версію БД після будь-якої операції і у мене не було одномоментних масових операцій по вставці даних. Якщо ви тільки починаєте і не знаєте, як правильно вибрати, вам може допомогти ось ця стаття Microsoft.
Для включення цього режиму необхідно виконати наступний скрипт
ALTER DATABASE [Ім'я бази даних] SET RECOVERY FULL;


При перемиканні моделі відновлення на повну у нас з'явиться наступні можливості:
  1. СУБД почне вести журнал запису транзакцій
  2. Створення різницевої резервної копії
  3. Створення повної резервної копії

Нижче будуть описані деякі нюанси, пов'язані з резервним копіювання журналів транзакцій і різницевий копій. З приводу повного копіювання у мене зауважень ніяких немає, просто робіть її періодично і все у вас буде добре

1. Журнал транзакцій
Журнал транзакцій є критичним компонентом бази даних і у разі системного збою може знадобитися для приведення бази даних в узгоджене стан.

Переваги при відновленні БД за допомогою журналу транзакцій:
  1. відновлення окремих транзакцій;
  2. відновлення всіх незавершених транзакцій при запуску SQL Server;
  3. накат відновленої бази даних файлу, файлової групи чи сторінки до моменту збою і т. д
Рекомендації
  1. Винести на швидкий жорсткий диск, щоб при великому потоці операцій не було затримок при записі.
  2. Необхідно робити резервні копії журналу транзакцій не рідше ніж щогодини.
  3. Після створення повної (різницевої) копії бази даних, всі старі журнали можна видаляти, оскільки вони втрачають свою актуальність.
  4. Уважно стежте за розміром диска на якому зберігаються журнали транзакцій, якщо воно закінчиться, то записати нові дані в БД буде неможливо, поки не відбудеться зменшення розмірів журналу транзакцій або не додатися новий додатковий файл транзакцій.
  5. Журнал транзакцій необхідно регулярно усекать, щоб уникнути його переповнення. UPD: Як сказав kolu4iy дана операція по усічення злегка сумнівна в плані продуктивності, т. к. при бэкапирование журнал транзакції очищається всередині і СУБД починає писати в ньому по новій. Проте у вас може виникнути ситуація, яку я описав у своєму коментарі і тоді це вам може знадобитися.
  6. Можлива ситуація, коли неможливо відразу зробити усічення журналу. Вони описані в даній статті
  7. Для отримання інформації про стан бази даних можна за допомогою наступного запиту:
    select name,log_reuse_wait, log_reuse_wait_desc from sys.databases

  8. При необхідності можна отримати інформацію про останніх відкритих транзакції
    DBCC OPENTRAN (Ім'я бази даних) WITH TABLERESULTS
    
Приклад SQL скрипта для виконання резервного копіювання журналу транзакції з подальшим усіканням файлу.
BACKUP LOG [Ім'я бази даних] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL\MSSQL\Backup\[Ім'я файлу].bak' WITH NOFORMAT, NOINIT, 
NAME = 'Журнал транзакцій Резервне копіювання', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE [Ім'я бази даних]
GO
DBCC SHRINKFILE ('Ім'я файлу лода БД' , 25)
GO

Ці ж операції можна виконати за допомогою SSMS

2.Різницева копія БД
Різницеве резервне копіювання засноване на самій останній попередньої повної резервної копії даних. У різницевої резервної копії зберігаються тільки ті зміни, які були проведені з моменту створення останньої повної резервної копії.
Рекомендації:
  1. Використовуйте різницеві копії БД, якщо створення повної копії БД займає великий проміжок часу
  2. Періодично робіть повну копію БД, щоб зменшити обсяги створюваних різницевих копій.
  3. Після створення повної копії БД, всі попередні різницеві копії втрачають свою актуальність.
Більш детально про рекомендації по частоті створення різницевих резервних копій, можна прочитати тут.

Наведу невеликий приклад з практики, чому ми стали використовувати різницеву копію. З часом у нашого клієнта розрослася база даних до таких розмірів, що створення повної резервної копії займало 8 годин, ще кілька місяців і можливо, до початку робочого дня не встигала б завершитися дана операція. Після перекладу на різницеве резервне копіювання, ми скоротили час з 8 годин до 2-4 хвилин (залежно від дня тижня). Раз в тиждень ми робили повну копію БД.

Приклад SQL для створення резервної різницевої копії БД з перевіркою копії по завершенню (відрізняється від повного копіювання прапором DIFFERENTIAL замість нього потрібно використовуватиNOFORMAT).

declare @pathBackup as varchar(55)
set @pathBackup = N'C:\Backup\[Ім'я файлу БД]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak'
BACKUP DATABASE [Ім'я бази даних] TO DISK = @pathBackup
WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = 'Повна База даних Резервне копіювання', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId int as
declare @pathBackup as varchar(55)
set @pathBackup = N'C:\Backup\[Ім'я файлу БД]_' + REPLACE(convert(varchar,GETDATE(), 104),'.','_') + '.bak'
select @backupSetId = position from msdb..backupset where database_name=N'[Ім'я бази даних]' 
and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'[Ім'я бази даних]')
if @backupSetId is null 
begin 
raiserror('Помилка верифікації. Відомості про резервне копіювання бази даних "[Назва бази даних]" не знайдено.', 16, 1) 
end
RESTORE VERIFYONLY FROM DISK = @pathBackup
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO


3.Системні бази даних
Крім основної бази та пов'язаних з нею файли, я настійно рекомендую робити копії і системних баз даних. Почнемо з того, що розглянемо які бази існують в MS SQL. Їх усього 5:








Назва
Опис
База даних master
У цій базі даних зберігаються всі дані системного рівня для екземпляра SQL Server.
База даних msdb
Використовується агентом SQL Server для планування попереджень та завдань.
База даних model
Використовується в якості шаблону для всіх баз даних, які створюються в екземпляра SQL Server. Зміна розміру, зміна параметрів сортування, моделі відновлення та інших параметрів бази даних model призводить до зміни відповідних параметрів всіх баз даних, які створюються після зміни.
База даних resource
База даних тільки для читання. Містить системні об'єкти, які входять до складу SQL Server. Системні об'єкти фізично зберігаються в базі даних Resource, але логічно відображаються в схемі sys будь-якої бази даних.
База даних tempdb
Робочий простір для тимчасових об'єктів або взаємодії результуючих наборів.
Детальніше можете прочитати про них тут і ще ось тут.

Я вибрав резервувати тільки 2 системні БД:
  1. msdb – тому що там зберігаються налаштовані завдання та інші
  2. master – зберігаються всі вироблені налаштування SQL Server.
Дана інформація все одно не дуже критична і її можна відновити руками, але навіщо витрачати зайвий час, коли можна просто взяти з резервної копії.

4. План бекапирования
На основі описаного вище складемо наш план резервного копіювання даних. Він може відрізнятися від того, що буде потрібно вам, все залежить від вимог до відновлення БД. Коли я готував план, мені довелося врахувати, що необхідно відновити дані максимально і втрата даних становила не більше однієї години.

Ми будемо робити наступні резервні копії:
  • Повна копія основної БД, частіше ніж раз в тиждень немає необхідності
  • Різницева копія основної БД, кожен день
  • Копії журналу транзакцій основний БД, щогодини
  • Копія системної БД master, раз на тиждень
  • Копія системної БД msdb, раз на тиждень
У результаті у нас вийшов наступний план резервного копіювання даних:










День тижня
Час
Дії
Частота
Опис
Понеділок — П'ятниця
З 8-00 до 21-00
Резервні копії

Журналу транзакцій
щогодини
Після виконання резервної копії БД йде стиснення і скорочення журналу транзакцій
Субота — Неділя
З 8-00 до 18-00
Понеділок – Неділя
22-00
Різницева копія основної БД
1 разу в день
Після успішного виконання різницевої копії видаляються всі старі копії журналу транзакцій
Субота
12-00
Перевірка БД
1 разу в день
Перевірка БД Справу на цілісність.
Субота
18-00
Створення повної копії БД
1 разу в день
По завершенню даної операції йде повідомлення на пошту.

 

Якщо створення резервної копії пройшло вдало, видаляється

  • стара повна резервна копія
  • всі старі різницеві копії

  • всі старі журнали транзакцій
Понеділок – Неділя
23-30
Створення копії системної бази master
1 разу в день
Зберігається завжди тільки останній примірник БД
Неділя
12-30
Створення копії системної бази даних msdb
1 разу в місяць
Зберігається завжди тільки останній примірник БД
5. Загальні рекомендації по резервному копіюванню
  1. Використовуйте опцію
    BACKUP WITH CHECKSUM

    щоб переконатися, що все пройшло добре. Недоліком такого рішення є те, що для великих баз даних перевірка контрольної суми може серйозно завантажити систему.
  2. Не виконуйте резервне копіювання файлів на той же фізичний диск, на якому розміщено базу даних, або протокол транзакцій.
  3. Якщо ви використовуєте MS SQL 2008 або вище, рекомендую вам використовувати стиснення резервних копій засобами SQL. Наступний код включить стиснення за замовчуванням:
    USE master; GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE;
  4. тримайте резервні копії по кілька днів на випадок, якщо одна з них буде пошкоджена – стара резервна копія краще, ніж ніякої.
  5. Використовуйте DBCC CHECKDB для перевірки кожної бази даних перед копіюванням, це вчасно попередить вас про проблеми, що насуваються.
    DBCC CHECKDB ('Ім'я бази даних') WITH NO_INFOMSGS, ALL_ERRORMSGS;
    Примітка: практики ми використовували дану перевірку, тільки перед виконанням повної резервної копії.
  6. Виконуйте періодично оновлення статистики та реорганізації індексів БД

Використовуємо додаток

Кілька нюансів з додатком:
  • Всі тексти і запити в коді винесені на ресурси, мені так було простіше
  • При введенні параметрів з'єднання і інших налаштувань, вони зберігаються у файл. Для Express і Standart використовуються різні файли (dbStandart, udExpress) в них зберігається клас UserData
  • Для виконання деяких операцій можуть потрібні права адміністратора
  • На даний момент не працює з'єднання з БД під доменної обліковим записом
  • Програма не має суперкрасивым інтерфейсом
1. Налаштування повідомлення адміністратора
Мені було лінь кожен раз заходити на сервер і перевіряти, чи спрацював завдання чи сталася якась помилка. Так і хотілося мати можливість отримувати інші повідомлення, не тільки про виконання завдань.

Для цієї мети використовується DatabaseMail MS SQL (для версії Standart і вище)
У своєму додаток я зробив спеціальний розділ для автоматизації даної задачі



При натисканні відкриється форма для заповнення інформації необхідної для створення профілю розсилки листів:



Додаток автоматично налаштований на стандартний 25 SMTP порт для адреси з якого відправляються листи. При необхідності його можна змінити в процедурі sysmail_add_account_sp
Користувач та пароль потрібні на випадок, якщо поштового сервісу налаштована аутентифікація.

Ім'я оператора в системі вказується для того, щоб у нас нормально створився профіль DatabaseMail. Пишіть будь-яку назву, яка буде для вас зрозумілим. Нижче наведено приклад заповненої форми.



Далі з цього ящика від зазначеного оператора, до нас будуть приходити повідомлення про успішне виконання операцій.
Виконувані дії на даному етапі:
  1. Змінюються системні параметри MS SQL.
  2. Створюється DatabaseMail Profile
  3. Активується в SQL Agente профіль
  4. Створюється DatabaseMail Account
  5. Додано DatabaseMail Account до Database Mail Profile
  6. Створюється DatabaseMail Operator
Більш докладно описано статті і частково я брав звідси. Природно дані дії можна виконати за допомогою SSMS.
2.Додаткові повідомлення для адміністратора
У програмі передбачено 2 завдання, що застосовуються до БД:
  1. перевірка цілісності БД. Для перевірки бази даних використовувалася стандартна процедура DBCC CHECKDB.
  2. інформування про вільному місці в файлових групах.
  3. Друга задача була реалізована з допомогою запиту до системної таблиці dbo.sysfiles
  4. Ось приклад даного запиту, який виконувався на базі:
Select 
NAME = left(a.NAME,15),
a.FILEID,
[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
FILENAME = a.FILENAME
From dbo.sysfiles a

Відповідь з сервера приходить на пошту адміністратора у вигляді html-розмітки. Даний синтаксис можливий завдяки такій стандартної функції MS SQL FOR XML.

Так само поки я шукав, як перетворити у зворотний результат виконання запитів в html-текст, натрапив на наступну сторінку, де людина створила цілу процедуру для цих цілей
Налаштувати ці операції можна з допомогою відповідного пункту в меню програми:



З'явитися вікно для вказівки поштової скриньки, на яку необхідно надіслати html текст звіту:



3.Рішення проблем при налаштуванні DatabaseMail
В MS SQL 2008 я зіткнувся з проблемою при налагодженні SQL Server Agent. Симптоми такі, після налаштування неможливо запустити SQL Agent. В основному це вирішується за допомогою установки update на SQL сервер.

Переконайтеся, що встановлено sp1а потім можна вже ставити оновлення.

Якщо оновлення не допомагають, необхідно завантажити fix. Його можна знайти на даному сайт кінцеву посилання не можу вказати зараз, для того що б дійти до пакету фікса, потрібно відповісти на ряд питань.
Якщо є проблеми з модулем DatabaseMail. Після налаштування цього модуля за допомогою додатка, необхідно зайти в SQL Agent і переглянути журнал подій. Якщо там будуть помилки «неможливо підключитися до поштової скриньки». Значить є проблема, навіть якщо через перевірку відправляється лист.

Виправляється це такими маніпуляціями:
  1. Management Studio, SQL Server Agent — Properties.
  2. Alert System
  3. Приберіть галочку з Enable mail profile
  4. Натисніть OK
  5. Зайдіть знову і поставте галочку
  6. Перезавантажте SQL Server Agent.
Перевірте обліковий запис для SQL service Agent. Якщо це доменна обліковий запис змініть її на системну або навпаки. Все повинно запрацювати.

4.Налаштовуємо резервне копіювання за допомогою програми SQL Standart:
Вибираємо версію Standart. Налаштовуємо повідомлення. (див. розділ налаштування повідомлення):



Єднаємося з БД, заповнюючи дані для з'єднання і вказуємо БД, для якої буде застосовуватися Job:



Вибираємо налаштування резервного копіювання:



Вказуємо шлях для збереження копій БД. Якщо зазначені папки не існує, програма спробує їх створити (потрібні відповідні права).



Натискаємо зберегти і базі налаштовуються відповідні завдання. Бажано налаштувати для кожного бекапа різні папки, т. к. при видаленні будуть видалятися всі файли з розширенням .bak. (див. розділ видалення копій БД)



5.Налаштовуємо резервне копіювання за допомогою програми для SQL Express:
Так як в SQL Express відсутня SQL Agent, завдання по автоматизації резервного копіювання довелося вирішити іншим шляхом. У вказаній користувачем папці створюється bat файл в якому описаний SQL запит, який відповідає за створення резервної копії. У разі необхідності можна редагувати безпосередньо. По мимо цього повинен працювати стандартний планувальник Windows, в ньому створюється задача, яка буде запускати раз на добу у визначений час.

Для цього запускаємо програму. Вибираємо пункт MS SQL Express:



З'являється форма для заповнення параметрів:



Вказуємо де буде зберігатися наша копія, а також де буде лежати bat файл для створення копії бази (ім'я файлу вказувати не треба, воно буде вказано автоматично). Далі вказуємо налаштування з'єднання і час, коли необхідно запускати завдання.

Єдиний мінус даного підходу в тому, що доводиться зберігатися у відкритому вигляді пароль для з'єднання з БД.

6.Видалення завдань з БД.
Якщо необхідно видалити всі завдання з БД (наприклад, захотіли змінити шляхи збереження БД). Для цього використовуємо відповідний пункт у меню програми. З SQL Agent будуть видалені всі завдання з певним початковим префіксом (в моєму випадку King):



7.Видалення копій БД
У деяких завданнях, налаштоване видалення старих копій БД. Для цього я використовую процедуру master.dbo.xp_delete_file. Приклад використання: Видалить всі файли з розширенням bak з вказаної папки, дата створення яких перевищує 14 днів.

EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N'bak',dateadd(d,-14,getdate()),0;

І ось ще один більш докладний приклад інформація про те, які параметри приймає дана функція.

Як відновлювати резервні копії

З-за браку часу модуль відновлення ще не реалізований, можливо, в майбутньому я його додам, а поки просто коротко опишу як можна буде відновити базу.

За допомогою SQL скрипта. Для відновлення бази даних використовується команда RESTORE.

Якщо потрібно просто відновити базу з повної копії, то достатньо виконати наступний скрипт:

RESTORE DATABASE [Ім'я бази даних] FROM DISK = 'Z:\SQLServerBackups\back.bak' REPLACE WITH

У випадку, якщо необхідно відновити послідовно спочатку повну копію, різницеві копії та журнали транзакцій, тоді необхідно написати наступний SQL скрипт.

RESTORE DATABASE TEST_DB –відновлюємо повну копію
FROM test_db_full
WITH NORECOVERY;
GO
RESTORE DATABASE TEST_DB –відновлюємо різницеву копію
FROM test_db_diff
WITH FILE = 1,
NORECOVERY;
GO
RESTORE LOG TEST_DB –відновлюємо журнал транзакцій №1
FROM test_db_tran_1
WITH FILE = 1,
WITH NORECOVERY;
GO
RESTORE LOG TEST_DB –відновлюємо журнал транзакцій №2
FROM test_db_tran_2
WITH FILE = 1,
WITH NORECOVERY;
GO
RESTORE DATABASE TEST_DB
WITH RECOVERY;
GO

Для відновлення БД можна використовувати так само і SSMS.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.