Створення резервних копій баз даних sql server. Налаштування регулярного резервного копіювання БД MS SQL Server

Сервери баз даних - одні з ключових в будь-якій організації. Саме вони зберігають інформацію і надають видачу за запитом, і вкрай важливо зберегти БД в будь-якій ситуації. Базова поставка зазвичай включає потрібні утиліти, але адміну, не стикалися до цього з БД, доведеться деякий час розбиратися з особливостями роботи, щоб забезпечити автоматизацію.

Види бекапов баз даних

Для початку розберемося з тим, які взагалі бувають бекапи. Сервер баз даних не є звичайним десктопних додатком, і, щоб забезпечити виконання всіх властивостей ACID (Atomic, Consistency, Isolated, Durable), використовується ряд технологій, а тому створення і відновлення БД з архіву має свої особливості. Існують три різних підходи до створення резервної копії даних, кожен з яких має свої плюси і мінуси.

При логічному, або SQL, бекапе (pg_dump, mysqldump, SQLCMD) створюється миттєвий знімок вмісту бази з урахуванням транзакционной цілісності і зберігається у вигляді файлу з SQL-командами (можна вибрати всю базу або окремі таблиці), за допомогою якого можна відтворити базу даних на іншому сервері. На це потрібен час (особливо для великих баз) для збереження і відновлення, тому дуже часто цю операцію виконувати не можна і її виробляють під час мінімального навантаження (наприклад, вночі). При відновленні адміністратор повинен буде виконати кілька команд, щоб підготувати все необхідне (створити порожню базу даних, облікові записита інше).

Фізичний бекап (рівня файлової системи) - копіювання файлів, які СУБД використовує для зберігання даних в базі даних. Але при простому копіюванні ігноруються блокування і транзакції, які, швидше за все, будуть неправильно збережені і порушені. При спробі приєднати цей файл він буде в неузгоджену стані і призведе до помилок. Щоб отримати актуальний бекап, базу даних потрібно зупинити (можна зменшити час простою, використавши два рази rsync - спочатку на працюючій, потім на зупиненої). Недолік цього методу очевидний - не можна відновити певні дані, тільки всю базу даних. При старті БД, відновленої з архіву файлової системи, потрібно буде провести перевірку на цілісність. Тут використовуються різні допоміжні технології. Наприклад, в PostgreSQL логи упреждающей журналізації WAL (Write Ahead Logs) і спеціальна функція (Point in Time Recovery - PITR), що дозволяє повернутися до певного стану бази. З їх допомогою легко реалізується третій сценарій, коли бекап рівня файлової системи об'єднується з резервною копією WAL-файлів. Спочатку відновлюємо файли резервної копії файлової системи, а потім за допомогою WAL база приводиться до актуального стану. Це трохи більше складний підхід для адміністрування, але зате немає проблем з цілісністю БД і відновленням баз до певного часу.

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

Barman

Ліцензія: GNU GPL

Підтримувані СУБД: PostgreSQL

PostgreSQL підтримує можливості фізичного і логічного бекапа, додаючи до них ще один рівень WAL (див. Врізку), який можна назвати безперервним копіюванням. Але керувати за допомогою штатних інструментів декількома серверами не дуже зручно навіть адміну зі стажем, а в разі збою рахунок йде на секунди.

Barman (backup and recovery manager) - внутрішня розробка компанії 2ndQuadrant, що надає послуги на базі PostgreSQL. Призначений для фізичного бекапа PostgreSQL (логічний не підтримує), архівування WAL і швидкого відновленняпісля збоїв. Підтримуються віддалений бекап і відновлення кількох серверів, функції point-in-time-recovery (PITR), управління WAL. Для копіювання і подачі команд на віддалений вузол використовується SSH, синхронізація і бекап за допомогою rsync дозволяє скоротити трафік. Також Barman інтегрується зі стандартними утилітами bzip2, gzip, tar і подібними. В принципі, можна використовувати будь-яку програму стиснення та архівування, інтеграція не займе багато часу. Реалізовано різні сервісні і діагностичні функції, що дозволяють контролювати стан сервісів і регулювати смугу пропускання. Підтримуються Pre / Post-скрипти.

Barman написаний на Python, управління політиками резервного копіювання проводиться за допомогою зрозумілого INI-файлу barman.conf, який може знаходитися в / etc або домашньому каталозі користувача. У постачанні йде готовий шаблонз докладними коментарями всередині. Працює тільки на * nix-системах. Для установки в RHEL, CentOS і Scientific Linux слід підключити EPEL - репозиторій, в якому містяться додаткові пакети. У розпорядженні користувачів Debian / Ubuntu офіційний репозиторій:

$ Sudo apt-get install barman

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

Sypex Dumper

Ліцензія: BSD

Підтримувані СУБД: MySQL

Разом з MySQL поставляються утиліти mysqldump, mysqlhotcopy, що дозволяють легко створити дамп бази даних, вони добре задокументовані, і в інтернеті можна знайти велику кількість готових прикладів і фронтенда. Останні дозволяють новачкові швидко приступити до роботи. Sypex Dumper є PHP-скрипт, який дозволяє легко створити і відновити копію бази даних MySQL. Створювався для роботи з великими базами даних, працює дуже швидко, зрозумілий і зручний у використанні. Вміє працювати з об'єктами MySQL - уявленнями, процедурами, функціями, тригерами і подіями.

Ще один плюс, на відміну від інших інструментів, при експорті виробляють перекодування в UTF-8, - в Dumper експорт виробляється в рідній кодуванні. Результуючий файл займає менше місця, а сам процес відбувається швидше. В одному дампі можуть бути об'єкти з різними кодуваннями. Причому легко імпорт / експорт зробити в кілька етапів, зупиняючи процес під час навантаження. При поновленні процедура почнеться з місця зупинки. При відновленні підтримується чотири варіанти:

• CREATE + INSERT - стандартний режим відновлення;
• TRUNCATE + INSERT - менше часу на створення таблиць;
• REPLACE - відновлюємо в робочій базі старі дані, що не затираючи нові;
• INSERT IGNORE - додаємо в базу віддалені або нові дані, не чіпаючи існуючі.

Підтримується стиснення копії (gzip або bzip2), Автовидалення старих резервних копій, реалізований перегляд вмісту дамп-файлу, відновлення тільки структури таблиць. Є і сервісні функції з управління БД (створення, видалення, перевірка, відновлення БД, оптимізація, очищення таблиць, робота з індексами і інше), а також файл-менеджер, що дозволяє копіювати файли на сервер.

Управління проводиться за допомогою веб-браузера, інтерфейс з використання AJAX локалізована з коробки і створює враження роботи з настільним додатком. Також можливо запускати завдання з консолі і за розкладом (через cron).

Для роботи Dumper знадобиться класичний L | WAMP-сервер, установка звичайна для всіх додатків, написаних на PHP (копіюємо файли і встановлюємо права), і не буде складною навіть для новачка. Проект надає детальну документацію і відеоуроки, що демонструють роботу з Sypex Dumper.

Є дві редакції: Sypex Dumper (безкоштовно) і Pro (10 доларів). Друга має більше можливостей, все відмінності наведені на сайті.

SQL Backup And FTP

Ліцензія:

Підтримувані СУБД: MS SQL Server

MS SQL Server - одне з популярних рішень, а тому зустрічається досить часто. Завдання резервного копіювання створюється за допомогою середовища SQL Server Management Studio, Власне Transact-SQL і командлетів модуля SQL PowerShell (Backup-SqlDatabase). На сайті MS можна знайти просто величезна кількість документації, яка дозволяє розібратися з процесом. Документація хоча і повна, але дуже специфічна, а інформація в інтернеті часто суперечить один одному. Новачкові дійсно спочатку потрібно потренуватися, «набивши руку», тому, навіть незважаючи на все сказане, стороннім розробникам є де розвернутися. До того ж безкоштовна версія SQL Server Express не може похвалитися вбудованими інструментами для резервного копіювання. Для більш ранніх версій MS SQL (до 2008) можна знайти безкоштовні утиліти, наприклад SQL Server backup, але в більшості подібні проекти вже комерціалізувалися, хоча і пропонують всю функціональність часто за символічну суму.

Наприклад, розробка SQL Backup And FTP і One-Click SQL Restore відповідає принципу «налаштував і забув». Володіючи дуже простим і зрозумілим інтерфейсом, вони дозволяють створювати копії баз даних MS SQL Server (включаючи Express) і Azure, зберігати зашифровані і стислі файли на FTP і хмарних сервісах (Dropbox, Box, Google Drive, MS SkyDrive або Amazon S3), результат можна тут же переглянути. Можливий запуск процесу як вручну, так і за розкладом, відправка повідомлення про результат завдання по email, запуск скриптів.

Підтримуються всі варіанти бекапа: повний, диференційний, журнал транзакцій, копіювання папки з файлами і багато іншого. Старі резервні копії видаляються автоматично. Для підключення до віртуального вузла використовується SQL Management Studio, хоча тут можуть бути нюанси і це буде працювати не в усіх таких конфігураціях. Для завантаження пропонується п'ять версій - від безкоштовної Free до навороченій Prof Lifetime (на момент написання цих рядків коштувала всього 149 доларів). Функціоналу Free цілком достатньо для невеликих мереж, в яких встановлено один-два SQL-сервера, всі основні функції активні. Обмежена кількість резервних БД, можливість відправки файлів на Google Drive і SkyDrive і шифрування файлів. Інтерфейс хоча і не локалізована, але дуже простий і зрозумілий навіть новачкові. Потрібно лише підключитися до SQL-сервера, після чого буде виведений список баз даних, слід зазначити потрібні, налаштувати доступ до віддалених ресурсів і вказати час виконання завдання. І все це в одному вікні.

Але є одне але". Сама програма не призначена для відновлення архівів. Для цього пропонується окрема безкоштовна утиліта One-Click SQL Restore, розуміюча і формат, створений командою BACKUP DATABASE. Адміну необхідно лише вказати архів і сервер, на який відновити дані, і натиснути одну кнопку. Але в більш складних сценаріях доведеться використовувати RESTORE.

Особливості бекапа MS SQL Server

Створення резервної копії та відновлення СУБД має свої відмінності, які потрібно враховувати, особливо їх багато при перенесенні архіву на інший сервер. Для прикладу розберемо деякі нюанси MS SQL Server. Для архівування за допомогою Transact-SQL слід використовувати команду BACKUP DATABASE (є і різницева DIFFERENTIAL) і журнал транзакцій BACKUP LOG.

Якщо резервна копія розгортається на іншому сервері, потрібно переконатися, що присутні ті ж самі логічні диски. Як варіант - можна вручну прописати правильні шляхи для файлів бази даних, використовуючи опцію WITH MOVE команди RESTORE DATABASE.

Проста ситуація - бекап і перенесення баз на інші версії SQL Server. Ця послуга підтримується, але в разі SQL Server буде працювати, якщо версія сервера, на якій розгортається копія, така ж або новіше, ніж та, на якій вона створювалася. Причому є обмеження: новішими не більш ніж на дві версії. Після відновлення БД буде знаходитися в режимі сумісності з тією версією, з якою здійснювався перехід, тобто нові функції будуть недоступні. Це легко поправити, змінивши COMPATIBILITY_LEVEL. Можна це зробити при допомоги GUIабо SQL.

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

Визначити, на якій версії була створена копія, можна, переглянувши заголовок файлу архіву. Щоб не експериментувати, при переході на нову версію SQL Server слід запустити безкоштовну утиліту Microsoft Upgrade Advisor.

Iperius

Ліцензія:комерційна, є версія Free

Підтримувані СУБД: Oracle 9-11, XE, MySQL, MariaDB, PostgreSQL і MS SQL Server

Коли доводиться керувати кількома типами СУБД, без комбайнів не обійтися. Вибір великий. Наприклад, Iperius - легка, дуже проста у використанні і одночасна потужна програма для резервного копіювання файлів, що має функцію гарячого резервування баз даних без переривання в роботі або блокування. Забезпечує повний або інкрементальний бекап. Може створювати повні образи дисків для автоматичної переустановки всієї системи. Підтримує резервне копіювання на NAS, USB-пристрої, стример, FTP / FTPS, Google Drive, Dropbox і SkyDrive. Підтримує стиснення zip без обмеження в розмірі файлів і AES256-шифрування, запуск зовнішніх скриптів і програм. Включає досить функціональний планувальник завдань, можливо паралельне або послідовне виконання декількох завдань, результат відправляється на email. Підтримуються численні фільтри, змінні для персоналізації шляхів і налаштувань.

Можливість закачування по FTP дозволяє легко оновлювати інформацію на декількох веб-сайтах. відкриті файлирезервуються за допомогою технології VSS (тіньового копіювання томів), що дозволяє виробляти гарячий бекап не тільки файлів СУБД, але і інших додатків. Для Oracle також задіюється засіб організації резервного копіювання і відновлення даних RMAN (Recovery Manager). Щоб не перевантажувати канал, є можливість налаштування смуги пропускання. Управління резервуванням і відновленням проводиться за допомогою локальної та веб-консолі. Всі функції на увазі, тому для налаштування завдання буде потрібно лише розуміння процесу, в документацію заглядати навіть не доведеться. Просто слідуємо підказкам майстра. Також можна відзначити менеджер облікових записів, що дуже зручно при великій кількості систем.

Базові функції пропонуються безкоштовно, але можливість резервування БД закладена тільки в версіях Advanced DB і Full. Підтримується установка від XP до Windows Server 2012.

Handy Backup

Ліцензія:комерційна

Підтримувані СУБД: Oracle, MySQL, IBM DB2 (7-9.5) і MS SQL Server

Одна з найпотужніших систем керування базами даних - IBM DB2, що має унікальні функції по масштабування і підтримуюча безліч платформ. Поставляється в декількох редакціях, які побудовані на одній базі і відрізняються функціонально. Архітектура баз даних DB2 дозволяє управляти практично всіма типами даних: документами, XML, медіафайламі і так далі. Особливо популярна безкоштовна DB2 Express-C. Бекап дуже простий:

Db2 backup db sample

Або снапшот, що використовує функцію Advanced Copy Services (ACS):

Db2 backup db sample use snapshot

Але потрібно пам'ятати, що в разі знімків ми не можемо відновлювати (db2 recover db) окремі таблиці. Є і можливості з автоматичного бекапа, і багато іншого. Продукти добре задокументовані, хоча в російськомовному інтернеті керівництва зустрічаються рідко. Також далеко не у всіх спеціальних рішеннях можна знайти підтримку DB2.

Наприклад, Handy Backup дозволяє виконувати бекап декількох типів серверів баз даних і зберігати файли практично на будь-який носій ( жорсткий диск, CD / DVD, хмарне та мережеве сховище, FTP / S, WebDAV і інші). Можливий бекап баз даних через ODBC (тільки таблиці). Це одне з небагатьох рішень, які підтримують DB2, і до того ж має логотип «Ready for IBM DB2 Data Server Software». Вся процедура виконується за допомогою звичайного майстра, в якому необхідно лише вибрати потрібний пункт і сформувати завдання. Сам процес налаштування настільки простий, що розібратися зможе і новачок. Можна створити кілька завдань, які будуть запускатися за розкладом. Результат фіксується в журналі і відправляється по email. Під час роботи завдання зупинка сервісу не потрібна. Архів автоматично стискається і шифрується, що гарантує його безпеку.

Роботу з DB2 підтримують дві версії Handy Backup - Office Expert (локальний) і Server Network (мережевий). Працює на комп'ютерах під управлінням Win8 / 7 / Vista / XP або 2012/2008/2003. Сам процес розгортання нескладний для будь-якого адміністратора.

Розглянемо, як організувати дві найбільш часто зустрічаються завдання адміністрування 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).

Існує кілька способів копіювання таблиці в базі даних MS SQL Server. Пропоную кілька варіантів створення копії таблиць. Який з них вибрати - залежить від структури таблиці, наявності в ній індексів, тригерів і т.п., а також бажання робити щось руками.

1. Ручний метод копіювання структури таблиці

У Micrisoft SQL Management Studio вибрати базу, вибрати таблицю, натиснути правою кнопкою миші і вибрати пункти "Script Table as" -> "CREATE TO" -> "New Query Editor Window". У вікні запиту відкриється код для створення таблиці. У ньому потрібно вказати ім'я бази, в якій потрібно зробити копію таблиці, і нове ім'я, якщо база не змінюється. Як створити код для створення структури, що є таблиці, показано на малюнку нижче.

За допомогою цього способу будуть створені індекси таблиці, але не скопійовано тригери. Їх потрібно копіювати аналогічним способом.

Для копіювання даних в уже створену таблицю потрібно використовувати такий SQL запит:

INSERT into ..tmp_tbl_Deps SELECT * FROM ..tbl_Deps

2. Копіювання SQL таблиці запитом в одну строчку

Зробити копію структури таблицю і даних всередині однієї бази:

SELECT * into tmp_tbl_Dep FROM tbl_Deps

Скопіювати структури таблицю і її дані з однієї бази в іншу:

SELECT * into ..tmp_tbl_Deps FROM ..tbl_Deps

Мінус у такого рішення - чи не копіюються індекси.

Незважаючи на те, що в наших попередніх матеріалах ми вже торкалися питання резервного копіювання баз 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 години.