резервна копія SQL Server база даних з нашим повним посібником на 2025 рік. Покрокові інструкції та найкращі практики для всіх рівнів кваліфікації.
1. Вступ до SQL Server резервна копія
1.1 Що таке SQL Server Резервне копіювання?
SQL Server Резервне копіювання – це процес створення копій файлів бази даних для захисту від втрати даних. Резервне копіювання фіксує стан вашої бази даних у певний момент часу, що дозволяє відновити дані у разі збоїв обладнання, людських помилок або катастроф.
SQL Server за замовчуванням зберігає резервні копії у файлах .bak, які містять усі об'єкти бази даних, включаючи таблиці, збережені процедури, представлення, індекси та журнали транзакцій.
1.2 Чому SQL Server Резервне копіювання є критично важливим
Резервні копії баз даних слугують останньою лінією захисту від втрати даних. Без належного резервного копіювання ваша організація ризикує:
- Постійна втрата даних від збоїв або пошкодження обладнання
- Подовжений час простою під час спроб відновлення
- Зрив у бізнесі та втрата доходів
- Порушення комплаєнс якщо дані неможливо відновити
- Шкода репутації від перебоїв у наданні послуг
регулярний SQL Server Резервні копії забезпечують безперервність бізнесу та відповідають нормативним вимогам щодо захисту даних.
1.3 Поширені сценарії втрати даних
Розуміння того, коли відбувається втрата даних, допомагає розробити ефективну стратегію резервного копіювання:
- Збої обладнання: Збої диска, несправності сервера або збої системи зберігання даних
- Людські помилки: Випадкові видалення, неправильні оновлення або вилучення таблиць
- Проблеми з програмним забезпеченням: Помилки програм, пошкоджені оновлення або системні збої
- Порушення безпеки: Атаки програм-вимагачів, зловмисні видалення або несанкціонований доступ
- Стихійні лиха: Пожежі, повені або відключення електроенергії, що впливають на центри обробки даних
2. Розуміння SQL Server Типи резервного копіювання
SQL Server підтримує кілька типів резервного копіювання, кожен з яких задовольняє різні потреби відновлення та вимоги до сховища.
2.1 Повне резервне копіювання
Повне резервне копіювання створює повну копію всієї бази даних, включаючи всі файли даних та частину журналу транзакцій, необхідні для відновлення.
2.1.1 Коли використовувати повне резервне копіювання
Повні резервні копії ідеально підходять для:
- Встановлення базової лінії для інших типів резервного копіювання
- Малі та середні бази даних, для яких прийнятний час резервного копіювання
- Щотижневі або щомісячні графіки резервного копіювання
- Бази даних з нечастими змінами
2.1.2 Переваги та обмеження повного резервного копіювання
переваги:
- Найпростіший процес відновлення – один файл містить усе
- Автономний та незалежний від інших резервних копій
- Найшвидший час відновлення для повного відновлення бази даних
Обмеження:
- Потрібно значного місця для зберігання
- Довший час резервного копіювання для великих баз даних
- Більше споживання ресурсів під час операцій резервного копіювання
2.2 Диференціальне резервне копіювання
Диференціальне резервне копіювання фіксує лише зміни даних з моменту останнього повного резервного копіювання, що зменшує час резервного копіювання та вимоги до сховища.
2.2.1 Як працює диференціальне резервне копіювання
Диференціальні резервні копії відстежують зміни за допомогою змінених екстентів. Під час відновлення, SQL Server спочатку застосовує останню повну резервну копію, потім застосовує most нещодавня диференціальна резервна копія.
2.2.2 Повне та диференціальне резервне копіювання
| Аспект | Повне резервне копіювання | Диференціальне резервне копіювання |
|---|---|---|
| Розмір | Повна база даних | Тільки зміни з моменту останнього повного резервного копіювання |
| Час резервного копіювання | Найдовше | Швидше, ніж повний |
| Відновити процес | Відновлення одного файлу | Потрібно повний + диференціальний |
| Потрібне зберігання | Most простір | Спочатку менше місця, з часом зростає |
2.3 Резервне копіювання журналу транзакцій
Резервні копії журналів транзакцій фіксують усі транзакції з моменту останнього резервного копіювання журналу, що дозволяє відновлювати дані до певного моменту часу.
2.3.1 Розуміння журналів транзакцій
Журнал транзакцій записує всі зміни у вашій базі даних. Резервні копії журналів скорочують неактивну частину журналу, запобігаючи його нескінченному зростанню та заповненню диска.
2.3.2 Відновлення в певний момент часу
Резервні копії журналів транзакцій дозволяють відновити базу даних до будь-якого конкретного моменту в резервній копії журналу. Це важливо для відновлення після випадкових змін або видалення даних.
Щоб виконати відновлення в певний момент часу, вам потрібно:
- Остання повна резервна копія
- Мost нещодавня диференціальна резервна копія (необов'язково)
- Усі резервні копії журналу транзакцій, починаючи з повної/диференціальної резервної копії та закінчуючи вашою tarотримати час
2.4 Резервне копіювання хвостового журналу
Резервні копії хвостового журналу фіксують записи журналу, які ще не були резервно коповані, запобігаючи втраті даних та зберігаючи цілісний ланцюжок журналів. Перед відновленням SQL Server Щоб зберегти базу даних до останнього моменту часу, необхідно створити резервну копію журналу транзакцій «хвостової частини». Резервна копія журналу «хвостової частини» – це остання резервна копія, яка цікавить вас у плані відновлення бази даних.
Примітка: Не всі сценарії відновлення вимагають резервної копії журналу відновлення. Вона не потрібна, якщо точка відновлення міститься в попередній резервній копії журналу. Резервна копія журналу відновлення також не потрібна, якщо ви переміщуєте або замінюєте (перезаписуєте) базу даних і не потрібно відновлювати її до певного моменту часу після її зміни.ost нещодавня резервна копія.
2.4.1 Коли необхідні резервні копії хвостового журналу
Наведені нижче сценарії описують, коли слід створювати резервну копію журналу обробки даних:
Відновлення онлайн-бази даних: Якщо база даних перебуває в мережі, і ви плануєте виконати операцію відновлення бази даних, почніть зі створення резервної копії журналу. Щоб уникнути помилки для бази даних в мережі, під час резервного копіювання необхідно використовувати параметр WITH NORECOVERY інструкції BACKUP Transact-SQL. SQL Server , що постійно розширюється.
Відновлення бази даних офлайн: Якщо база даних перебуває в автономному режимі і не вдається завантажитисяtarЯкщо вам потрібно відновити базу даних, спочатку створіть резервну копію кінця журналу. Оскільки наразі транзакції не можуть відбуватися, використання параметра WITH NORECOVERY необов'язкове. У цьому сценарії NORECOVERY фактично є тим самим, що й резервна копія журналу транзакцій лише з копіюванням.
Пошкоджена резервна копія бази даних: Якщо база даних пошкоджена, спробуйте створити резервну копію хвостової частини журналу, використовуючи параметр WITH CONTINUE_AFTER_ERROR інструкції BACKUP. У пошкодженій базі даних резервне копіювання хвостової частини журналу може бути успішним, лише якщо файли журналу не пошкоджені, база даних перебуває у стані, що підтримує резервне копіювання хвостової частини журналу, і база даних не містить жодних змін, записаних у груповий журнал. Якщо резервну копію хвостової частини журналу неможливо створити, будь-які транзакції, здійснені після останньої MS SQL Server резервні копії баз даних є lost.
2.4.2 Основні параметри резервного копіювання журналу хвостової частини
З НОРЕКОВЕРІ: Використовуйте WITH NORECOVERY, якщо ви створюєте резервну копію кінця журналу онлайн-бази даних, яку плануєте згодом відновити. NORECOVERY переводить базу даних у автономний режим. Ви також можете створювати резервні копії SQL Server Хвостовий журнал автономної бази даних. Якщо ви хочете залишити базу даних автономною, використовуйте WITH NORECOVERY. Зверніть увагу, що журнал буде скорочено, якщо ви не вкажете параметр COPY_ONLY або NO_TRUNCATE.
З ПРОДОВЖЕННЯМ_ПІСЛЯ_ПОМИЛКИ: Використовуйте CONTINUE_AFTER_ERROR лише якщо ви створюєте резервну копію хвоста пошкодженої бази даних. Під час резервного копіювання хвоста журналу на пошкодженій базі даних деякі метадані, які зазвичай записуються в резервні копії журналів, можуть бути недоступними.
2.5 Резервне копіювання лише для копіювання
Резервні копії лише для копіювання створюють незалежну резервну копію, не впливаючи на звичайну послідовність резервного копіювання. Вони не порушують диференціальні ланцюжки резервного копіювання або безперервність журналу транзакцій.
Використовуйте резервні копії лише для копіювання для:
- Створення тестових або розробницьких копій бази даних
- Спеціальні резервні копії без впливу на заплановані резервні копії
- Резервне копіювання перед серйозними змінами або тестуванням
2.6 Резервне копіювання файлів та файлових груп
Резервні копії файлів та файлових груп tarотримати певні файли бази даних або групи файлів, а не всю базу даних. Цей підхід добре працює для дуже великих баз даних, де резервне копіювання всього займає занадто багато часу.
Переваги включають:
- Швидше резервне копіювання великих баз даних
- Паралельне резервне копіювання кількох файлових груп
- Детальні варіанти відновлення
- Оптимізовані розклади резервного копіювання для файлових груп лише для читання
2.7 Часткове резервне копіювання
Часткові резервні копії включають усі дані в основній файловій групі та будь-яких файлових групах з правами на читання та запис, за винятком файлових груп лише для читання. Це зменшує розмір і час резервного копіювання для баз даних зі статичними історичними даними, що зберігаються у файлових групах лише для читання.
3. SQL Server Моделі відновлення
SQL Server Моделі відновлення визначають, які типи резервних копій доступні та як керуються журнали транзакцій.
3.1 Проста модель відновлення
3.1.1 Характеристики та варіанти використання
Просте відновлення автоматично скорочує журнал транзакцій після кожної контрольної точки, звільняючи місце без необхідності резервного копіювання журналів.
Найкраще для:
- Розробка та тестування баз даних
- Бази даних, де втрата даних між резервними копіями є прийнятною
- Сховища даних з ETL-процесами, які можна запускати повторно
- Бази даних лише для читання або звітності
3.1.2 Доступні варіанти резервного копіювання
Просте відновлення підтримує:
- Повні резервні копії
- Диференціальні резервні копії
- Резервні копії файлів та файлових груп
- Резервні копії лише для копіювання
Резервні копії журналу транзакцій є недоступний у простій моделі відновлення.
3.2 Модель повного відновлення
3.2.1 Функції та переваги
Повне відновлення реєструє всі транзакції та зберігає записи журналу, доки ви не створите їх резервну копію. Це дозволяє повністю відновити дані до будь-якого моменту часу в резервній копії журналу транзакцій.
Основні переваги:
- Мінімальний потенціал втрати даних
- Можливість відновлення в певний момент часу
- Підтримує доставку журналів та дзеркалювання бази даних
- Максимальна гнучкість відновлення
3.2.2 Керування журналом транзакцій
Під час повного відновлення необхідно регулярно створювати резервні копії журналу транзакцій для:
- Запобігання заповненню дискового простору журналом транзакцій
- Підтримуйте безперервний ланцюг резервного копіювання
- Enable point-in-time recovery
- Контроль зростання файлу журналу
Типовий графік резервного копіювання: повне резервне копіювання щотижня, диференціальне резервне копіювання щодня, резервне копіювання журналів кожні 15-30 хвилин.
3.3 Модель відновлення з масовим реєструванням
3.3.1 Коли використовувати масове ведення журналу
Масове відновлення з веденням журналу мінімально реєструє масові операції, такі як BULK INSERT, SELECT INTO та перебудови індексу, зберігаючи при цьому повне ведення журналу для звичайних транзакцій.
Використовуйте відновлення з масовим записом даних, коли:
- Виконання великих операцій імпорту
- Перебудова індексів на великих таблицях
- Виконання операцій, що потребують мінімального ведення журналу
- Необхідність зменшення розміру журналу транзакцій під час певних операцій
3.3.2 Обмеження та міркування
Важливі обмеження:
- Відновлення на певний момент часу недоступне під час масових операцій.
- Резервні копії журналів збільшуються під час виконання масових операцій.
- За потреби необхідно перемикатися між повним та масовим веденням журналу
3.4 Вибір правильної моделі відновлення
Виберіть модель відновлення на основі бізнес-вимог:
| Модель відновлення | Ризик втрати даних | Point-in-Time Recovery | Best For |
|---|---|---|---|
| простий | Зміни з моменту останнього резервного копіювання | Немає | Розробка/тестування, прийнятна втрата даних |
| Повний | Мінімальний (зазвичай хвилини) | Так | Виробничі бази даних, критичні дані |
| Масово зареєстровано | Зміни з моменту останнього резервного копіювання журналу | Обмежено під час масових операцій | Темпrarвикористання під час масових операцій |
4. резервна копія SQL Server База даних за допомогою SSMS
4.1 Передумови та підготовка
Перш ніж створити резервну копію SQL Server бази даних, переконайтеся:
- У вас є відповідні дозволи (дозвіл db_owner або BACKUP DATABASE)
- Достатньо місця на диску для файлу резервної копії
- SQL Server Встановлено Management Studio (SSMS)
- Мережеві шляхи, доступні під час резервного копіювання в мережеві розташування
4.2 Покрокова інструкція: Повне резервне копіювання за допомогою SSMS
Виконайте ці кроки, щоб створити повну резервну копію вашого SQL Server база даних за допомогою SSMS.
4.2.1 Відкриття SQL Server Студія управління
- Запуск SQL Server Студія управління
- Введіть ім'я вашого сервера в Ім'я сервера поле
- Виберіть метод автентифікації
- Натисніть Спілкування
4.2.2 Вибір параметрів бази даних та резервного копіювання
- In Провідник об’єктів, розгорніть Бази даних вузол
- Клацніть правою кнопкою миші на базі даних, резервну копію якої потрібно створити
- Виберіть Завдання -> Підтримувати
- Перейдіть на вкладку Резервне копіювання бази даних вікно, перевірте ім'я бази даних
- Виберіть Повний в якості Тип резервного копіювання
4.2.3 Налаштування місця призначення резервного копіювання
- під призначення, Натисніть Видалення очистити шлях за замовчуванням (за потреби)
- Натисніть додавати вказати нове місце для резервного копіювання
- Введіть шлях до файлу та його назву за допомогою Бак розширення
- Натисніть OK підтвердити пункт призначення
4.2.4 Advanced Backup Налаштування
- Натисніть Параметри медіа на лівій панелі
- Виберіть параметри резервного копіювання:
- Перезаписати всі існуючі набори резервних копій – Замінює існуючі резервні копії
- Додати до існуючого набору резервних копій – Додає до існуючого файлу резервної копії
- Натисніть Параметри резервного копіювання на лівій панелі
- Налаштуйте додаткові параметри:
- Стиснути резервну копію – Зменшує розмір файлу резервної копії
- Шифрувати резервну копію – Захищає конфіденційні дані
- Перевірте резервну копію після завершення – Перевіряє цілісність резервної копії
4.2.5 Виконання резервного копіювання
- Перегляньте всі налаштування в Резервне копіювання бази даних вікно
- Натисніть OK до сtarпроцес резервного копіювання
- Зачекайте завершення резервного копіювання
- Після завершення резервного копіювання з’являється повідомлення про успішне створення резервної копії
- Натисніть OK щоб закрити діалогове вікно підтвердження
4.3 Створення диференціальної резервної копії за допомогою SSMS
Щоб створити диференціальну резервну копію, виконайте ті ж кроки, що й для повної резервної копії, але виберіть Диференціальний як тип резервної копії на кроці 4.2.2. Пам’ятайте, що диференціальні резервні копії вимагають попереднього повного резервного копіювання як базового рівня.
4.4 Створення резервної копії журналу транзакцій за допомогою SSMS
Резервні копії журналів транзакцій доступні лише для баз даних, що використовують моделі повного або групового відновлення.
- Клацніть правою кнопкою миші на базі даних у Провідник об’єктів
- Виберіть Завдання -> Підтримувати
- Вибрати Журнал транзакцій як тип резервної копії
- Налаштуйте пункт призначення та параметри за потреби
- Натисніть OK створити резервну копію журналу
4.5 Створення резервної копії лише для копіювання за допомогою SSMS
Резервні копії лише для копіювання не заважають вашій звичайній послідовності резервного копіювання.
- Виконайте кроки для створення повної резервної копії
- Перейдіть на вкладку Параметри резервного копіювання сторінка
- Оберіть Резервне копіювання лише для копіювання варіант
- Завершіть процес резервного копіювання звичайним способом
5. резервна копія SQL Server База даних за допомогою T-SQL
5.1 Базовий синтаксис резервної копії бази даних
Команда T-SQL BACKUP DATABASE забезпечує програмний контроль над SQL Server резервні копії.
BACKUP DATABASE database_name
TO DISK = 'backup_file_path'
WITH options;5.2 Повне резервне копіювання команд T-SQL
5.2.1 Простий скрипт повного резервного копіювання
Створіть повну резервну копію з мінімальними опціями:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
GO5.2.2 Повне резервне копіювання з опціями
Додайте описову інформацію та параметри форматування:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH FORMAT,
INIT,
NAME = 'AdventureWorks-Full Database Backup',
DESCRIPTION = 'Full backup of AdventureWorks database',
STATS = 10
GOПояснення опцій:
- ФОРМАТ – Створює новий набір резервних копій
- INIT – Перезаписує існуючий файл резервної копії
- ІМ'Я – Призначає назву набору резервних копій
- ОПИС – Додає описовий текст
- СТАТИСТИКА – Показує прогрес кожні 10%
5.3 Команди T-SQL диференціального резервного копіювання
Диференціальні резервні копії використовують параметр DIFFERENTIAL:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH DIFFERENTIAL,
INIT,
NAME = 'AdventureWorks-Differential Backup',
STATS = 10
GO5.4 Команди T-SQL для резервного копіювання журналу транзакцій
Використовуйте BACKUP LOG для резервного копіювання журналу транзакцій:
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH INIT,
NAME = 'AdventureWorks-Transaction Log Backup',
STATS = 10
GO5.5 Розширені параметри резервного копіювання T-SQL
5.5.1 Резервне копіювання на кілька файлів
Розподіліть резервну копію між кількома файлами для швидшої роботи:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_1.bak',
DISK = 'D:\Backups\AdventureWorks_2.bak',
DISK = 'E:\Backups\AdventureWorks_3.bak'
WITH FORMAT, INIT
GO5.5.2 Резервне копіювання зі стисненням
Зменште розмір файлу резервної копії та пропускну здатність мережі:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Compressed.bak'
WITH COMPRESSION,
INIT,
STATS = 10
GO5.5.3 Резервне копіювання з шифруванням
Захистіть конфіденційні дані за допомогою шифрування:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCertificate
),
STATS = 10
GO5.5.4 Резервне копіювання із захистом паролем
Додати захист паролем (застаріло, використовувати шифрування):
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH PASSWORD = 'StrongPassword123!',
INIT
GO5.5.5 Дзеркальні резервні копії
Створюйте одночасні копії в різні місця розташування:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
MIRROR TO DISK = 'D:\Backups\AdventureWorks_Mirror.bak'
WITH FORMAT, INIT
GO5.6 Приклади та сценарії резервного копіювання T-SQL
Повний скрипт резервного копіювання з обробкою помилок:
DECLARE @BackupPath NVARCHAR(500);
DECLARE @DatabaseName NVARCHAR(128) = 'AdventureWorks';
DECLARE @BackupDate NVARCHAR(20);
SET @BackupDate = CONVERT(NVARCHAR(20), GETDATE(), 112);
SET @BackupPath = 'C:\Backups\' + @DatabaseName + '_' + @BackupDate + '.bak';
BEGIN TRY
BACKUP DATABASE @DatabaseName
TO DISK = @BackupPath
WITH COMPRESSION,
INIT,
NAME = @DatabaseName + '-Full Backup',
STATS = 10;
PRINT 'Backup completed successfully: ' + @BackupPath;
END TRY
BEGIN CATCH
PRINT 'Backup failed: ' + ERROR_MESSAGE();
END CATCH
GO6. резервна копія SQL Server База даних за допомогою PowerShell
6.1 Командлети резервного копіювання PowerShell
SQL Server Модуль PowerShell надає командлети для автоматизації резервного копіювання:
- Резервне копіювання SQLDatabase – Створює резервні копії баз даних
- Відновлення SqlDatabase – Відновлює резервні копії бази даних
- Get-SqlDatabase – Отримує інформацію з бази даних
Імпортуйте SQL Server модуль:
Import-Module SqlServer6.2 Створення скриптів резервного копіювання за допомогою PowerShell
Базова команда резервного копіювання PowerShell:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks.bak" `
-BackupAction Database `
-CompressionOption OnПриклад диференціального резервного копіювання:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks_Diff.bak" `
-BackupAction Database `
-IncrementalРезервна копія журналу транзакцій:
Backup-SqlDatabase -ServerInstance "localhost" `
-Database "AdventureWorks" `
-BackupFile "C:\Backups\AdventureWorks_Log.trn" `
-BackupAction Log6.3 Автоматизація резервного копіювання за допомогою PowerShell
Створіть автоматизований сценарій резервного копіювання для кількох баз даних:
# Configuration
$ServerInstance = "localhost"
$BackupPath = "C:\Backups"
$Databases = @("AdventureWorks", "TestDB", "ProductionDB")
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
# Create backup directory if not exists
if (-not (Test-Path $BackupPath)) {
New-Item -ItemType Directory -Path $BackupPath
}
# Backup each database
foreach ($Database in $Databases) {
$BackupFile = Join-Path $BackupPath "$Database`_$Timestamp.bak"
try {
Backup-SqlDatabase -ServerInstance $ServerInstance `
-Database $Database `
-BackupFile $BackupFile `
-BackupAction Database `
-CompressionOption On
Write-Host "Successfully backed up $Database to $BackupFile" -ForegroundColor Green
}
catch {
Write-Host "Failed to backup $Database : $_" -ForegroundColor Red
}
}
7. резервна копія SQL Server База даних за допомогою командного рядка
SQL Server надає утиліти командного рядка, які дозволяють створювати резервні копії SQL Server базу даних без використання SSMS або графічних інтерфейсів. Ці інструменти необхідні для автоматизації, сценаріїв та сценаріїв віддаленого адміністрування.
7.1 Використання резервної копії бази даних SQLCMD
SQLCMD — це сучасна утиліта командного рядка для SQL Server що замінив OSQL. Він забезпечує розширену функціональність і є рекомендованим інструментом для виконання команд T-SQL з командного рядка.
7.1.1 Базовий синтаксис SQLCMD
sqlcmd -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"- -S: Визначає SQL Server назва екземпляра
- -d: Вказує ім'я бази даних
- -П: Виконує запит і завершує роботу
- -І: Використовує автентифікацію Windows
- -У: Вказує SQL Server ім'я користувача для входу
- -П: Вказує пароль для SQL Server Логін
7.1.2 Створення резервної копії за допомогою SQLCMD
Для резервного копіювання SQL Server за допомогою SQLCMD виконайте такі дії:
- відкритий Командний рядок or PowerShell
- перейдіть до SQL Server каталог інструментів (зазвичай додається до PATH під час встановлення)
- Виконайте команду резервного копіювання бази даних SQLCMD з відповідними параметрами
- Перевірте, чи файл резервної копії було успішно створено
Приклад команди повного резервного копіювання з використанням автентифікації Windows:
sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"Приклад використання SQL Server Аутентифікація:
sqlcmd -S localhost -U sa -P YourPassword -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"Створення диференціальної резервної копії за допомогою SQLCMD
sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, COMPRESSION, INIT"Створення резервної копії журналу транзакцій за допомогою SQLCMD
sqlcmd -S localhost -E -Q "BACKUP LOG AdventureWorks TO DISK='C:\Backups\AdventureWorks_Log.trn' WITH COMPRESSION, INIT"7.1.3 Резервне копіювання бази даних видавця в SQL Server Реплікація
Під час резервного копіювання баз даних видавця в SQL Server Реплікація, використовуйте параметр WITH REPLICATION, щоб зберегти метадані реплікації та забезпечити узгодженість транзакцій.
-- Backup publisher database with replication support
BACKUP DATABASE PublisherDB
TO DISK = 'C:\Backup\PublisherDB_Full.bak'
WITH REPLICATION,
COMPRESSION,
CHECKSUM,
INIT,
STATS = 10;
GOДетальніше про SQL Server реплікації, див. наш вичерпний посібник.
7.2 Використання резервної копії бази даних OSQL
OSQL — це застаріла утиліта командного рядка для SQL ServerХоча Microsoft рекомендує використовувати SQLCMD, OSQL залишається доступним для зворотної сумісності зі старими скриптами та системами.
7.2.1 Базовий синтаксис OSQL
Синтаксис OSQL схожий на SQLCMD:
osql -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"- -S: SQL Server назва екземпляра
- -d: Назва бази даних
- -П: Виконує запит та завершує роботу
- -І: Використовує довірене з'єднання (автентифікація Windows)
- -У: Ім'я користувача для входу
- -П: Пароль для входу
7.2.2 Створення резервної копії за допомогою OSQL
Щоб виконати операції резервного копіювання бази даних OSQL:
- відкритий Командний рядок
- Перевірте, чи доступний OSQL у вашій SQL Server установка
- Виконайте команду резервного копіювання OSQL
Приклад повної резервної копії:
osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH INIT"Приклад диференціального резервного копіювання:
osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, INIT"8. Третя сторона SQL Server Інструменти резервного копіювання
У той час як SQL Server включає вбудовані можливості резервного копіювання, сторонні інструменти пропонують розширені функції, автоматизацію та управління корпоративного рівня для організацій зі складними вимогами. Ці рішення забезпечують розширене стиснення, централізоване управління та оптимізовані робочі процеси для резервного копіювання. SQL Server бази даних у різних середовищах.
8.1 Резервне копіювання Veeam для SQL Server
Veeam пропонує комплексні рішення для захисту даних, спеціально розроблені для резервного копіювання. SQL Server бази даних з мінімальним впливом на виробничі системи.
Ключові особливості:
- Обробка з урахуванням потреб додатків для SQL Server узгодженість резервного копіювання
- Резервне копіювання та керування журналом транзакцій
- Відновлення в певний момент часу з детальними параметрами відновлення
- Інтеграція з Veeam Backup & Replication для єдиного захисту даних
- Автоматизована перевірка та валідація резервних копій
- Підтримка груп доступності Always On
- Рівень віртуальної машини та рівень програми SQL Server параметри резервного копіювання
8.2 Резервне копіювання Barracuda для SQL Server
Barracuda пропонує хмарні рішення для резервного копіювання зі спрощеним керуванням для Microsoft Windows. SQL Server операції резервного копіювання бази даних.
Ключові особливості:
- Автоматизований SQL Server планування резервного копіювання
- Вбудована хмарна реплікація до хмарного сховища Barracuda
- Глобальна дедуплікація та стиснення
- Можливості миттєвого локального відновлення
- Веб-консоль керування
- Підтримка повного, диференціального та резервного копіювання журналів транзакцій
- Захист від програм-вимагачів за допомогою незмінних резервних копій
8.3 Veritas NetBackup для SQL Server
Veritas NetBackup — це платформа резервного копіювання корпоративного рівня, яка забезпечує комплексний захист SQL Server бази даних у складних ІТ-середовищах.
Ключові особливості:
- Керування резервним копіюванням у масштабі підприємства для тисяч SQL Server випадки
- Розширені алгоритми дедуплікації та стиснення
- Гнучкі політики резервного копіювання та планування
- Підтримка для всіх SQL Server моделі відновлення
- Інтеграція з бібліотекою стрічкових програмrarта хмарне сховище
- Детальне відновлення баз даних, таблиць та об'єктів
- Підтримка кількох платформ (Windows, Linux) SQL Server)
- Автоматизоване управління життєвим циклом резервного копіювання
8.4 Повне резервне копіювання та відновлення Commvault для SQL Server
Commvault забезпечує інтелектуальне управління даними з комплексним резервним копіюванням SQL Server можливості та розширені функції автоматизації.
Ключові особливості:
- Оптимізація резервного копіювання та виявлення аномалій на основі штучного інтелекту
- Уніфікована платформа для резервного копіювання, відновлення та архівування
- Розширені налаштування SQL Server стиснення резервних копій (до 90%)
- Автоматизована оркестрація аварійного відновлення
- Live Sync для майже нульового захисту RPO
- Підтримка SQL Server локальні, хмарні та гібридні розгортання
- IntelliSnap для резервного копіювання на основі знімків
- Комплексні можливості дотримання вимог та електронного розкриття інформації
8.5 Захист даних щодо згуртованості для SQL Server
Cohesity забезпечує управління даними наступного покоління з гіперконвергентною інфраструктурою для сучасних SQL Server операції резервного копіювання.
Ключові особливості:
- Веб-масштабована архітектура для спрощеного управління
- Можливості миттєвого масового відновлення для SQL Server базами даних
- Знімки, сумісні з додатком
- Глобальна дедуплікація для всіх резервних копій
- Нативна інтеграція з хмарою (AWS, Azure, Google Cloud)
- Вбудовані панелі аналітики та моніторингу
- Можливості клонування та тестування бази даних
- Захист від програм-вимагачів за допомогою незмінних знімків
8.6 Red Gate SQL Backup Pro
Red Gate SQL Backup Pro – це спеціалізований інструмент, орієнтований виключно на оптимізацію SQL Server операції резервного копіювання та відновлення з чудовим стисненням та продуктивністю.
Ключові особливості:
- Найкращі в галузі коефіцієнти стиснення (до 95%)
- Стійкість мережі для резервного копіювання SQL Server через ненадійні з'єднання
- Шифрування резервних копій за допомогою 256-бітного AES
- Перевірка резервної копії та цілісності
- Детальна історія резервного копіювання та звіти
- Інтеграція з SQL Server Студія управління
- Підтримка резервного копіювання в мережеві сховища та хмарні сховища
- Паралельне резервне копіювання та відновлення для швидшої роботи
9. Як відновити SQL Server Database
9.1 Розуміння процесу відновлення
Відновлення a SQL Server База даних відтворює базу даних з резервних копій файлів. Процес відновлення зчитує файл резервної копії та відновлює базу даних до стану, коли вона була з резервної копії.
Важливі міркування:
- Відновлення перезаписує існуючу базу даних
- Користувачі відключаються під час відновлення
- Відновлення має відповідати послідовності резервного копіювання (повне, потім диференціальне, потім журнали)
- База даних недоступна під час операції відновлення
9.2 Відновлення повної резервної копії за допомогою SSMS
Виконайте ці кроки, щоб відновити повну резервну копію бази даних.
9.2.1 Покроковий процес відновлення
- відкритий SQL Server Студія управління та підключіться до свого сервера
- In Провідник об’єктів, клацніть правою кнопкою миші Бази даних
- Виберіть Відновити базу даних
- Перейдіть на вкладку Source розділ, виберіть Пристрій
- Натисніть ... кнопка для перегляду файлів резервних копій
- Натисніть додавати та перейдіть до вашого файлу .bak
- Виберіть файл резервної копії та натисніть OK
- Перейдіть на вкладку призначення розділі введіть ім'я бази даних
- Перегляньте набори резервних копій для відновлення
- Натисніть OK до сtarвідновлення
9.2.2 Параметри та налаштування відновлення
Натисніть Опції на лівій панелі для налаштування:
- Перезаписати існуючу базу даних (ЗАМІНИТИ) – Дозволяє відновлювати поверх існуючої бази даних
- Збереження станів реплікації (WITH KEEP_REPLICATION) – Тримайте SQL Server копіювання були
- Обмежити доступ до відновленої бази даних (WITH RESTRICTED_USER) – Обмеження post-відновити доступ
- Стан відновлення – Виберіть між ВІДНОВЛЕННЯМ З ВІДНОВЛЕННЯМ або БЕЗ ВІДНОВЛЕННЯ
9.3 Відновлення диференціальної резервної копії
Диференціальне відновлення вимагає як повного, так і диференціального резервного копіювання:
- Спочатку відновіть повну резервну копію за допомогою НЕ ВІДНОВЛЕННЯ варіант
- Потім відновіть диференціальну резервну копію за допомогою ВІДНОВЛЕННЯ варіант
Приклад T-SQL:
-- Restore full backup (NORECOVERY to allow differential)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
-- Restore differential backup (RECOVERY to complete)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH RECOVERY;
GO9.4 Відновлення за допомогою резервних копій журналу транзакцій
Для відновлення в певний момент часу відновлюйте в такій послідовності:
- Відновіть повну резервну копію за допомогою NORECOVERY
- Відновлення диференціальної резервної копії за допомогою NORECOVERY (якщо доступно)
- Відновлення резервних копій журналу транзакцій по порядку за допомогою NORECOVERY
- Відновлення остаточної резервної копії журналу за допомогою RECOVERY
-- Restore full backup
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
-- Restore first log backup
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;
-- Restore second log backup with recovery
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log2.trn'
WITH RECOVERY;
GO9.5 Відновлення до певного моменту часу
Відновіть базу даних до певного моменту часу за допомогою опції STOPAT:
-- Restore to specific time: January 15, 2025 at 2:30 PM
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY, STOPAT = '2025-01-15 14:30:00';
GO9.6 Відновлення таблиці
SQL Server не підтримує пряме відновлення на рівні таблиці з резервних копій файлів. Однак деякі рішення все ж існують.
9.6.1 Спосіб 1: Знімки бази даних (найкращий варіант для запобігання)
Знімки бази даних забезпечують найшвидший спосіб відновлення даних таблиці, якщо їх було створено до виникнення проблеми. Знімок – це статичне подання бази даних у певний момент часу, доступне лише для читання.
Створення знімка бази даних:
-- Create snapshot before making changes
CREATE DATABASE ProductionDB_Snapshot_20250107
ON
( NAME = ProductionDB_Data,
FILENAME = 'C:\Snapshots\ProductionDB_Snapshot.ss' )
AS SNAPSHOT OF ProductionDB;
GOВідновлення даних таблиці зі знімка:
USE ProductionDB;
GO
-- Replace entire table content
BEGIN TRANSACTION;
-- Disable constraints temporarily
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT ALL;
-- Clear current data
TRUNCATE TABLE dbo.Orders;
-- Restore from snapshot
INSERT INTO dbo.Orders
SELECT * FROM ProductionDB_Snapshot_20250107.dbo.Orders;
-- Re-enable constraints
ALTER TABLE dbo.Orders CHECK CONSTRAINT ALL;
COMMIT TRANSACTION;
GOВимоги до версії: Знімки бази даних доступні в SQL Server Enterprise Edition (усі версії) та Standard Editiontarting with SQL Server 2016 SP1.
9.6.2 Спосіб 2: Відновлення темпуrarБаза даних y (Most Звичайний)
Цей метод працює, коли вам потрібно відновити дані таблиці після виникнення проблеми, а знімок не існує:
- Відновлення резервної копії до темпуrarбаза даних y
- Копіювання даних таблиці з темпуrarбази даних y до поточної бази даних
9.7 Відновлення сторінки
Відновлення сторінок відновлює окремі пошкоджені сторінки без відновлення всієї бази даних, мінімізуючи час простою завдяки tarотримання лише пошкоджених сторінок. Ця функція доступна лише в моделях повного або масового відновлення та вимагає безперервного ланцюжка резервних копій журналу транзакцій від резервної копії сторінки до поточного файлу журналу.
Щоб виконати відновлення сторінки, спочатку визначте пошкоджені сторінки, зробіть резервну копію журналу транзакцій, відновіть потрібні сторінки, а потім застосуйте всі журнали транзакцій:
-- Identify damaged pages
SELECT * FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID('AdventureWorks');
-- Take tail-log backup
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH NORECOVERY;
-- Restore damaged pages
RESTORE DATABASE AdventureWorks
PAGE = '1:123, 1:456'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;
-- Apply transaction logs
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH RECOVERY;
GOПримітка: Відновлення сторінок недоступне в моделі простого відновлення. Ви не можете відновити сторінки із системних таблиць або метаданих основної файлової групи.
9.8 Поетапне відновлення
Часткове відновлення (поетапне відновлення) відновлює базу даних поетапно на рівні файлової групи,tarвзаємодія з основною файловою групою. Це негайно переводить критично важливі дані в онлайн-режим, тоді як менш критичні дані відновлюються у фоновому режимі. За простої моделі відновлення всі файлові групи з правами читання та запису мають бути відновлені разом з основною файловою групою; окремо можна відновити лише файлові групи з правами читання. За моделей повного або групового відновлення з веденням журналів кожну файлову групу можна перевести в онлайн-режим незалежно після застосування журналів транзакцій.
| Модель відновлення | Поведінка поетапного відновлення |
|---|---|
| простий | Основна та всі файлові групи для читання та запису відновлені разом. Файлові групи лише для читання відновлені окремо. |
| Повний/масовий журнал | Кожна файлова група відновлюється незалежно на рівні файлової групи. |
Приклад моделі повного відновлення – спочатку відновіть основну файлову групу, щоб перевести базу даних в онлайн-режим, а потім відновіть вторинні файлові групи, не втрачаючи працездатності бази даних:
-- Stage 1: Restore primary filegroup (database comes online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GO
-- Stage 2: Restore secondary filegroup (database stays online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'HistoricalData'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GOПриклад простої моделі відновлення:
-- Restore primary with all read-write filegroups
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, RECOVERY;
-- Restore read-only filegroup separately
RESTORE DATABASE AdventureWorks
FILEGROUP = 'ReadOnlyArchive'
FROM DISK = 'C:\Backups\AdventureWorks_ReadOnly.bak'
WITH RECOVERY;
GO9.9 Відновлення за допомогою команд T-SQL
Повний скрипт відновлення з переміщенням файлів:
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'D:\Data\AdventureWorks.mdf',
MOVE 'AdventureWorks_Log' TO 'E:\Logs\AdventureWorks.ldf',
REPLACE,
STATS = 10;
GO9.10 Перевірка цілісності резервної копії перед відновленням
Перевірте дійсність резервної копії без відновлення:
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak';
GOЦя команда перевіряє, чи резервний набір повний та чи читається, без фактичного відновлення бази даних.
10. SQL Server Найкращі методи резервного копіювання
10.1 Розробка стратегії резервного копіювання
10.1.1 Оцінка бізнес-вимог
Перш ніж впроваджувати резервне копіювання, оцініть:
- Критичність даних: Наскільки важливі ці дані для операцій?
- Зміна частоти: Як часто змінюються дані?
- Розмір бази даних: Який розмір бази даних?
- Доступні ресурси: Який обсяг сховища та пропускної здатності доступний?
- Вимоги до дотримання вимог: Яких правил ви повинні дотримуватися?
10.1.2 Визначення RTO та RPO
Цільовий час відновлення (RTO): Максимально допустимий час простою. Визначає, як швидко потрібно відновити роботу.
Цільова точка відновлення (RPO): Максимально допустима втрата даних. Визначає частоту резервного копіювання.
| Вимога RTO/RPO | Рекомендована стратегія резервного копіювання |
|---|---|
| RPO: Години, RTO: Години | Щоденний повний + журнал транзакцій кожні 1-2 години |
| RPO: Хвилини, RTO: Години | Щоденне повне резервне копіювання + резервне копіювання журналів кожні 15-30 хвилин |
| RPO: Майже нуль, RTO: Хвилини | Завжди доступні групи доступності + часте резервне копіювання журналів |
| RPO: Дні, RTO: Дні | Тижневий повний + денний диференціал |
10.2 Створення розкладу резервного копіювання
10.2.1 Рекомендації щодо частоти
Типовий графік резервного копіювання для виробничих баз даних:
- Повні резервні копії: Щотижня (у неділю ввечері під час низької активності)
- Диференціальні резервні копії: Щоденно (щовечора)
- Резервні копії журналу транзакцій: Кожні 15-30 хвилин у робочий час
- Резервні копії лише для копіювання: За потреби для тестування або розробки
10.2.2 Балансування продуктивності та захисту
Враховуйте ці фактори під час планування:
- Години без піку: Створюйте повні резервні копії в періоди низької активності
- Вплив ресурсів: Стиснення зменшує обсяг вводу-виводу, але збільшує використання процесора
- Пропускна здатність мережі: Планування резервного копіювання мережі при низькому трафіку
- Вікна резервного копіювання: Забезпечте створення резервних копій до початку робочого часу
10.3 Рекомендації щодо зберігання резервних копій
10.3.1 Зберігання на місці та поза місцем зберігання
Резервні копії на місці:
- Швидше резервне копіювання та відновлення
- Нижня сost для високочастотного доступу
- Вразливий до місцевих катастроф
- Найкраще підходить для сценаріїв швидкого відновлення
Резервні копії поза сайтом:
- Захист від стихійних лих, характерних для конкретного місця
- Дотримання вимог щодо географічного резервування
- Повільніший час відновлення
- Необхідний для відновлення після катастроф
10.3.2 Параметри резервного копіювання в хмару
Переваги хмарного сховища:
- Azure Blob Storage: рідний SQL Server інтеграція, cost-ефективний для нечастого доступу
- Amazon S3: Дуже міцні, гнучкі яруси для зберігання
- Google Cloud Storage: Конкурентні ціни, глобальна доступність
10.3.3 Політики зберігання резервних копій
Політика зберігання зразків:
- Зберігайте щоденні резервні копії протягом 7 днів
- Зберігайте щотижневі резервні копії протягом 4 тижнів
- Зберігайте щомісячні резервні копії протягом 12 місяців
- Зберігайте щорічні резервні копії протягом 7 років (відповідність)
10.4 Стиснення та шифрування резервних копій
Переваги компресії:
- Зменшує розмір файлу резервної копії на 50-70%
- Зменшує час резервного копіювання
- Знижує об'єм зберіганняosts
- Зменшує пропускну здатність мережі для віддалених резервних копій
Найкращі практики шифрування:
- Завжди шифруйте резервні копії, що містять конфіденційні дані
- Використовуйте 256-бітове шифрування AES
- Безпечне керування сертифікатами або ключами
- Документуйте ключі шифрування та зберігайте їх окремо
10.5 Тестування та перевірка резервних копій
10.5.1 Регулярне тестування відновлення
Процедури тестового відновлення щоквартально або щомісяця:
- Відновлення резервних копій у тестовому середовищі
- Перевірте цілісність та повноту даних
- Перевірте функціональність програми
- Час відновлення документа (перевірка RTO)
- Визначте та вирішіть будь-які проблеми
10.5.2 Використання RESTORE VERIFYONLY
Автоматизуйте перевірку резервних копій:
-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GOВиконайте перевірку одразу після завершення резервного копіювання або в рамках планового технічного обслуговування.
10.6 Автоматизація та моніторинг резервного копіювання
10.6.1 SQL Server Агент Джобс
Створення автоматизованих завдань резервного копіювання:
- Розширювати SQL Server Агент
в SSMS - Клацніть правою кнопкою миші Вакансії і виберіть Нова робота
- Назвіть завдання (наприклад, «Щоденне повне резервне копіювання»)
- Додавати Крок за допомогою команд резервного копіювання T-SQL
- Створити Розклад за час виконання
- Конфігурувати Сповіщення для успіху/невдачі
10.6.2 Плани технічного обслуговування
SQL Server Плани технічного обслуговування забезпечують візуальний інтерфейс для автоматизації резервного копіювання:
- перейдіть до управління -> Плани технічного обслуговування
- Клацніть правою кнопкою миші та виберіть Майстер плану технічного обслуговування
- Виберіть завдання резервного копіювання для автоматизації
- Налаштування розкладу та параметрів резервного копіювання
- Налаштування звітності та ведення журналу
10.6.3 Сповіщення та оповіщення про резервне копіювання
Налаштуйте сповіщення електронною поштою:
- Налаштування пошти бази даних у SQL Server
- Створення сповіщень про збої резервного копіювання
- Відстеження історії завдань резервного копіювання
- Надсилати зведені звіти адміністраторам
10.7 Документація та планування аварійного відновлення
Вести повну документацію:
- Графік резервного копіювання: Коли і що створюється резервною копією
- Політика зберігання: Як довго зберігаються резервні копії
- Місця зберігання: Де зберігаються резервні копії
- Процедури відновлення: Покрокові інструкції з відновлення
- Контактна інформація: Ключовий персонал та постачальники
- Результати тесту на відновлення: Задокументовані результати тестування
11. Розширений SQL Server Сценарії резервного копіювання
11.1 Резервне копіювання дуже великих баз даних (VLDB)
11.1.1 Стратегії роботи з файлами та файловими групами
Для баз даних, що перевищують кілька сотень гігабайт:
- Розділіть дані лише для читання та для читання/запису на різні файлові групи
- Нечасто створюйте резервні копії файлових груп лише для читання
- Зосередьте часте резервне копіювання на активних файлових групах
- Використовуйте резервні копії на рівні файлів для детального контролю
Приклад резервної копії файлу:
-- Back up specific file
BACKUP DATABASE LargeDB
FILE = 'LargeDB_Data1'
TO DISK = 'C:\Backups\LargeDB_File1.bak'
WITH COMPRESSION;
GO11.1.2 Оптимізація продуктивності резервного копіювання
Покращення продуктивності резервного копіювання VLDB:
- Смугасті резервні копії: Запис у кілька файлів одночасно
- Ступінь стиснення: Зменшення вимог до вводу-виводу та сховища
- Кілька резервних пристроїв: Паралелізація операцій резервного копіювання
- Швидке зберігання: Використання SSD-накопичувачів для резервного копіювання
- Кількість буферів: Збільшення параметра BUFFERCOUNT
- Максимальний розмір переказу: Оптимізуйте налаштування MAXTRANSFERSIZE
-- Optimized VLDB backup
BACKUP DATABASE LargeDB
TO DISK = 'C:\Backups\LargeDB_1.bak',
DISK = 'D:\Backups\LargeDB_2.bak',
DISK = 'E:\Backups\LargeDB_3.bak'
WITH COMPRESSION,
BUFFERCOUNT = 100,
MAXTRANSFERSIZE = 4194304;
GO11.2 Резервне копіювання в групах доступності Always On
Групи доступності Always On розподіляють навантаження резервного копіювання між репліками:
- Налаштування параметрів резервного копіювання (основного, додаткового або будь-якої репліки)
- Розвантаження резервних копій на вторинні репліки для зменшення основного навантаження
- Використовуйте резервні копії COPY_ONLY на вторинних репліках
- Відстеження налаштувань пріоритету резервного копіювання
-- Check backup preferences
SELECT
ag.name AS AvailabilityGroup,
ar.replica_server_name,
ar.backup_priority
FROM sys.availability_replicas ar
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;
GO11.3 Резервне копіювання для дзеркалювання бази даних
У сценаріях дзеркалювання бази даних:
- Регулярно створюйте резервні копії основної бази даних
- Резервні копії журналу транзакцій є важливими для дзеркалювання
- Дзеркальна база даних перебуває у стані ВІДНОВЛЕННЯ (неможливо створити резервну копію безпосередньо)
- Розгляньте можливість створення резервної копії дзеркала після відновлення після відмови
11.4 Резервне копіювання до сховища BLOB-об'єктів Azure
SQL Server можна створювати резервні копії безпосередньо в сховище BLOB-об'єктів Azure:
- Створіть обліковий запис Azure Storage
- Створити SQL Server облікові дані для автентифікації Azure
- Використовуйте синтаксис URL-адреси для місця призначення резервної копії
-- Create credential for Azure
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your_SAS_token';
GO
-- Backup to Azure
BACKUP DATABASE AdventureWorks
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/AdventureWorks.bak'
WITH COMPRESSION,
STATS = 10;
GO11.5 Резервне копіювання на URL-адресу
Переваги резервного копіювання на URL-адресу:
- Необмежена ємність хмарного сховища
- Географічне резервування обробляється автоматично
- Розрахункова модель ціноутворення
- Не потрібно місця на локальному диску
- Підтримує до 64 URL-адрес на резервну копію (чергування)
11.6 Смугасті резервні копії для підвищення продуктивності
Черговате резервне копіювання розділяє дані між кількома файлами для швидшого вводу-виводу:
-- Striped backup to 4 files
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AW_Stripe1.bak',
DISK = 'D:\Backups\AW_Stripe2.bak',
DISK = 'E:\Backups\AW_Stripe3.bak',
DISK = 'F:\Backups\AW_Stripe4.bak'
WITH COMPRESSION, FORMAT;
GOПримітка: Для відновлення потрібні всі файли смуг. Відсутність будь-якого файлу робить резервну копію непридатною для використання.
12. Вирішення проблем SQL Server Проблеми резервного копіювання
12.1 Поширені помилки резервного копіювання та їх вирішення
Помилка: «Помилка операційної системи 5: Доступ заборонено»
- Причина: SQL Server обліковому запису служби бракує дозволів
- Рішення: Надати права на запис для SQL Server обліковий запис служби в папці резервних копій
Помилка: «Не вдається відкрити резервний пристрій… Помилка пристрою або пристрій відключений від мережі»
- Причина: Недійсний шлях або недоступний мережевий ресурс
- Рішення: Перевірте існування шляху, перевірте підключення до мережі, переконайтеся, що достатньо місця на диску
Помилка: «Недостатньо місця на диску»
- Причина: Недостатньо місця на диску для резервного копіювання
- Рішення: Звільніть місце на диску, використовуйте стиснення, резервне копіювання в інше місце
Помилка: «База даних використовується. База даних використовується іншими користувачами»
- Причина: Активні з'єднання під час відновлення
- Рішення: Використайте опцію WITH REPLACE або спочатку відключіть користувачів
12.2 Проблеми з продуктивністю резервного копіювання
Діагностика повільних резервних копій:
- Перевірте продуктивність вводу/виводу диска за допомогою Performance Monitor
- Відстежуйте перебіг резервного копіювання за допомогою опції STATS
- Розгляд SQL Server журнал помилок для вузьких місць
- Розгляньте стиснення для зменшення обсягу вводу-виводу
- Використання чергуваних резервних копій на кількох дисках
Запит для моніторингу прогресу резервного копіювання:
SELECT
session_id,
command,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%';
GO12.3 Проблеми з простором та зберіганням
Запобігання проблемам зі зберіганням:
- Впроваджуйте політики збереження даних: Автоматичне видалення старих резервних копій
- Використовуйте стиснення: Зменшити розмір файлу резервної копії на 50-70%
- Архівуйте для дешевшого сховища: Переміщення старих резервних копій до архівного сховища
- Монітор дискового простору: Налаштування сповіщень про нестачу місця на диску
- Приблизний розмір резервної копії: Обчисліть очікуваний розмір перед резервним копіюванням
Приблизний розмір резервної копії:
-- Estimate full backup size
EXEC sp_spaceused;
GO12.4 Проблеми з дозволами та доступом
Необхідні дозволи для резервного копіювання:
- РЕЗЕРВНА БАЗА ДАНИХ дозвіл
- db_backupoperator рольова приналежність
- сисадмін роль сервера (для всіх операцій резервного копіювання)
Надати дозволи на резервне копіювання:
-- Grant backup permission to user
GRANT BACKUP DATABASE TO [BackupUser];
GRANT BACKUP LOG TO [BackupUser];
GO
-- Add user to backup operator role
ALTER ROLE db_backupoperator ADD MEMBER [BackupUser];
GO12.5 Пошкоджені файли резервних копій
Виявлення та обробка пошкоджених резервних копій:
Перевірте цілісність резервної копії:
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GOУвімкнути CHECKSUM для майбутніх резервних копій:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM, INIT;
GOСтратегії профілактики:
- Завжди використовувати параметр CHECKSUM під час резервного копіювання
- Перевіряйте резервні копії одразу після створення
- Тестування відновлюється регулярно
- Зберігайте резервні копії на надійному сховищі
- Зберігайте кілька резервних копій
12.6 Відновлення даних з пошкоджених резервних копій файлів
Якщо ваші резервні копії пошкоджені, але ви все ще хочете відновити з них дані, ви можете скористатися сторонніми інструментами, такими як DataNumen SQL Recovery, як зазначено нижче:
- Start DataNumen SQL Recovery.
- Виберіть пошкоджений файл резервної копії як вихідний файл, змінивши фільтр на «Усі файли (*.*)»:
- За потреби встановіть вихідний файл .MDF.
- Клацніть на “Star«Відновлення» та дотримуйтесь інструкцій для відновлення бази даних.
- Після завершення процесу відновлення з’явиться нова база даних відновлення. SQL Server який містить усі відновлені дані.
13. SQL Server Безпека резервного копіювання
13.1 Захист резервних копій файлів
Захистіть резервні копії файлів від несанкціонованого доступу:
- Дозволи файлової системи: Обмежити доступ лише авторизованим адміністраторам
- Безпека мережі: Використовуйте безпечні протоколи для резервного копіювання мережі
- Фізична охорона: Зберігайте резервні копії носіїв у безпечних місцях
- Журнал доступу: Аудит доступу до файлів резервних копій
13.2 Параметри шифрування
SQL Server підтримує прозоре шифрування резервного копіювання:
Створити сертифікат для шифрування:
-- Create master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!';
GO
-- Create certificate
CREATE CERTIFICATE BackupCertificate
WITH SUBJECT = 'Database Backup Certificate',
EXPIRY_DATE = '2026-12-31';
GOЗашифрована резервна копія:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCertificate
);
GOВажливо: Зробіть резервну копію сертифіката та закритого ключа окремо. Без них зашифровані резервні копії неможливо відновити.
-- Backup certificate
BACKUP CERTIFICATE BackupCertificate
TO FILE = 'C:\Certificates\BackupCertificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\Certificates\BackupCertificate.key',
ENCRYPTION BY PASSWORD = 'C3rt!f!c@t3P@ss'
);
GO13.3 Контроль доступу та дозволи
Реалізуйте принцип найменших привілеїв:
- Надавати дозволи на резервне копіювання лише необхідним обліковим записам
- Використовуйте окремі облікові записи для операцій резервного копіювання та відновлення
- Уникайте використання облікового запису SA для резервного копіювання
- Регулярно перевіряйте дозволи на резервне копіювання
- Видаліть дозволи, коли вони більше не потрібні
13.4 Зауваження відповідності
Зверніть увагу на нормативні вимоги:
- GDPR: Шифруйте резервні копії, що містять персональні дані, впроваджуйте політики зберігання
- HIPAA: Шифруйте захищену медичну інформацію в резервних копіях, контролюйте доступ, ведіть журнали аудиту
- PCIDSS: Шифрування резервних копій даних власників карток, безпечне сховище резервних копій
- SOX: Підтримка цілісності резервних копій, політики зберігання документів
14. Моніторинг та підтримка операцій резервного копіювання
14.1 Відстеження історії резервного копіювання
SQL Server зберігає історію резервного копіювання в базі даних msdb:
-- View recent backup history
SELECT
bks.database_name,
bks.backup_start_date,
bks.backup_finish_date,
CASE bks.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
ELSE 'Other'
END AS backup_type,
bks.backup_size / 1024 / 1024 AS backup_size_mb,
bkmf.physical_device_name
FROM msdb.dbo.backupset bks
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bks.media_set_id = bkmf.media_set_id
WHERE bks.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY bks.backup_start_date DESC;
GOЗнайти бази даних без нещодавніх резервних копій:
SELECT
d.name AS database_name,
MAX(bs.backup_finish_date) AS last_backup_date,
DATEDIFF(DAY, MAX(bs.backup_finish_date), GETDATE()) AS days_since_last_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4 -- Exclude system databases
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE())
OR MAX(bs.backup_finish_date) IS NULL
ORDER BY last_backup_date;
GO14.2 Використання SQL Server Звіти
SQL Server Management Studio містить вбудовані звіти про резервне копіювання:
- Клацніть правою кнопкою миші на базі даних у Провіднику об'єктів
- Виберіть Звіти -> Стандартні звіти
- Виберіть з доступних звітів:
- Події резервного копіювання та відновлення
- Усі резервні копії
- Стан доставки журналу транзакцій
14.3 Інструменти моніторингу третіх сторін
Рішення для комерційного моніторингу:
- SQL Sentry: Комплексний моніторинг та оповіщення
- Монітор SQL Redgate: Моніторинг та діагностика в режимі реального часуostics
- Аналізатор продуктивності бази даних SolarWinds: Моніторинг продуктивності та резервного копіювання
- Діагностика SQL в IderaostМенеджер інтерфейсу: Перевірка резервного копіювання та оповіщення
14.4 Перевірки справності резервного копіювання
Створіть процедуру перевірки стану здоров'я:
-- Backup health check procedure
CREATE PROCEDURE sp_BackupHealthCheck
AS
BEGIN
-- Check for databases without recent full backup
SELECT
'Missing Recent Full Backup' AS issue,
d.name AS database_name,
ISNULL(CAST(MAX(bs.backup_finish_date) AS VARCHAR), 'Never') AS last_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
ON d.name = bs.database_name AND bs.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE()) OR MAX(bs.backup_finish_date) IS NULL; -- Check for failed backup jobs SELECT 'Failed Backup Job' AS issue, j.name AS job_name, jh.run_date, jh.run_time, jh.message FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id WHERE jh.run_status = 0 -- Failed AND jh.step_id = 0 AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-7, 112))
AND j.name LIKE '%backup%';
END
GO15. SQL Server Найчастіші запитання щодо резервного копіювання
15.1 Як часто слід створювати резервні копії SQL Server?
Частота резервного копіювання залежить від вашої цільової точки відновлення (RPO):
- Критично важливі виробничі бази даних: Повний тижневий, диференціальний щоденний, журнал кожні 15-30 хвилин
- Стандартні виробничі бази даних: Повний тижневий, диференціальний щоденний, журнал кожні 1-2 години
- Бази даних розробки: Повний щоденний або щотижневий
- Бази даних лише для читання: Заповнюється після кожної зміни даних
15.2 Яка різниця між повним та диференціальним резервним копіюванням?
Повне резервне копіювання копіює всю базу даних, тоді як диференціальне резервне копіювання фіксує лише зміни з моменту останнього повного резервного копіювання. Диференціальне резервне копіювання менше за розміром і швидше, але для відновлення потрібна базова повна резервна копія.
15.3 Чи можу я створити резервну копію SQL Server Поки воно працює?
Так, SQL Server підтримує онлайн-резервне копіювання. Користувачі можуть продовжувати роботу під час операцій резервного копіювання. SQL Server використовує журнал транзакцій для підтримки узгодженості, забезпечуючи дійсність резервних копій навіть за одночасних змін.
15.4 Як довго триває SQL Server Резервний дубль?
Тривалість резервного копіювання залежить від:
- Розмір бази даних: Більші бази даних працюють довше
- Тип резервного копіювання: Повне резервне копіювання займає найдовше
- Ступінь стиснення: Може збільшити час роботи процесора, але зменшити загальну тривалість
- Швидкість зберігання: SSD-накопичувачі значно швидші за жорсткі диски (HDD)
- Навантаження на сервер: Вища активність уповільнює резервне копіювання
Типові діапазони: повне резервне копіювання бази даних розміром 10 ГБ зі стисненням на сучасному обладнанні може зайняти 5-15 хвилин.
15.5 Де слід зберігати SQL Server Резервні копії?
Найкраща практика: Дотримуйтесь правила 3-2-1:
- 3 копії ваших даних
- 2 різні типи сховищ (наприклад, дискові та стрічкові/хмарні)
- 1 копіювати за межі сайту
Рекомендовані місця:
- Локальний диск для швидкого відновлення
- Мережеве сховище для централізованого управління
- Хмарне сховище (Azure, AWS) для аварійного відновлення
15.6 Що таке розширення файлу .bak?
Розширення .bak – це розширення файлу за замовчуванням для SQL Server резервні копії файлів. Це домовленість, а не вимога – SQL Server Резервні копії працюють з будь-яким розширенням файлу. Однак використання розширення .bak робить резервні файли легко ідентифікованими та є галузевим стандартом.
15.7 Як створити резервну копію SQL Server на мережевий диск?
Щоб створити резервну копію на мережевий диск:
- Забезпечувати SQL Server обліковий запис служби має дозволи на запис у мережевому ресурсі
- Використовуйте UNC-шлях у команді резервного копіювання:
\\ServerName\ShareName\BackupFile.bak - Перевірте підключення перед плануванням автоматичного резервного копіювання
BACKUP DATABASE AdventureWorks
TO DISK = '\\BackupServer\SQLBackups\AdventureWorks.bak'
WITH COMPRESSION, INIT;
GO15.8 Чи можна стискати SQL Server Резервні копії?
Так, SQL Server підтримує вбудоване стиснення резервних копій (Enterprise Edition або Standard Edition)tarдзвін SQL Server 2016 SP1). Стиснення зазвичай зменшує розмір резервної копії на 50-70% і часто скорочує час резервного копіювання, зменшуючи обсяг вводу-виводу, хоча це збільшує використання процесора.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COMPRESSION;
GO16. Висновок
16.1 ключових виносів
Ефективний SQL Server Стратегії резервного копіювання захищають ваші дані та забезпечують безперервність бізнесу. Пам’ятайте про ці важливі моменти:
- Розуміння типів резервного копіювання: Виберіть відповідні типи резервних копій (повне, диференціальне, журналу транзакцій) на основі ваших вимог до відновлення
- Виберіть правильну модель відновлення: Повне відновлення критично важливих даних, просте для баз даних розробки
- Впровадити розклад резервного копіювання: Регулярне повне резервне копіювання в поєднанні з диференціальним резервним копіюванням та резервним копіюванням журналів мінімізує втрату даних
- Процедури тестового відновлення: Резервні копії цінні лише тоді, коли їх можна успішно відновити
- Автоматизуйте та контролюйте: Скористайтеся кнопкою SQL Server Агент, плани технічного обслуговування та інструменти моніторингу
- Безпечні резервні копії: Шифруйте конфіденційні дані та контролюйте доступ до резервних копій файлів
- Зберігайте копії поза межами сайту: Захист від катастроф на рівні всього сайту за допомогою хмарного або віддаленого сховища
- Документуйте все: Ведіть чітку документацію процедур резервного копіювання та відновлення
16.2 Наступні кроки та ресурси
Щоб покращити свій SQL Server реалізація резервного копіювання:
- Оцініть свою поточну стратегію резервного копіювання відповідно до найкращих практик
- Розрахуйте свої вимоги до RTO та RPO
- Тестування процедур відновлення на невиробничих системах
- Регулярно переглядайте та оновлюйте розклади резервного копіювання
- Впровадити автоматизований моніторинг та оповіщення
- Навчіть членів команди процедурам відновлення
Додаткові ресурси:
- Microsoft SQL Server Документація: Офіційні інструкції з резервного копіювання та відновлення
- SQL Server форуми спільноти резервного копіювання: обмін досвідом та рішеннями
- Професійні сертифікати: Сертифіковано Microsoft: Адміністратор бази даних Azure
16.3 Рекомендовані інструменти та рішення
Виходячи з різних сценаріїв:
Малі підприємства:
- рідний SQL Server резервне копіювання за розкладом SQL Server Вакансії агентів
- SQLBackupAndFTP для інтеграції з хмарою
- Резервне копіювання Azure для SQL Server
Середні підприємства:
- SQL Server Плани технічного обслуговування
- Сторонні інструменти, такі як Redgate SQL Backup Pro
- Резервне копіювання Veeam для SQL Server
Великі підприємства:
- Quest LiteSpeed для максимального стиснення
- Commvault або Veritas NetBackup для керування резервним копіюванням у підприємстві
- Групи доступності Always On для високої доступності
SQL Server Резервне копіювання є основоположним для адміністрування баз даних. Завдяки належному плануванню, впровадженню та тестуванню ви можете забезпечити захист своїх даних та їх відновлення за потреби.tarвпроваджуючи ці найкращі практики сьогодні, щоб забезпечити свою SQL Server бази даних
Про автора
Юань Шен є старшим адміністратором баз даних (DBA) з понад 10-річним досвідом роботи в SQL Server середовища та управління корпоративними базами даних. Він успішно вирішив сотні сценаріїв відновлення баз даних у фінансових службах, охороні здоров'я та виробничих організаціях.
Юань спеціалізується на SQL Server відновлення баз даних, рішення для забезпечення високої доступності та оптимізація продуктивності. Його великий практичний досвід включає управління багатотерабайтними базами даних, впровадження груп доступності Always On та розробку автоматизованих стратегій резервного копіювання та відновлення для критично важливих бізнес-систем.
Завдяки своїй технічній експертизі та практичному підходу, Юань зосереджується на створенні комплексних посібників, які допомагають адміністраторам баз даних та ІТ-фахівцям вирішувати складні SQL Server ефективно вирішує проблеми. Він слідкує за останніми новинками SQL Server випуски та технології баз даних Microsoft, що розвиваються, регулярно тестуючи сценарії відновлення, щоб переконатися, що його рекомендації відображають найкращі практики реального світу.
Є запитання щодо SQL Server відновлення чи потрібні додаткові інструкції з усунення несправностей бази даних? Юань вітає відгуки та пропозиції для покращення цих технічних ресурсів.