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

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

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

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

Зміст:

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

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

Повна (повна резервна копія)

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

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

Якщо модель бази даних SQL встановлена як "повна", то при відновленні резервної копії ви можете вказати параметр "Зупинка", Де вказується час (до секунди), на який потрібно зупинити відновлення даних. Наприклад, працівник ввів неправильні дані о 14:46:07, використовуючи параметр STEPAT, ви можете відновити дані під час 14:46:06

Різний

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

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

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

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

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

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

Відносивши журнал транзакцій, ви також можете вказати параметр STEPAT, як у відновленні повного резервного копіювання.

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

Хвостовий

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

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

Копіювання лише

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

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

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

Часткове резервне копіювання Цей тип резервної копії використовується для видалення копії з груп файлів лише для читання. На практиці він рідко використовується.

Резервні файли та групи файлів

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

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

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

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

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

У разі аварії дані можна відновити лише під час видалення резервної копії.

Використовуючи цю модель відновлення, наступна функція SQL Server недоступна:

Доставка журналів транзакцій
Завжди
Відновлення вчасно
Резервні копії журналу транзакцій

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

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

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

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

Основна реєстрація відновлення

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

Вибрати
Об'ємна вставка та BCP
Вставте в вибір
Індекс -хірургія (створити індекс, переробити індекс змін, індекс краплі)

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

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

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

Розглянемо конфігурацію бази резервів на SQL Server, копіюючись відповідно до плану:

Повна резервна копія кожні 24 години
Копія журналу Transaction - кожні 30 хвилин

У SSMS (SQL Server Management Studio) Перейдіть до Управління -> Розділ літаків технічного обслуговування та запустіть -Master створення майстра обслуговування).

Вкажіть назву плану та виберіть режим "Окремі графіки для кожного завдання".

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

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

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

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

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

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

Клацніть Далі та аналогічно створіть графік резервного копіювання журналів транзакцій.

За бажанням ви можете вказати файл для переходу плану обслуговування.

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

Слідкуйте за планом обслуговування вручну та перевірте журнал.

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

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

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

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

Запустіть SSMS, натисніть на розділ бази даних та виберіть елемент бази даних відновлення.

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

Наприклад, ми будемо використовувати відновлення в часі та виберемо момент, до якого ми хочемо відновити базу даних. Клацніть Часу шкали.

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

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

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

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

Використовуйте [master] Змінити базу даних [TestDatabase2] встановити SELE_USER з відкатанням негайним Журнал резервного копіювання [testDatabase2] до диска = n'e: \ mssql15.Node2 \ mssql \ backup \ testdatabase2_logbackup_2020-02-17_15-39-43.Bak 'з noformat, noinit, name = n'testdatabase2_logbackup_2020-02-17_15-39-43', noskip, norewind, nounload, norecovery, статистика = 5 Відновити базу даних [TestDatabase2] від DISK = n'e: \ mssql15.Node2 \ mssql \ backup \ full.Bak 'з файлом = 1, norecovery, nounload, статистика = 5 Відновити журнал [testDatabase2] від disk = n'e: \ mssql15.Node2 \ mssql \ backup \ trans.Bak 'з файлом = 1, norecovery, nounload, статистика = 5 Відновити журнал [testDatabase2] від disk = n'e: \ mssql15.Node2 \ mssql \ backup \ trans.Bak 'з файлом = 2, nounload, stats = 5, kpitat = n'2020-02-17t15: 38: 23' Змінити базу даних [testDatabase2] setti_user Йти

У цьому випадку база даних передається в SOLE_USER, Але вам потрібно бути акуратним з цим параметром, оскільки в деяких ситуаціях ви можете закрити свій доступ, якщо хтось відкриває сеанс перед вами.

Потім виконується резервна копія хвоста, потім відновлюється повна резервна копія, а резервні копії журналу транзакції відновлюються. Зверніть увагу на параметр STEPAT, база даних відновиться на момент 15:38:23

Рекомендації та найкраща практика для резервного копіювання SQL Server