Розбираємось з утилітами для бекапу баз даних. Налаштування регулярного резервного копіювання БД MS SQL Server Sql server резервна копія написати скрипт

Відновимо базу "Test _Recovery" у стан " t 4».

Приступимо до відновлення бази даних із повної резервної копії «Full2_Test_Recovery.bak» використовуючи SQL Server Management Studio ». Клацаємо правою кнопкою миші на базі « Test _ Recovery », в меню вибираємо « Tasks», далі «Restore», потім «Database».

У вікні « Restore Database» у розділі «Sourse» вибираємо «Device». Далі « Add », прописуємо шлях «\\ vniz-tst-bkp 01. test. local \ Backup _ SQL \ Full 2_ Test _ Recovery. bak», натискаємо «Ok». У розділі «Destination» вибираємо Database Test Recovery

Натискаємо «Ok»

База успішно відновиться.

Розглянемо відновлення бази даних, використовуючи Transact-SQL.

Клацаємо правою кнопкою миші по базі Test_Recovery, в меню вибираємо New Query:

У вікні вводимо:

USE master

RESTORE DATABASE Test_Recovery

FROM DISK = "\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak"

WITH REPLACE

База успішно відновиться.

У цьому прикладі ми використовували параметр REPLACE:

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

Параметр REPLACE скасовує кілька важливих перевірок, які зазвичай виконуються операцією відновлення. Скасовуються такі перевірки.

Перевірка на відновлення поверх існуючої бази даних резервної копії, створеної для іншої бази даних.При використанні параметра REPLACE для відновлення можна записати дані поверх існуючої бази даних незалежно від того, які бази даних містяться в резервному наборі даних, навіть якщо зазначене ім'я даних відрізняється від записаного в резервному наборі. Це може призвести до випадкового перезапису поверх бази даних іншої бази даних.
Перевірка відновлення бази даних, що використовує модель повного відновлення або модель відновлення з неповним протоколюванням, для якої не була створена резервна копія заключного фрагмента журналу, і не був застосований параметр STOPAT.При використанні параметра REPLACE можлива втрата зафіксованих даних, оскільки останні записані до журналу дані ще не були скопійовані в резервну копію.
Перезапис наявних файлів.

Великий функціонал Bacula Enterprise Edition, крім іншого, дозволяє швидко і просто створювати бекапи БД під . Наприклад, йдеться про інструмент, за допомогою якого можна здійснювати резервне копіювання MS SQL Server. Зробити бекап MS SQL користувач може, створюючи резервні копії специфічних баз даних MS SQL великих обсягів, що використовуються платформою Windows, при менших витратах на ПЗ сторонніх виробників, з можливістю відновлення даних до певного моменту часу (PITR-відновлення) на мережевий та локальний диск.

Скрипт Bacula Systems для створення бекапів MS SQL Server характеризується надзвичайною ефективністю, що досягається за рахунок реалізації сучасної, високонадійної архітектури. Більш того, ПЗ дозволяє зробити бекап MS SQL Server, використовувати найрізноманітніші можливості створення резервних копій MS SQL.

Скрипт бекапу MS SQL Bacula Systems працює незалежно від VSS. Це означає, що інструмент резервного копіювання MS SQL не використовує VSS снапшоти для створення бекапів. Тому користувач може встановити наступне значення “Enable VSS = no” в Bacula FileSet. Ефективне створення бекапів MS SQL Server та їх відновлення за допомогою цього рішення досягаються за рахунок використання Microsoft API для SQL Server. Завдяки цьому Bacula Systems може підтримувати роботу механізмів забезпечення захисту та всі типи автентичності, реалізовані в Microsoft SQL Server.

Резервне копіювання журналу транзакцій MS SQL та відновлення MS SQL на момент часу: ПЗ Bacula Enterprise Edition дозволяє відновлювати блоки даних MS SQL або конкретні налаштування до певного моменту часу. Завдяки реалізації моделей повного відновлення та відновлення з неповним протоколюванням ви зможете відновлювати MS SQL, використовуючи PITR-відновлення або використовувати LSN для відновлення системи до конкретного стану. Ви можете відновлювати певний стан бази даних MS SQL на будь-який момент часу з точністю до секунди. У разі бекапу журналу транзакцій MS SQL, при відновленні стан БД відновлюватиметься з різних вибраних бекапів.

Короткий огляд функційавтоматичного бекапу та відновлення MS SQL з Bacula Enterprise

Компанія Bacula Systems створила плагін для резервного копіювання MS SQL Server для спільного використання Bacula Enterprise Edition. Бекап MS SQL Server з Bacula має такі функції:

Підтримка повного та диференціального резервного копіювання MS SQL
Підтримка інкрементального резервного копіювання MS SQL
Резервне копіювання MS SQL на мережевий та локальний диск
Резервне копіювання MS SQL за розкладом
Створення бекапів на рівні бази даних MS SQL Server
Можливість включати/виключати БД із процедури створення бекапів
Підтримка створення бекапів БД «тільки для читання»
Відновлення MS SQL бекапів на диск
Надсилання потоку резервної копії безпосередньо в Storage Daemon
Відновлення MS SQL на момент часу

Огляд та налаштування резервного копіювання MS SQL 2008, 2008 R2, 2012 та 2014

У цьому документі представлені рішення для Bacula Enterprise Edition 8.4 та пізніших версій, які не підтримуються ранніми версіями ПЗ. Резервне копіювання бази MS SQL було протестовано та підтримується MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. Можлива робота резервного копіювання MS SQL від Bacula з SQL Express.

Глосарій резервного копіювання MS SQL 2008, 2008 R2, 2012 та 2014

MS SQLозначає Microsoft SQL Server.
Журнал транзакцій (transaction log).Будь-яка база даних MS SQL Server має журнал транзакцій, до якого записуються всі транзакції та модифікації БД, виконані під час таких транзакцій. Журнал транзакцій – важливий елемент БД. У разі відмови системи журнал транзакцій може знадобитися для відновлення БД до робочого стану. Детальнішу інформацію ви знайдете за посиланням https://msdn.microsoft.com/en-us/library/ms190925.aspx.
Диференційне резервне копіювання бази даних MS SQL Server.Диференціальний бекап заснований на останньому повному. У ході виконання диференціального бекапу захоплюються ті дані, які були змінені з моменту створення останнього повного бекапу. Детальнішу інформацію ви знайдете за посиланням https://msdn.microsoft.com/en-us/library/ms175526.aspx.
Повне резервне копіювання бази даних MS SQL Server.У результаті повного бекапу БД створюється резервна копія всієї бази даних. Бекап включає частину журналу транзакцій з метою відновлення повної БД із резервної копії. Повні бекапи БД містять БД на момент завершення створення резервної копії. Детальнішу інформацію ви знайдете за посиланням https://msdn.microsoft.com/en-us/library/ms186289.aspx.
Бекап "тільки для копіювання" (CopyOnly).Бекапи «лише копіювання» є бекапи MS SQL, незалежні від звичайної послідовності створення традиційних резервних копій SQL Server. Іноді корисно створювати бекапи для особливих потреб, не впливаючи на загальний процес резервного копіювання та відновлення БД. Детальнішу інформацію ви знайдете за посиланням https://msdn.microsoft.com/en-us/library/ms191495.aspx.
VDI(Інтерфейс віртуального пристрою) – це технологія Microsoft, що дозволяє створювати іменований каналміж програмами.
стандартні маски задають набори рядків із підстановними знаками. Наприклад, стандартна маска production* включатиме рядки production1 і production2.
рядок
ціле число.
LSNКожен запис у журналі транзакцій MS SQL Server позначається за допомогою унікального реєстраційного номера транзакції (LSN). Більш детальну інформацію ви знайдете за посиланням https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.

Резервне копіювання MS SQL Server 2008, 2008 R2, 2012 та 2014

Повне резервне копіювання баз даних MS SQL Server 2008, 2008 R2, 2012 та 2014

У результаті повного резервного копіювання бази даних MS SQL зберігаються файли БД і журнал транзакцій, що дозволяє повністю захистити базу MS SQL у разі відмови носія. У разі пошкодження одного або більше файлів відновлення бази MS SQL з бекапу дозволить відновити всі транзакції. Також буде здійснено відкат усіх транзакцій, що перебували в процесі виконання. У цьому режимі виробляється створення бекапів БД master і mbdb.

Диференційне резервне копіювання баз даних MS SQL Server 2008, 2008 R2, 2012 та 2014

Диференціальний бекап бази MS SQL Server заснований на останньому повному бекапі бази даних MS SQL. У ході створення диференціального бекапу MS SQL захоплюються ті дані, які були змінені з моменту створення останнього повного бекапу MS SQL. Для функції диференціального бекапу MS SQL дуже важлива послідовність бекапів. Якщо з якоїсь причини повний бекап, на який посилається MS SQL, не доступний, диференціальні бекапи бази даних MS SQL Server не можна використовувати. Резервне копіювання MS SQL від Bacula використовує певні методи вирішення цієї проблеми. Тому, у разі виникнення складнощів, статус диференціального бекапу БД може бути автоматично підвищено до повного бекапу.

Резервне копіювання журналу транзакцій MS SQL 2008, 2008 R2, 2012 та 2014

Налаштування резервного копіювання MS SQL та конфігурування БД

Відновлення бази MS SQL з бекапу

Ви можете використовувати всі стандартні методи запуску процедури відновлення бази MS SQL з бекапу. Однак ви повинні переконатися в тому, що у разі відновлення диференціальних даних буде також відновлено повний попередній бекап бази MS SQL. У такому разі відновлення відбувається автоматично, якщо ви запускаєте його в консолі bconsoleза допомогою варіантів відновлення 5 або 12. У згенерованій файловій структурі необхідно відзначити відновлення повних БД або інстансів БД.

Варіанти відновлення бази MS SQL із бекапу

ПЗ Bacula Enterprise Edition дозволяє користувачам використовувати безліч варіантів відновлення MS SQL і застосовувати різні способи «відкату» БД. Варіанти відновлення, що найчастіше використовуються, описані нижче:

параметр Where: У випадку Bacula Enterprise Edition, цей параметр дозволяє адміністратору відновлювати БД в конкретному місці.
параметр Replace: Використовується для того, щоб визначити, як Bacula має вести себе з поточної БД при відновленні. Резервне копіювання MS SQL від Bacula також дозволяє використовувати ще кілька опцій при відновленні, наприклад:
Instance: Оскільки MS SQL використовує кілька інстансів, бекап бази MS SQL від Bacula дозволяє вибирати, який інстанс слід відновлювати. Цей параметр є опціональним, і, якщо він не заданий, при відновленні використовуватиметься значення, задане при створенні бекапу. За промовчанням використовується інстанс з ім'ям “MSSQLSERVER”.
Database. Ця опція вказує ім'я БД для відновлення і використовує значення, задане в момент створення БД. Цей параметр є опціональним. За замовчуванням резервне копіювання баз даних SQL Server використовує параметр Where для визначення імені нової бази даних. Якщо обидва параметри Where і Database призначені валідне ім'я БД, параметр Database буде використовуватися.
User. Ім'я користувача, яке використовується для підключення до інстансу бази даних MS SQL. Цей параметр є опціональним, і, якщо він не заданий, при відновленні використовуватиметься значення, задане при створенні бекапу.
Password. Пароль, який використовується для підключення до інстансу бази даних MS SQL. Цей параметр є опціональним, і, якщо він не заданий, при відновленні використовуватиметься значення, задане при створенні бекапу.
Domain. Домен, який використовується для підключення до інстансу бази даних MS SQL. Цей параметр є опціональним, і, якщо він не заданий, при відновленні використовуватиметься значення, задане при створенні бекапу.
Recovery. Параметр дозволяє визначити, чи буде відкат БД до попереднього стану при відновленні чи ні. За замовчуванням при відновленні БД здійснить відкат до попереднього стану.
Stop_before_mark Умова WITH STOPBEFOREMARK = Використовується для того, щоб вказати, що запис у журналі транзакцій, який знаходиться безпосередньо перед прапором, є точкою відновлення. Точкою відновлення може бути дата і час, номер LSN або ім'я mark_name.
Stop_at_mark. Умова WITH STOPATMARK = Використовується для того, щоб показати, що зазначена транзакція є точкою відновлення. STOPATMARK переміщується вперед до прапора та включає повтор поміченої транзакції. Точкою відновлення може бути дата і час, номер LSN або ім'я mark_name.
Stop_at= . Умова WITH STOPAT = використовується для того, щоб вказати, що точкою відновлення є дата/час.
Restrict_user. Умова WITH RESTRICT_USER використовується для обмеження доступу до відновленої бази даних. За промовчанням використовується значення no.

Відновлення MS SQL на даний момент часу можна здійснювати безпосередньо з плагіна бекапу MS SQL. Також можна відновлювати файли локально та виконувати операції з консолі керування Microsoft SQL Server Mangement Console, щоб мати можливість використовувати більше можливостей.

LSN

LSN номер запису в журналі, в момент якої виникла конкретна подія щодо створення бекапу та відновлення, можна переглянути одним із наступних способів:

При виведенні опису завдань створення бекапу за допомогою ПЗ Bacula
У назві файлу журналу
У таблиці msdb.backupset
У таблиці msdb.backupfile

При виконанні завдання зі створення бекапу бази MS SQL при виведенні опису завдання з'явиться наступна інформація про номери LSN:

Номер First LSNвідповідає останньому LSN номеру останнього бекапу журналу транзакцій. Таким бекапом може бути перший повний бекап або останній бекап (інкрементальний).

Номер Last LSNвідповідає останній транзакції, зареєстрованої в журналі.

У випадку бекапу журналу транзакцій (інкрементального), назва файлу, пов'язаного з цією БД, в завданні створення інкрементального бекапу буде виглядати наступним чином:

Число в назві, у нашому випадку 42000162001, відповідає останньому LSN номеру попереднього завдання (щодо створення повного або інкрементального бекапу).

Рисунок 2: Перший номер LSN, останній номер LSN та номери LSN у назві файлів

Як показано у прикладі на малюнку 2, якщо адміністратору необхідно відновити базу даних MS SQL у стан, що відповідає LSN номеру 14, можна виконати такі дії:

У меню відновлення БД використовуйте опцію 5
Виберіть останній файл повного бекапу “data.bak” (LSN: 10)
Виберіть інкрементальний бекап “log-10.trn”

Або, якщо останній повний бекап MS SQL Server не доступний, однак доступний попередній повний бекап, то:

Використовуйте опцію відновлення 3, оберіть відповідні значення jobids
Виберіть директорію БД “/@mssql/db29187”
Виберіть файл повного бекапу “data.bak” (LSN: 2)
Виберіть інкрементальні бекапи "log-2.trn", "log-3.trn", "log-10.trn"
Задайте параметр stop_at_mark рівний “lsn:14”
Запустіть завдання відновлення бекапу

Сценарії відновлення MS SQL

Опис	Where	Database	Приклад
Відновити файли на диск	Шлях		where=c:/tmp
Відновити вихідну БД			where=/
Відновити з новим ім'ям	Ім'я		where=newdb
Відновити з новим ім'ям		Ім'я	database=newdb
Відновити з новим ім'ям та перемістити файли		Ім'я

Таблиця 1: Сценарії відновлення MS SQL

2.3.1 Відновлення бази MS SQL із вихідним ім'ям

Щоб відновити базу даних з вихідним ім'ям, параметр Whereповинен бути не заданий (порожнє значення), або має бути задане значення “/”, а параметру Replaceмає бути надано значення Always, або спочатку необхідно видалити вихідну БД.

Відновлення бекапу MS SQL з новим ім'ям

Щоб відновити бекап бази даних MS SQL з новим ім'ям, можливо, спочатку потрібно буде перемістити файли БД на диск. Все залежить від того, чи є ще вихідна БД.

Якщо вихідна БД не доступна, то параметр where, або поле “Plugin Options” може містити назву нової бази даних. Резервне копіювання MS SQL від Bacula автоматично створить БД із новим ім'ям.

Якщо вихідна БД все ще потрібна, параметр where буде використовуватися для переміщення файлів на диск, і необхідно буде задати назву нової БД за допомогою меню “Plugin Options”. У дереві відновлення потрібно вибрати файл layout.dat.

Використовуючи каталог My Catalogue

Запустіть завдання відновлення MS SQL:

Використовуючи каталог My Catalogue, запустіть завдання відновлення бази MS SQL:

Відновлення MS SQL на локальний диск

Якщо вказати where=c:/path/, файли будуть відновлені на локальний диск, і адміністратор бази даних MS SQL зможе використовувати процедурне розширення TSQL для консолі керування Microsoft SQL Server Management Console для відновлення БД. Команди SQL, необхідні відновлення БД, перераховані в описі Job outputяк показано малюнку нижче.

2. Перегляд інформації про події резервного копіювання та відновлення для бази даних

Щоб дізнатися, коли здійснювалося створення резервних копій конкретної бази даних, а також відновлення бази даних із резервної копії, можна скористатися стандартним звітом « » (Backup and Restore Events). Для формування даного звіту необхідно в браузері об'єктів (Server Oblects) клікнути правою кнопкою миші по відповідній базі даних, у контекстному меню вибрати « Звіти» (Reports) - « Стандартний звіт» (Standart Reports) - « Події резервного копіювання та відновлення» (Backup and Restore Events).

Сформований звіт містить такі дані:

Середній час, що витрачається на операції резервного копіювання (Average Time Taken For Backup Operations)
Успішні операції резервного копіювання (Saccessful Backup Operations)
Помилки операції резервного копіювання (Backup Operation Errors)
Успішні операції відновлення (Saccessful Restore Operations)

Для перегляду цієї інформації необхідно розкрити відповідне угруповання у звіті.

Чи допомогла Вам ця стаття?

Існує кілька способів копіювання таблиці у базі даних 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 години.