Восстановление 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: Используйте 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 Управление журналом транзакций
При полном восстановлении необходимо регулярно выполнять резервное копирование журнала транзакций для:
- Предотвратить заполнение дискового пространства журналом транзакций
- Поддерживайте непрерывную цепочку резервного копирования
- Включить восстановление на определенный момент времени
- Контроль роста файла журнала
Типичный график резервного копирования: полное резервное копирование еженедельно, дифференциальное резервное копирование ежедневно, резервное копирование журналов каждые 15–30 минут.
3.3 Модель восстановления с массовым протоколированием
3.3.1 Когда использовать массовую регистрацию
Восстановление с массовым протоколированием минимально регистрирует массовые операции, такие как BULK INSERT, SELECT INTO и перестроение индексов, сохраняя при этом полное протоколирование обычных транзакций.
Используйте восстановление с массовым протоколированием, когда:
- Выполнение крупных импортных операций
- Перестроение индексов на больших таблицах
- Выполнение операций, требующих минимального ведения журнала
- Необходимость уменьшения размера журнала транзакций во время определенных операций
3.3.2 Ограничения и соображения
Важные ограничения:
- Восстановление на определенный момент времени недоступно во время массовых операций.
- Резервные копии журналов увеличиваются при выполнении массовых операций.
- При необходимости необходимо переключаться между полным и массовым протоколированием.
3.4 Выбор правильной модели восстановления
Выберите модель восстановления на основе бизнес-требований:
| Модель восстановления | Риск потери данных | Моментальное восстановление | Best For |
|---|---|---|---|
| Простой | Изменения с момента последнего резервного копирования | Нет | Разработка/тестирование, приемлемая потеря данных |
| Длинный | Минимально (обычно несколько минут) | Да | Производственные базы данных, критические данные |
| Массовая регистрация | Изменения с момента последнего резервного копирования журнала | Ограничено во время массовых операций | Времяrary использовать во время массовых операций |
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 Обозреватель объектов, разверните Databases узел
- Щелкните правой кнопкой мыши базу данных, резервную копию которой вы хотите создать.
- Выберите Задач -> Поддерживать
- В Резервное копирование базы данных окно, проверьте имя базы данных
- Выберите Длинный как Тип резервного копирования
4.2.3 Настройка места назначения резервного копирования
- Под Направление, нажмите Удалить очистить путь по умолчанию (при необходимости)
- Нажмите Добавить чтобы указать новое место для резервной копии
- Введите путь к файлу и имя с помощью .bak расширение
- Нажмите OK для подтверждения пункта назначения
4.2.4 Advanced Backup Настройки
- Нажмите Параметры мультимедиа в левой панели
- Выберите параметры резервного копирования:
- Перезаписать все существующие резервные наборы – Заменяет существующие резервные копии
- Добавить к существующему резервному набору – Добавляет к существующему файлу резервной копии
- Нажмите параметры резервного копирования в левой панели
- Настройте дополнительные параметры:
- Сжатие резервной копии – Уменьшает размер файла резервной копии
- Зашифровать резервную копию – Защищает конфиденциальные данные
- Проверьте резервную копию после завершения – Проверяет целостность резервной копии
4.2.5 Выполнение резервного копирования
- Проверьте все настройки в Резервное копирование базы данных окно
- Нажмите OK это сtart процесс резервного копирования
- Дождитесь завершения резервного копирования.
- По завершении резервного копирования появится сообщение об успешном завершении.
- Нажмите 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"- -С: Определяет SQL Server имя экземпляра
- -d: Указывает имя базы данных
- -В: Выполняет запрос и выходит
- -Э: Использует аутентификацию Windows
- -У: Определяет SQL Server имя пользователя для входа
- -П: Указывает пароль для SQL Server Войти
7.1.2 Создание резервной копии с помощью SQLCMD
Для резервного копирования SQL Server используя SQLCMD, выполните следующие действия:
- Открыто Командная строка or PowerShell
- Перейдите в SQL Server каталог tools (обычно добавляется в 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.2 Использование резервной копии базы данных OSQL
OSQL — это устаревшая утилита командной строки для SQL Server. Хотя Microsoft рекомендует использовать SQLCMD, OSQL остаётся доступным для обратной совместимости со старыми скриптами и системами.
7.2.1 Базовый синтаксис OSQL
Синтаксис OSQL аналогичен SQLCMD:
osql -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"- -С: 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 для унифицированной защиты данных
- Автоматизированная проверка и валидация резервных копий
- Поддержка групп постоянной доступности
- Уровень виртуальной машины и уровень приложения SQL Server параметры резервного копирования
8.2 Резервное копирование Barracuda для SQL Server
Barracuda предлагает интегрированные в облако решения для резервного копирования с упрощенным управлением для MS SQL Server резервное копирование операций базы данных.
Ключевые особенности:
- Автоматизированный SQL Server резервное копирование
- Встроенная облачная репликация в Barracuda Cloud Storage
- Глобальная дедупликация и сжатие
- Возможности мгновенного локального восстановления
- Веб-консоль управления
- Поддержка полного, дифференциального и резервного копирования журнала транзакций
- Защита от программ-вымогателей с помощью неизменяемых резервных копий
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 Cohesity DataProtect для 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 База данных
9.1 Понимание процесса восстановления
Восстановление SQL Server База данных восстанавливается из резервных файлов. Процесс восстановления считывает резервную копию и восстанавливает базу данных до состояния, в котором она была до резервной копии.
Важные соображения:
- Восстановление перезаписывает существующую базу данных.
- Пользователи отключаются во время восстановления
- Восстановление должно осуществляться в той же последовательности, что и резервное копирование (полное, затем дифференциальное, затем журналы).
- База данных недоступна во время операции восстановления
9.2 Восстановление полной резервной копии с помощью SSMS
Выполните следующие действия для восстановления полной резервной копии базы данных.
9.2.1 Пошаговый процесс восстановления
- Открыто SQL Server Студия управления и подключитесь к своему серверу
- In Обозреватель объектов, щелкните правой кнопкой мыши Databases
- Выберите Восстановить базу данных
- В Источник раздел, выберите Устройство
- Нажмите ... кнопка для просмотра файлов резервных копий
- Нажмите Добавить и перейдите к вашему .bak-файлу
- Выберите файл резервной копии и нажмите OK
- В Направление раздел, введите имя базы данных
- Проверьте наборы резервных копий для восстановления.
- Нажмите OK это сtarт восстановление
9.2.2 Восстановление параметров и настроек
Нажмите Настройки в левой панели для настройки:
- Перезаписать существующую базу данных (С ЗАМЕНОЙ) – Позволяет восстанавливать существующую базу данных
- Сохранить настройки репликации (WITH KEEP_REPLICATION) – Поддерживает конфигурацию репликации
- Ограничить доступ к восстановленной базе данных (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 Восстановление с помощью команд 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.7 Проверка целостности резервной копии перед восстановлением
Проверка корректности резервной копии без восстановления:
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 Параметры резервного копирования в облаке
Преимущества облачного хранения:
- Хранилище BLOB-объектов Azure: Родной SQL Server интеграция, cost-эффективен при нечастом доступе
- Амазон С3: Высокопрочные, гибкие ярусы хранения
- Облачное хранилище Google: Конкурентоспособные цены, доступность по всему миру
10.3.3 Политики хранения резервных копий
Образец политики хранения:
- Сохраняйте ежедневные резервные копии в течение 7 дней
- Сохраняйте еженедельные резервные копии в течение 4 недель.
- Сохраняйте ежемесячные резервные копии в течение 12 месяцев
- Сохраняйте ежегодные резервные копии в течение 7 лет (соответствие требованиям)
10.4 Сжатие и шифрование резервных копий
Преимущества сжатия:
- Уменьшает размер файла резервной копии на 50–70 %
- Уменьшает время резервного копирования
- Снижает объем хранилища costs
- Уменьшает пропускную способность сети для удаленного резервного копирования
Лучшие практики шифрования:
- Всегда шифруйте резервные копии, содержащие конфиденциальные данные
- Использовать 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 Агент
в ССМС - Щелкните правой кнопкой мыши Вакансии и Новая работа
- Назовите задание (например, «Ежедневное полное резервное копирование»).
- Добавить Шаг с командами резервного копирования T-SQL
- Создайте Назначить для времени выполнения
- Настроить Уведомления для успеха/неудачи
10.6.2 Планы технического обслуживания
SQL Server Планы обслуживания предоставляют визуальный интерфейс для автоматизации резервного копирования:
- Перейдите в Руководство -> Планы обслуживания
- Щелкните правой кнопкой мыши и выберите Мастер плана обслуживания
- Выберите задачи резервного копирования для автоматизации
- Настройте расписание и параметры резервного копирования
- Настройка отчетов и ведения журнала
10.6.3 Оповещения и уведомления о резервном копировании
Настройте уведомления по электронной почте:
- Настройка Database Mail в 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 Availability
Группы Always On Availability распределяют нагрузку резервного копирования между репликами:
- Настройте параметры резервного копирования (первичный, вторичный или любая реплика)
- Перенесите резервные копии на вторичные реплики, чтобы снизить основную рабочую нагрузку
- Используйте резервные копии 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
- Создавай 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.
- Нажмите «Сtart Recovery» и следуйте инструкциям по восстановлению базы данных.
- После процесса восстановления появится новая база данных восстановления 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: Шифруйте резервные копии, содержащие персональные данные, внедряйте политики хранения
- ЗИПАА: Шифруйте PHI в резервных копиях, контролируйте доступ, ведите аудиторские журналы
- PCIDSS: Шифруйте резервные копии данных держателей карт, безопасное хранилище резервных копий
- СОКС: Поддержание целостности резервных копий, политики хранения документов
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: Комплексный мониторинг и оповещение
- Redgate SQL Monitor: Мониторинг и диагностика в реальном времениostICS
- Анализатор производительности базы данных SolarWinds: Мониторинг производительности и резервного копирования
- Idera SQL DiagnostМенеджер по информационным технологиям: Проверка резервного копирования и оповещения
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-накопители значительно быстрее жестких дисков
- Нагрузка на сервер: Высокая активность замедляет резервное копирование
Типичные диапазоны: для полного резервного копирования базы данных объемом 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)tarTing 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 Database Administrator Associate
16.3 Рекомендуемые инструменты и решения
На основе различных сценариев:
Малый бизнес:
- Родной SQL Server резервное копирование по расписанию SQL Server Работа агента
- SQLBackupAndFTP для интеграции с облаком
- Резервное копирование Azure для SQL Server
Средние предприятия:
- SQL Server Планы обслуживания
- Сторонние инструменты, такие как Redgate SQL Backup Pro
- Veeam Backup для SQL Server
Крупные предприятия:
- Quest LiteSpeed для максимального сжатия
- Commvault или Veritas NetBackup для управления резервным копированием предприятия
- Группы Always On Availability для высокой доступности
SQL Server Резервное копирование — основа администрирования баз данных. Правильное планирование, внедрение и тестирование гарантируют сохранность и возможность восстановления данных при необходимости.tarВнедряйте эти передовые практики сегодня, чтобы защитить свои SQL Server базы данных.
Об авторе
Юань Шэн старший администратор баз данных (DBA) с более чем 10-летним опытом работы в SQL Server сред и управления корпоративными базами данных. Он успешно реализовал сотни сценариев восстановления баз данных в финансовых, медицинских и производственных организациях.
Юань специализируется на SQL Server Восстановление баз данных, решения для обеспечения высокой доступности и оптимизация производительности. Его обширный практический опыт включает управление многотерабайтными базами данных, внедрение групп Always On Availability Groups и разработку автоматизированных стратегий резервного копирования и восстановления для критически важных бизнес-систем.
Благодаря своим техническим знаниям и практическому подходу Юань фокусируется на создании всеобъемлющих руководств, которые помогают администраторам баз данных и ИТ-специалистам решать сложные задачи. SQL Server Он эффективно решает задачи. Он всегда в курсе последних новостей. SQL Server выпускает новые версии и развивает технологии баз данных Microsoft, регулярно тестируя сценарии восстановления, чтобы убедиться, что его рекомендации соответствуют реальным передовым практикам.
Есть вопросы о SQL Server Восстановление или требуется дополнительное руководство по устранению неполадок в базе данных? Юань приветствует отзывы и предложения для улучшения этих технических ресурсов.