Розглянемо, як організувати два найчастіше зустрічаються завдання адміністрування SQL ServerЯ:
- Автоматичне резервне копіюваннябаз даних;
- Видалення старих резервні копії.
Планування резервних копій бази даних
- Відкрийте SQL Management Studio і підключіться до бази даних. Переконайтеся, що SQL Server Agent працює;
- Розгорніть вузол Management – Maintenance (для цього має бути роль «SYSADMIN») – клацніть правою кнопкою і виберіть «New Maintenance Plan»;
- Введіть назву нового плану обслуговування;
- Клацніть по іконці календаря праворуч у єдиному рядку. У вікні конфігуруйте час виконання завдання. Виберіть час, коли база даних менше завантажена;
- З розділу Toolbox перетягніть завдання Backup Database Task в основну область;
- Двічі клацніть на Backup Database Task – відкриється вікно налаштувань завдання резервного копіювання – задайте потрібні налаштування;
- Натисніть кнопку OK – тепер резервні копії будуть створюватися відповідно до запланованого часу;
Видалення старих резервних копій
Оскільки файли резервних копій будуть створюватися часто, незабаром вільного місця на жорсткому диску у Вас поменшає. Тому потрібно буде видаляти застарілі файли резервних копій. Продовжимо конфігурувати план обслуговування:
- З панелі Toolbox перетягніть в основну область завдання Maintenance Cleanup Task;
- Двічі клацніть Maintenance Cleanup Task, щоб відкрити вікно властивостей. У ньому Ви повинні визначити розташування резервних копій, їх розширення та визначити вік файлів, що підлягають видаленню. Хорошою практикою є зберігання бекапів до одного місяця;
- Натисніть OK та зберігаєте план обслуговування;
- Далі можете дочекатися наступного часу виконання плану обслуговування, або виконати його вручну (правою кнопкою миші за планом обслуговування в Object Explorer).
Рекомендується налаштувати регулярне резервне копіювання бази даних(на випадок апаратних або програмних збоїв), причому краще зі збереженням резервних копій за останні кілька днів, наприклад сім (за останній тиждень).
Для цього можна використовувати або вбудований в 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/Завдання» натиснути правою кнопкою миші на завдання, що цікавить, і в контекстному меню вибрати пункт «Запустити завдання на кроці», у вікні вибрати перший крок даного завдання і натиснути «ОК». Далі з'явиться вікно, що відображає хід виконання завдання. Якщо виконання завдання закінчиться з помилкою, то докладний описпомилки можна побачити, викликавши пункт «Перегляд журналу» того ж контекстного меню.
Різнисне резервне копіювання засноване на останній попередньої повної резервної копії даних. У різницевій резервній копії зберігаються ті зміни, які були зроблені з моменту створення останньої повної резервної копії.Рекомендації:
- Використовуйте різні копії БД, якщо створення повної копії БД займає великий проміжок часу
- Періодично робіть повну копію БД, щоб зменшити обсяги створюваних різницевих копій.
- Після створення повної копії БД всі попередні різницеві копії втрачають свою актуальність.
Наведу невеликий приклад із практики, чому ми почали використовувати різницеву копію. Згодом у нашого клієнта розрослася база даних до таких розмірів, що створення повної резервної копії займало 8 годин, ще кілька місяців і можливо до початку робочого дня не встигало завершитися дана операція. Після переведення на різницеве резервне копіювання ми скоротили час з 8 годин до 2-4 хвилин (залежно від дня тижня). Щотижня ми робили повну копію БД.
Приклад SQL для створення резервної різницевої копії БД з перевіркою копії після завершення (відмінна від повного копіюванняпрапор DIFFERENTIALзамість нього потрібно використовувати NOFORMAT).
Declare @pathBackup як varchar(55) set @pathBackup = N"C:\Backup\[Ім'я файлу БД]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + " .bak" BACKUP DATABASE [Назва бази даних] TO DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N"Повна База даних Резервне копіювання", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backS declare @pathBackup як 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(N"Помилка верифікації. Відомості про резервне копіювання для бази даних "[Ім'я бази даних]" не знайдені.", 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backSet NOUNLOAD, NOREWIND GO
3.Системні бази даних
Крім основної бази та пов'язаних з нею файлів, я настійно рекомендую робити копії та системні бази даних. Почнемо з того, що розглянемо які бази існують у MS SQL. Їх всього 5:Я вибрав резервувати лише 2 системні БД:
- msdb – тому що там зберігаються налаштовані завдання та інші
- 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 |
Створення копії системної бази |
1 раз на місяць |
Зберігається завжди лише останній екземпляр БД |
- Використовуйте опцію BACKUP WITH CHECKSUM
щоб переконатися, що все пройшло добре. Недоліком такого рішення і те, що з великих баз даних перевірка контрольної суми може серйозно завантажити систему. - Не виконуйте резервне копіювання файлів на той самий фізичний диск, де зберігається база даних або протокол транзакцій.
- Якщо ви використовуєте MS SQL 2008 або вище, рекомендую використовувати стиснення резервних копій засобами SQL. Наступний код увімкне стиснення за замовчуванням: USE master; GO EXEC sp_configure “backup compression default”, "1"; RECONFIGURE WITH OVERRIDE;
- тримайте резервні копії по кілька днів на випадок, якщо одна з них буде пошкоджена – стара резервна копія краща, ніж жодна.
- Використовуйте DBCC CHECKDBДля перевірки кожної бази даних перед копіюванням, це вчасно попередить вас про проблеми, що насуваються. DBCC CHECKDB ("Ім'я бази даних") WITH NO_INFOMSGS, ALL_ERRORMSGS; Примітка:на практики ми використовували цю перевірку, лише перед виконанням повної резервної копії.
- Виконуйте періодично оновлення статистики та реорганізації індексів БД
Використовуємо програму
Декілька нюансів за додатком:- Всі тексти та запити в коді винесені до ресурсів, мені так було простіше
- Під час введення параметрів з'єднання та інших параметрів вони зберігаються у файлі. Для Express і Standart використовуються різні файли (dbStandart, udExpress) у яких зберігається клас UserData
- Для виконання деяких операцій можуть знадобитися права адміністратора
- на Наразіне працює з'єднання з БД під доменним обліковим записом
- Програма не має суперкрасивого інтерфейсу
1. Налаштування повідомлення адміністратора
Мені було ліньки щоразу заходити на сервер і перевіряти, чи спрацювало завдання чи сталася якась помилка. Та й хотілося мати можливість отримувати інші повідомлення, не лише про виконання завдань.Для цього використовується DatabaseMail MS SQL (для версії Standart і вище)
У своєму додатку я зробив спеціальний розділ для автоматизації цього завдання
При натисканні з'явиться форма для заповнення інформації, необхідної для створення профілю розсилки листів:
Програма автоматично налаштована на стандартний 25 SMTP портдля адреси з якої надсилаються листи. При необхідності його можна змінити у процедурі sysmail_add_account_sp
Користувач та пароль потрібні на випадок, якщо у поштового сервісу настроєно автентифікацію.
Ім'я оператора в системі вказується для того, щоб у нас нормально створився профіль DatabaseMail. Пишіть будь-яку назву, яка буде для вас зрозумілою. Нижче наведено приклад заповненої форми.
- Змінюються системні параметри MS SQL.
- Створюється DatabaseMail Profile
- Активується в SQL Agent профіль
- Створюється DatabaseMail Account
- Додається DatabaseMail Account до Database Mail Profile
- Створюється DatabaseMail Operator
2.Додаткові повідомлення для адміністратора
У програмі передбачено 2 завдання, що застосовуються до БД:- перевірка цілісності БД. Для перевірки бази даних використовувалася стандартна процедура DBCC CHECKDB.
- інформування про вільному місціу файлових групах.
- Друге завдання було реалізовано за допомогою запиту до системної таблиці dbo.sysfiles
- Ось приклад даного запиту, який виконувався до бази:
Відповідь із сервера надходить на пошту адміністратора у вигляді html розмітки. Цей синтаксис можливий завдяки наступній стандартної функції MS SQL FOR XML.
Так само поки я шукав, як перетворити на повертаний результат виконання запитів у html текст, наткнувся на наступну сторінку, де людина створила цілу процедуру для цих цілей
Налаштувати ці операції можна за допомогою відповідного пункту меню програми:
З'явиться вікно для вказівки поштової скриньки, на яку необхідно надсилати html текст звіту:
3.Вирішення проблем при налаштуванні DatabaseMail
У MS SQL 2008 я зіштовхнувся із проблемою при налаштуванні SQL Server Agent. Симптоми наступні після налаштування неможливо запустити SQL Agent. В основному це вирішується за допомогою установки update на сервер SQL.Якщо ці оновлення не допомагають, потрібно завантажити fix. Його можна знайти на даному сайті кінцеве посилання не можу вказати зараз, для того щоб дійти до пакета фіксу, потрібно буде відповісти на ряд питань.
Якщо є проблеми із модулем DatabaseMail. Після налаштування цього модуля за допомогою програми необхідно зайти в SQL Agent і переглянути журнал подій. Якщо там будуть помилки «неможливо підключитися до поштовій скриньці». Значить, є проблема, навіть якщо через перевірку надсилається лист.
Виправляється це такими маніпуляціями:
- Management Studio - SQL Server Agent - Properties.
- Alert System
- Заберіть галочку з Enable mail profile
- Натисніть OK
- Зайдіть знову і поставте галочку
- Перезавантажте SQL Server 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" WITH REPLACE
У разі, якщо необхідно відновити послідовно спочатку повну копію, копії різниці та журнали транзакцій, тоді необхідно написати наступний 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.
Теги: Додати теги
Незважаючи на те, що в попередніх матеріалах ми вже стосувалися питання резервного копіювання баз Microsoft SQL Server читацький відгук показав необхідність створення повноцінного матеріалу з більш глибоким опрацюванням теоретичної частини. Дійсно, виконані з упором на практичні інструкціїстатті дозволяють швидко налаштувати резервне копіювання, але не пояснюють причин вибору тих чи інших налаштувань. Постараємося виправити цю прогалину.
Моделі відновлення
Перед тим, як братися за налаштування резервного копіювання, слід вибрати модель відновлення. Для оптимального виборуслід оцінити вимоги до відновлення та критичність втрати даних, зіставивши їх із накладними витратами на реалізацію тієї чи іншої моделі.
Як відомо, база даних MS SQL складається з двох частин: власне бази даних і лога транзакцій до неї. База даних містить власні і службові дані на поточний момент часу, лог транзакцій включає історію всіх змін бази даних за певний період, розташовуючи логом транзакцій ми можемо відкотити стан бази на будь-який довільний момент часу.
Для використання у виробничих середовищах пропонується дві моделі відновлення: проста та повна. Існує також модель з неповним протоколюваннямАле вона рекомендується лише як доповнення до повної моделі на період великомасштабних масових операцій, коли немає необхідності відновлення бази на певний момент часу.
Проста модельпередбачає резервне копіювання тільки бази даних, відповідно відновити стан БД ми можемо тільки на момент створення резервної копії, всі зміни у проміжок часу між створенням останньої резервної копії та збоєм будуть втрачені. В той же час проста схемамає невеликі накладні витрати: вам необхідно зберігати тільки копії бази даних, лог транзакцій при цьому автоматично усікається та не зростає у розмірах. Також процес відновлення найпростіший і не займає багато часу.
Повна модельдозволяє відновити базу на будь-який довільний момент часу, але вимагає, крім резервних копій бази, зберігати копії лога транзакцій за період, котрій може знадобитися відновлення. При активної роботиз базою обсяг лога транзакцій, отже, і обсяг архівів, можуть досягати величезних розмірів. Процес відновлення також набагато складніший і триваліший за часом.
При виборі моделі відновлення слід порівняти витрати на відновлення із витратами на зберігання резервних копій, також слід взяти до уваги наявність та кваліфікацію персоналу, який виконуватиме відновлення. Відновлення при повній моделі вимагає від персоналу певної кваліфікації та знань, тоді як за простої схеми достатньо буде слідувати інструкції.
Для баз з невеликим обсягом додавання інформації може бути вигідніше використовувати просту модель з великою частотою копій, яка дозволить швидко відновитися та продовжити роботу, ввівши втрачені дані вручну. Повна модель насамперед має використовуватися там, де втрата даних неприпустима, які можливе відновлення пов'язані з значними витратами.
Види резервних копій
Повна копія бази даних- як випливає з її назви, є вмістом бази даних і частиною активного лога транзакцій за той час, який формувалася резервна копія (тобто відомості про всі поточні та незавершені транзакції). Дозволяє повністю відновити базу даних на момент створення резервної копії.
Різнична копія бази даних- Повна копія має один істотний недолік, вона містить всю інформацію бази даних. Якщо резервні копії потрібно робити досить часто, то відразу виникає питання неекономного використання дискового простору, оскільки більшість сховища займатимуть однакові дані. Для усунення цього недоліку можна використовувати різницеві копії бази даних, які містять інформацію, що тільки змінилася з часу останнього повного копіювання.
Звертаємо увагу, різницева копія – це дані від моменту останнього повногокопіювання, тобто. кожна наступна різницева копія містить дані попередньої (але при цьому вони можуть бути змінені) і розмір копії постійно зростатиме. Для відновлення достатньо однієї повної та однієї різницевої копії, зазвичай останньої. Кількість різницевих копій слід вибирати виходячи з приросту їх розміру, як тільки розмір різницевої копії зрівняється з розміром половини повної, є сенс зробити нову повну копію.
Резервна копія журналу транзакцій- застосовується лише за повної моделі відновлення та містить копію журналу транзакцій починаючи з моменту створення попередньої копії.
Важливо пам'ятати наступний момент - копії журналу транзакцій ніяк не пов'язані з копіями бази даних і не містять інформацію попередніх копій, тому для відновлення бази вам необхідно мати безперервний ланцюг копій того періоду, протягом якого ви хочете мати можливість відкочувати стан бази. При цьому момент останнього успішного копіювання має бути всередині цього періоду.
Подивимося на малюнок вище, якщо буде втрачена перша копія файлу журналу, то ви зможете відновити стан бази тільки на момент повного копіювання, що буде аналогічно до простої моделі відновлення, відновити стан бази на будь-який момент часу ви зможете тільки після наступного різницевого (або повного) копіювання , За умови, що ланцюжок копій журналів починаючи з попереднього копіювання бази і далі буде безперервна (на малюнку - від третього і далі).
Журнал транзакцій
Для розуміння процесів відновлення та призначення різних видіврезервних копій слід детальніше розглянути пристрій та роботу журналу транзакцій. Транзакція – це мінімально можлива логічна операція, яка має сенс і може бути виконана лише повністю. Такий підхід забезпечує цілісність і несуперечність даних за будь-яких ситуаціях, оскільки проміжний стан операції неприпустимо. Для контролю за будь-якими змінами у базі призначено журнал транзакцій.
При виконанні будь-якої операції в журнал транзакцій додається запис про початок транзакції, кожному запису присвоюється унікальний номер (LSN) з нерозривної послідовності, при будь-якій зміні даних журнал вноситься відповідний запис, а після завершення операції в журналі з'являється позначка про закриття (фіксації) транзакції.
При кожному запуску система аналізує журнал транзакцій і відкочує всі незафіксовані транзакції, одночасно відбувається накат змін, які зафіксовані в журналі, але не були записані на диск. Це дає можливість використовувати кешування та відкладений запис, не побоюючись за цілісність даних навіть за відсутності систем резервного живлення.
Та частина журналу, яка містить активні транзакції та використовується для відновлення даних, називається активною частиною журналу. Вона починається з номера, який називається мінімальним номером відновлення (MinLSN).
У найпростішому випадку MinLSN – це номер запису першої незавершеної транзакції. Якщо подивитися на малюнок вище, то, відкривши синю транзакцію, ми отримаємо MinLSN рівну 321, після її фіксації в записі 324, номер MinLSN зміниться на 323, що відповідатиме номеру зеленої, ще не зафіксованої транзакції.
На практиці дещо складніше, наприклад, дані закритої синьої транзакції можуть бути ще не скинуті на диск і переміщення MinLSN на 323 зробить відновлення цієї операції неможливою. Щоб уникнути таких ситуації було запроваджено поняття контрольної точки. Контрольна точка створюється автоматично за наступних умов:
- У разі явного виконання інструкції CHECKPOINT. Контрольна точка спрацьовує у поточній базі даних з'єднання.
- При виконанні в базі даних операції з мінімальною реєстрацією, наприклад, під час операції масового копіювання для бази даних, на яку поширюється модель відновлення з неповним протоколюванням.
- Під час додавання або видалення файлів баз даних, використовуючи інструкцію ALTER DATABASE.
- При зупинці екземпляра SQL Server за допомогою інструкції SHUTDOWN або під час зупинки служби SQL Server (MSSQLSERVER). І в тому, і в іншому випадку буде створено контрольну точку кожної бази даних в екземплярі SQL Server.
- Якщо екземпляр SQL Server періодично створює у кожній базі даних автоматичні контрольні точки для скорочення часу відновлення бази даних.
- Під час створення резервної копії бази даних.
- Під час виконання дії, що вимагає відключення бази даних. Прикладами можуть бути присвоєння параметру AUTO_CLOSE значення ON і закриття останнього з'єднання користувача з базою даних або зміна параметра бази даних, що вимагає перезапуску бази даних.
Залежно від того, яка подія сталася раніше, MinLSN буде присвоєно значення номера запису контрольної точки, або початку найстарішої незавершеної транзакції.
Усічення журналу транзакцій
Журнал транзакцій, як і будь-який журнал, вимагає періодичного очищення від застарілих записів, інакше він розростеться і займе все доступне місце. Враховуючи, що при активній роботі з базою розмір лога транзакцій може значно перевищувати розмір бази, це питання актуальне для багатьох адміністраторів.
Фізично файл журналу транзакцій є контейнером для віртуальних журналів, які послідовно заповнюються зі зростанням лога. Логічний журнал, що містить запис MinLSN, є початком активного журналу, попередні логічні журнали є неактивними і не потрібні для автоматичного відновлення бази.
Якщо обрано просту модель відновлення, то при досягненні логічними журналами розміру рівного 70% фізичного файлу відбувається автоматичне очищення неактивної частини журналу, т.зв. усічення. Однак це не призводить до зменшення фізичного файлу журналу, усікаються лише логічні журнали, які після операції можуть використовуватися повторно.
Якщо кількість транзакцій велика і на момент досягнення 70% розміру фізичного файлу не виявиться неактивних логічних журналів, то розмір фізичного файлу буде збільшено.
Таким чином, файл лога транзакцій при простій моделі відновлення зростатиме відповідно до активності роботи з базою доти, доки не буде надійно вміщати всю активну частину журналу. Після чого його зростання припиниться.
При повній моделі неактивну частину журналу не можна усікти доти, доки вона повністю не потрапить у резервну копію. Усічення журналу проводиться за умови, що виконано резервну копію журналу транзакцій, після чого було створено контрольну точку.
Неправильне настроювання резервного копіювання журналу транзакцій при повній моделі може призвести до неконтрольованого зростання файлу журналу, що часто є проблемою для недосвідчених адміністраторів. Також часто трапляються поради щодо ручного усічення журналу транзакцій. При повній моделі відновлення робити цього не слід категорично, тому що ви порушите цілісність ланцюжка копій журналу і зможете відновити базу тільки на момент створення копій, що відповідатиме простої моделі.
У цьому випадку саме час згадати те, про що ми говорили на початку статті, якщо витрати на повну модель перевищують витрати на відновлення, слід віддати перевагу простої моделі.
Проста модель відновлення
Тепер, після отримання необхідного мінімуму знань, можна перейти до детальнішого розгляду моделей відновлення. Почнемо з простої. Припустимо, на момент збою у нас є одна повна та дві різницеві копії:
Резервне копіювання виконувалося раз на добу і остання копія була створена вночі з 21-го на 22-те. Збій відбувається увечері 22-го до створення чергової копії. У цьому випадку нам потрібно буде послідовно відновити повну та останню різницеві копії, при цьому дані за останній робочий день будуть втрачені. Якщо з будь-яких причин копія від 21-го також виявиться пошкоджена, то ми можемо відновити попередню копію, втративши ще день роботи, в той же час пошкодження копії за 20-е число ніяк не завадить успішно відновити дані на вечір 21-го, якщо наявності відповідної копії.
Повна модель відновлення
Розглянемо аналогічну ситуацію, але із застосуванням повної моделі відновлення. Резервні копії у нас також робляться щодобово, за принципом повна + різницеві, а також кілька разів на добу копіюється лог транзакцій.
Процес відновлення у разі буде складніший. Насамперед потрібно створити вручну резервну копію заключного фрагмента журналу (показана червоним), тобто. частина журналу з моменту минулого створення копії та до аварії.
Якщо цього не зробити, відновити базу можна буде лише до стану на момент створення останньої копії журналу транзакцій.
При цьому пошкодження файлу копії журналу за попередній день не завадить нам відновити стан бази, але обмежить нас моментом створення останньої копії, тобто. поточної доби.
Потім послідовно відновлюємо повну та різницеву копію та ланцюжок копій журналу, створену після останнього резервного копіювання, останньою відновлюємо копію заключного фрагмента журналу, що дасть нам можливість відновити базу прямо на момент аварії або довільний, що передував йому.
Якщо остання різницева копія буде пошкоджена, то у разі простої моделі це призведе до втрати ще одного робочого дня, повна модель дозволяє відновити передостанню копію, після чого потрібно буде відновити весь ланцюжок копій лога транзакцій від моменту передостанньої копії і до збою. Глибина відновлення залежить лише від глибини безперервного ланцюжка логів.
З іншого боку, якщо одна з копій лога транзакцій буде пошкоджена, скажімо, передостання, то відновити дані ми зможемо лише на момент останньої резервної копії + період у непошкодженому ланцюжку копій журналів. Наприклад, якщо журнали робилися о 12, 14 і 16 годин і пошкоджено журнал, створений о 14 годині, то маючи добової копією ми зможемо відновити базу до закінчення безперервної ланцюжка, тобто. До 12 години.
Теги:
А також: бекап SQL, бекап 1С.
Серверна 1С містить дані у базі даних, що знаходиться на SQL сервері. Сьогодні ми розглядаємо варіант MS SQL 2005/2008.
Щоб дані не були втрачені у разі згорілого диска сервера або інших форс-мажорних ситуацій – необхідно від початку робити бекапи (backup).
Робити ручками щодня Backup SQL бази 1С, звичайно, ніхто не хоче. Для цього є автоматичні засоби. Познайомимося із ними.
Налаштування Backup SQL
Налаштування SQL Backup для бази 1С нічим не відрізняється від налаштування бекапу для будь-якої іншої бази даних.
Для налаштування запустіть MS SQL Management Studio. Ця програма перебуває у групі програм MS SQL.
Додавання завдання бекапу SQL бази 1С
Завдання автоматичного бекапу баз SQL знаходяться у гілці Management/Maintenance plans.
Щоб додати нове завдання бекапу, клацніть правою кнопкою миші на групу Maintenance plans і виберіть New Maintenance Plan.
Введіть назву завдання. Назва має значення лише для Вас. Про всяк випадок краще використовувати англійські символи.
Налаштування завдання бекапу SQL бази 1С
Відкриється редактор завдань. Зверніть увагу – завдання можуть виконувати різні операції з базою даних, а не тільки бекапи.
Список варіантів операцій виведено зліва внизу. Виберіть Back Up Database Task подвійною кнопкою миші або просто перетягніть праворуч.
Зверніть увагу на стрілку. Ви можете перетягнути кілька різних або однакових операцій та зв'язати їх стрілками. Тоді виконуватиметься одразу кілька завдань у визначеній Вами послідовності.
У вікні налаштування виберіть потрібні бази SQL 1С (можна одразу кілька або по одній).
Виберіть місце збереження бекапу бази SQL 1С. Необхідно вибрати фізично інший вінчестер. Організаційно можна поставити галочку "Створити підпапки".
Тепер налаштуємо розклад backup. Розклад backup за замовчуванням додався сам. Але Ви можете додати кілька розкладів (наприклад, один – щоденний, один – щотижневий тощо). Натисніть кнопку налаштування розкладу backup.
На скріншоті приклад щоденного Backup SQL бази 1С о 3 ночі.
Щоб розклад backup у списку був красиво-зрозумілим, його можна змінити.
Збереження завдання бекапу SQL бази 1С
Натисніть на запис. Завдання з'явиться зліва у списку.
Це важливо! Перевірте правильність створення завдання Backup SQL бази. Для цього натисніть правою кнопкою на завдання і виберіть Execute.
В результаті повинен з'явитися файл бекапу вказаним шляхом. Якщо щось не так – видаліть завдання (Del) та почніть з початку.
Завантаження...