У цій статті ми розглянемо, як налаштувати резервне копіювання баз даних в Microsoft SQL Server, покажемо, як відновити базу даних з резервної копії за допомогою SQL Server Management Studio та Transact-SQL. Перша частина статті присвячена теоретичним аспектам резервного копіювання в SQL, у другій на прикладі ми покажемо, як налаштувати регулярне резервне копіювання бази даних MS SQL за допомогою плану обслуговування і відновити базу з резервної копії на прикладі встановленого Microsoft SQL Server 2019.

Вимоги до плану резервного копіювання баз даних SQL Server встановлює бізнес, з огляду на кілька критеріїв:

  • Допустимий обсяг втрачених даних (за останній день / годину / хвилину / секунду);
  • Вимоги до дискового простору і його вартість;
  • Витрати ресурсів сервера на резервне копіювання.

Слід розуміти, що за допомогою механізмів резервного копіювання неможливо домогтися резервування даних в реальному часі. Для цієї мети використовуються інші технології високої доступності SQL Server – групи доступності Always On, віддзеркалення баз даних або реплікація.

Типи резервного копіювання SQL Server

Повний (Full Backup)

Повне резервне копіювання робить копію всієї бази даних, включаючи всі об’єкти і дані системних таблиць. Повна резервна копія НЕ буде усікати (truncate) журнал транзакцій. Це основний тип резервних копій, який потрібно виконувати перед іншими типами резервних копій.

Повну резервну копію ви можете відновити за 1 крок, тому що вона не вимагає інших диференціальних / інкрементальних копій.

Якщо модель відновлення бази SQL даних встановлена ​​як “Повна”, то при відновленні бекапу ви можете вказати параметр “СТОП“, Де вказується час (до секунди) на якому потрібно зупинити відновлення даних. Наприклад, співробітник вніс некоректні дані в 14:46:07, за допомогою параметра STOPAT ви можете відновити дані на момент 14:46:06

диференціальне

Диференціальне або різницеве ​​резервне копіювання – це копіювання тільки тих даних, які з’явилися з моменту останньої повної резервної копії.

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

Зазвичай при використанні разностного резервного копіювання використовують план по типу “повне раз в N днів, диференціальне кожні N годин”. Якщо щоденний оборот даних досить високий, то даний тип резервних копій може бути незручний в застосуванні, так як копії будуть важити досить багато.

Наприклад, якщо повна резервна копія важить 300 GB, а диференціальна через годину роботи 5 GB, то через добу це буде 120 GB, що робить використання даного типу копій нераціональним.

Журнал транзакцій

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

Відновлюючи журнал транзакцій, ви також можете вказати параметр STOPAT, як і у відновленні повної резервної копії.

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

Хвіст-Журнал

Цей вид резервного копіювання виділяють як окремий, але фактично це звичайна резервна копія журналу транзакцій з NORECOVERY опцією.

Tail-Log бекап рекомендується робити перед відновленням копій журналу транзакцій, щоб не втратити транзакції між останнім бекап і поточним моментом часу.

Лише копіювання

Цей вид бекапу не може служити “базою” для диференціальних резервних копій і для копій журналу транзакцій. Copy-only бекап чи не порушує поточну ланцюжок резервних копій (полний-> диференційний або повний -> копії журналів транзакцій) і використовується тільки в тому випадку, якщо вам потрібно зняти повну резервну копію, не зачіпаючи поточну ланцюжок бекапів.

За винятком цих нюансів – нічим не відрізняється від звичайної повної копії.

Часткова резервна копія

Partial backup цей тип резервної копії використовується для того, щоб зняти копії з read-only файлових груп. На практиці використовується рідко.

Резервне копіювання файлів і файлових груп

Використовується для зняття резервних копій певних файлів або файлових груп.

Моделі відновлення бази даних SQL Server

Модель відновлення – це параметр бази даних SQL Server, який відповідає за реєстрацію транзакцій в журналі транзакцій. Всього існує три моделі відновлення:

Проста модель відновлення

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

У разі аварії, дані можуть бути відновлені тільки на момент зняття резервної копії.

При використанні цієї моделі відновлення, наступний функціонал SQL Server недоступний:

  • Доставка журналів транзакцій
  • Завжди
  • Point-In-Time відновлення
  • Резервні копії журналу транзакцій

Повна модель відновлення

Повна модель відновлення зберігає всі транзакції в журналі транзакцій до усічення журналу (за допомогою зняття резервної копії журналу).

Це сама “надійна” модель відновлення, при аварійному збої можна ви зможете відновити всі транзакції, крім тих, які не встигли завершитися на підводному човні.

Ця модель потребує обслуговування журналів транзакцій (регулярні резервні копії), інакше журнали займуть все дисковий простір.

Відновлення з неповним протоколированием (bulk logged)

Ця модель, також, як і повна, записує всі транзакції в журнал транзакцій, за винятком таких операцій як:

  • ВИБЕРІТЬ ДО
  • ВСТАВИТИ ГРУПУ і BCP
  • ВСТАВИТИ У ВИБІР
  • Операції з індексами (CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX)

В іншому ця модель працює аналогічно повної моделі відновлення.

Налаштування резервного копіювання SQL Server за допомогою плану обслуговування

Плани обслуговування SQL Server це найпоширеніший спосіб настройки регулярного резервного копіювання.

Розглянемо налаштування резервного бази даних на SQL Server копіювання за планом:

  • Повна резервна копія кожні 24 години
  • Копія журналу транзакцій – кожні 30 хвилин

У SSMS (SQL Server Management Studio) перейдіть в розділ Management -> Maintenance Planes і запустіть -> майстер створення плану обслуговування (Maintenance Plan Wizard).

створити план обслуговування для резервних копіювання slq server

Вкажіть ім’я плану і виберіть режим “Separate schedules for each task”.

план резервних копіювання slq server

Виберіть операції, які потрібно зробити в цьому плані обслуговування:

  • Резервна база даних (повна)
  • Резервне копіювання бази даних (журнал транзакцій)

резервних копіювання бази даних і транзакційного балки sql server

Використовуйте наступну послідовність операцій:

послідовність резервних копіювання в плані обслуговування sql

Виберіть базу даних SQL Server, яку потрібно бекапіть і виберіть розклад.

вибору бази даних mssql для резервних копіювання

розклад резервних копіювання бд mssql

Вкажіть шлях до каталогу, в який потрібно зберігати резервні копії ваше бази даних.

каталог для резервних копій

Вкажіть скільки будуть зберігатися резервні копії (наприклад, 14 днів).

глибина зберігання резервних копій sql server

Натисніть Next і аналогічно створіть розклад резервного копіювання для журналу транзакцій.

настройка резервних копіювання журналу транзакцій microsoft sql server

Опціонально можна вказати файл для ведення логу плану обслуговування.

журнал резервного копіювання сервера

Завершення настройки плану обслуговування SQL Server.

план обслуговування sql server створений

Виконайте план обслуговування вручну і перевірте журнал.

запуску плану обслуговування, перевірка резервних копіювання mssql

дата останнього резервних копіювання sql server

Як ви бачите була створена повна резервна копія бази даних SQL Server і слідом копія журналу транзакцій. На цьому настройка резервного копіювання закінчена.

Відновлення бази даних SQL Server з резервної копії

Тепер розглянемо, як відновити бази даних SQL Server з резервної копії. Для відновлення бази можна використовувати графічну консоль SQL Server Management Studio або мову T-SQL.

Відновлення резервної копії за допомогою SQL Server Management Studio

Запустіть SSMS, клацніть по розділу Database і виберіть пункт Restore Database.

відновлення бази даних в SQL server

Виберіть базу даних. У вікні з’явиться список резервних копій, зареєстрованих в SQL Server для цієї бази даних.

вибору бази даних sql для відновлення

Для прикладу, скористаємося Point-In-Time відновленням і виберемо момент, на який ми хочемо відновити базу даних. Натисніть Timeline.

вибір дати створення резервних копії

Виберіть опцію “Закрийте існуючі підключення до цільової бази даних“, Якщо ваша база даних знаходиться в статус Online

параметри відновлення бази даних sql server з резервних копії

Натисніть ОК. Після цього база даних відновиться на обраний момент часу.

Відновлення бази даних MS SQL Server за допомогою T-SQL

Розглянемо невеликий Transact-SQL скрипт, який виконує ту ж послідовність дії для відновлення бази даних, що і майстер (скрипт був згенерований майстром з прикладу вище).

USE [master]
ALTER DATABASE [TestDatabase2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [TestDatabase2] TO DISK = N'E:MSSQL15.NODE2MSSQLBackupTestDatabase2_LogBackup_2020-02-17_15-39-43.bak' WITH NOFORMAT, NOINIT, NAME = N'TestDatabase2_LogBackup_2020-02-17_15-39-43', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackupfull.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackuptrans.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TestDatabase2] FROM DISK = N'E:MSSQL15.NODE2MSSQLBackuptrans.bak' WITH FILE = 2, NOUNLOAD, STATS = 5, STOPAT = N'2020-02-17T15:38:23'
ALTER DATABASE [TestDatabase2] SET MULTI_USER
GO

В даному випадку база даних переводиться в ОДИНОЧНИЙ КОРИСТУВАЧ, Але потрібно бути акуратним з цим параметром, так як в деяких ситуаціях ви можете закрити собі доступ, якщо хтось відкриє сесію раніше вас.

Далі виконується tail-log бекап, потім відновлюється повний бекап і слідом відновлюються бекапи журналу транзакцій. Зверніть увагу на параметр STOPAT, база даних відновитися на момент 15:38:23

  • Резервні копії не повинні зберігатися на тому ж диску, що і ваш SQL Server. Це правило стосується будь-яких резервних копій. При виході з ладу основного дискового масиву ви повинні мати доступ до ваших резервних копій. Якщо дозволяють ресурси, краще зберігати резервні копії відразу на декількох розрізнених масивах.
  • Процес резервного копіювання повинен мінімально впливати на роботу користувачів. Повні резервні копії краще робити тоді, коли для користувача активність на сервері мінімальна.
  • Регулярно перевіряйте цілісність резервних копій і проводите тестові відновлення. Ви завжди повинні бути впевнені, що ваші бекапи валідність і готові до відновлення в будь-який час.
  • Заздалегідь розрахуйте час, необхідний для повного відновлення при аварії. Часто в базах зберігається критично важлива для бізнесу інформація, тому ваш керівник повинен знати мінімальний час, який буде потрібно для відновлення після аварії. Якщо навіть вас про це не питають, краще заздалегідь повідомити про це, щоб в разі аварії не виникло непорозуміння.

Leave a Reply

Your email address will not be published. Required fields are marked *