Резервное копирование и восстановление баз данных SQL Server

Область применения:SQL Server

В этой статье описываются преимущества резервного копирования баз данных SQL Server, приводятся базовые термины резервного копирования и восстановления, а также рассматриваются стратегии резервного копирования и восстановления, а также вопросы безопасности SQL Server.

Компонент резервного копирования и восстановления SQL Server обеспечивает важную защиту критически важных данных, хранящихся в ваших базах данных SQL Server. Чтобы минимизировать риск катастрофической потери данных, регулярно делайте резервные копии баз данных, чтобы сохранить изменения в данных. Хорошо спланированная стратегия резервного копирования и восстановления помогает защитить базы данных от потери данных, вызванных множеством видов отказов. Проверьте свою стратегию, восстановив набор резервных копий, а затем свою базу данных, чтобы быть готовым реагировать на катастрофу.

Помимо локального хранилища, SQL Server также поддерживает резервное копирование и восстановление из Хранилище BLOB-объектов Azure. Дополнительные сведения см. в статье Резервное копирование и восстановление SQL Server с помощью хранилища BLOB-объектов Azure. Для файлов базы данных, хранящихся в хранилище BLOB-объектов Azure, SQL Server 2016 (13.x) позволяет использовать моментальные снимки Azure для практически мгновенного резервного копирования и более быстрого восстановления. Дополнительные сведения см. в статье "Резервные копии моментальных снимков файлов" для файлов базы данных в Azure. Azure также предоставляет возможности резервного копирования корпоративного класса для SQL Server на виртуальных машинах Azure. Это полностью управляемое решение для резервного копирования обеспечивает поддержку групп доступности Always On, долгосрочного хранения, точечного восстановления, а также централизованного управления и мониторинга. Дополнительные сведения см. в статье о резервном копировании SQL Server на виртуальных машинах Azure.

Зачем выполнять резервное копирование

• Резервное копирование ваших баз данных SQL Server, выполнение процедур тестового восстановления на резервных копиях и хранение копий в безопасном удалённом месте защищает вас от потенциально катастрофической потери данных. Резервное копирование — единственный способ защитить данные.

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

  • Сбой носителя данных.

  • ошибки пользователей (например, удаление таблицы по ошибке);

  • сбои оборудования (например, поврежденный дисковый накопитель или безвозвратная потеря данных на сервере);

  • Стихийные бедствия. Используя SQL Server Backup для Хранилище BLOB-объектов Azure, вы можете создать резервную копию вне офиса в другом регионе, отличном от локального расположения, чтобы использовать её в случае природного бедствия, которое затронет ваше местоположение.

• Кроме того, резервные копии базы данных полезны для обычных административных целей, таких как копирование базы данных с одного сервера на другой, настройка групп доступности AlwaysOn или зеркального отображения базы данных и архивация.

Глоссарий терминов, связанных с резервным копированием

Стратегии резервного копирования и восстановления

Вам нужно настраивать стратегии резервного копирования и восстановления под вашу среду и доступные ресурсы. Надежное восстановление требует стратегии резервного копирования и восстановления. Хорошо продуманная стратегия балансирует бизнес-требования к максимальной доступности данных и минимальным потерям данных с затратами на поддержание и хранение резервных копий.

Стратегия резервирования и восстановления состоит из части, относящейся к резервированию, и части, относящейся к восстановлению. Часть резервного копирования определяет тип и частоту резервных копий, тип и скорость необходимого оборудования, как тестировать резервные копии, а также где и как хранить носители резервного копирования (включая вопросы безопасности). Часть восстановления определяет, кто отвечает за восстановление, как выполнять восстановления для достижения целей по доступности базы данных и минимальной потере данных, а также как тестировать восстановления.

Эффективная стратегия резервного копирования и восстановления требует тщательного планирования, внедрения и тестирования. Требуется тестирование. У вас нет резервной стратегии, пока вы не успешно восстановите резервные копии во всех комбинациях, включённых в вашу стратегию, и не проверите каждую восстановленную базу данных на физическую согласованность. Рассмотрите несколько факторов, включая:

• Цели вашей организации в отношении рабочих баз данных, особенно требования к доступности и защите данных от потери или повреждения.

• Свойства каждой базы данных: размер, типичное использование, характер содержимого, требования к данным и т. д.

• Ограничения ресурсов, таких как оборудование, персонал, пространство для хранения носителей резервного копирования, физическая безопасность хранимого носителя и т. д.

Рекомендации по лучшим практикам

Не предоставляйте аккаунтам, выполняющим операции резервного копирования или восстановления, больше прав, чем необходимо. Дополнительные сведения о конкретных разрешениях см. в разделах резервное копирование и восстановление. Зашифруйте резервные копии базы данных и, если возможно, сжайте их.

Используйте согласованные расширения файлов, чтобы резервные копии было проще идентифицировать и управлять. SQL Server не требует и не применяет эти расширения, но согласованность помогает в операционных задачах, таких как настройка антивирусных исключений для резервных копий. Дополнительные сведения см. в статье "Настройка антивирусного программного обеспечения для работы с SQL Server".

• Файлы резервных копий базы данных должны иметь это .BAK расширение.
• Файлы резервного копирования журналов должны иметь расширение .TRN.

Используйте отдельное хранилище

Размещайте резервные копии базы данных в отдельном физическом месте или на отдельном устройстве от файлов базы данных. Когда физический диск, на котором хранятся ваши базы данных, выходит из строя или выходит из строя, восстановление зависит от вашей способности получить доступ к отдельному диску или удалённому устройству, где хранятся резервные копии. Вы можете создать несколько логических томов или разделов с одного и того же физического диска. Тщательно изучите раздел диска и логические раскладки томов перед выбором места хранения резервных копий.

Выбор подходящей модели восстановления

Операции резервного копирования и восстановления выполняются в контексте модели восстановления. Модель восстановления является свойством базы данных, которое задает, как выполняется управление журналом транзакций. Таким образом, модель восстановления базы данных определяет, какие типы сценариев резервного копирования и восстановления поддерживает база данных, а также размер резервных копий журнала транзакций. Обычно база данных использует простую модель восстановления или модель полного восстановления. Вы можете дополнить модель полного восстановления, переключившись перед операциями массовой загрузки на модель восстановления с неполным протоколированием. Общие сведения об этих моделях восстановления и о том, как они влияют на управление журналами транзакций, см. в журнале транзакций.

Лучший выбор модели восстановления базы данных зависит от требований вашего бизнеса. Чтобы избежать управления журналом транзакций и упростить резервное копирование и восстановление, используйте простую модель восстановления. Чтобы свести к минимуму риск потери работы за счет административных накладных расходов, используйте модель полного восстановления. Чтобы минимизировать влияние на размер журнала во время операций с массовым логированием и при этом сохранять восстановление этих операций, используйте модель восстановления с массовым логированием. Для получения информации о влиянии моделей восстановления на резервное копирование и восстановление см. обзор резервного копирования (SQL Server).

Создание стратегии резервного копирования

После того как вы выберете модель восстановления, соответствующую вашим бизнес-требованиям для конкретной базы данных, спланируйте и реализуйте стратегию соответствующего резервного копирования. Лучшая стратегия резервного копирования зависит от нескольких факторов. Особенно важны следующие факторы:

• Сколько часов в сутки нужно приложениям для доступа к базе данных?

  Если есть предсказуемый непиковый период, стоит запланировать полные резервные копии базы данных на этот период.

• Насколько часты и вероятны изменения и обновления?

  Если изменения происходят часто, рассмотрим:

  • В рамках простой модели восстановления вы можете планировать дифференциальные резервные копии между полными резервными копиями базы данных. Разностная резервная копия сохраняет только те изменения, которые были внесены с момента последнего полного резервного копирования.

  • В рамках модели полного восстановления вы можете планировать частые резервные копии журналов. Планирование разностного резервного копирования между полными резервными копиями сокращает время восстановления путем сокращения количества резервных копий журналов, которые необходимо восстанавливать после восстановления данных.

• Вероятны ли изменения только в небольшой части базы данных или в значительной части?

  Для большой базы данных, где изменения сосредоточены в подмножестве файлов или групп файлов, могут быть полезны частичные или полные резервные копии файлов. Дополнительные сведения см. в статьях о частичных резервных копиях (SQL Server) и полных резервных копий файлов (SQL Server).

• Сколько места на диске требует полноценная резервная копия базы данных?

• За какой период в прошлом вашему бизнесу необходимо хранить резервные копии?

  Убедитесь, что у вас есть правильный график резервного копирования, соответствующий потребностям приложения и бизнес-требованиям. По мере старения резервных копий риск потери данных растёт, если нет способа восстановить все данные до точки отказа. Прежде чем удалять старые резервные копии из-за ограничений хранилища, подумайте, нужно ли вам восстановление данных за столь давний период.

Оценка размера полной резервной копии базы данных

Прежде чем реализовать стратегию резервного копирования и восстановления, оцените, сколько пространства на диске занимает полная резервная копия базы данных. При выполнении операции резервного копирования данные, содержащиеся в базе данных, копируются в файл резервной копии. Резервная копия содержит только реальные данные базы данных, а не неиспользуемое пространство. Поэтому резервная копия обычно меньше, чем база данных. Чтобы оценить размер полной резервной копии базы данных, используйте sp_spaceused процедуру сохранения в системе. Дополнительные сведения см. в sp_spaceused.

Создание расписания резервного копирования

Операция резервного копирования минимально влияет на выполнение транзакций, поэтому вы можете запускать резервные копии во время обычных операций. Резервное копирование SQL Server можно выполнять с минимальным воздействием на рабочие нагрузки.

После того как вы определитесь, какие типы резервных копий вам нужны и как часто выполнять каждый тип, планируйте регулярные резервные копии в рамках плана обслуживания базы данных. Дополнительные сведения о планах обслуживания и об их создании для резервных копий баз данных и журналов см. в разделе Use the Maintenance Plan Wizard.

Проверка резервных копий

У вас нет стратегии восстановления, пока вы не протестируете резервные копии. Тщательно протестируйте свою стратегию резервного копирования для каждой базы данных, восстановив копию базы данных на тестовой системе. Необходимо протестировать восстановление каждого типа резервной копии, которую планируется использовать. После восстановления резервной копии проверьте DBCC CHECKDB базу данных, чтобы убедиться, что носитель резервного копирования не повреждён.

Проверка стабильности мультимедиа и согласованности

Используйте опции верификации, предоставляемые утилитами резервного копирования (BACKUPкоманда T-SQL, планы обслуживания SQL Server, ваше программное обеспечение или решение для резервного копирования и так далее). Для примера см. RESTORE Утверждения - VERIFYONLY.

Используйте расширенные функции, такие как BACKUP CHECKSUM, чтобы обнаруживать проблемы с самим носителем резервной копии. Дополнительные сведения см. в разделе Поссимые ошибки мультимедиа во время резервного копирования и восстановления (SQL Server).

Стратегия резервного копирования и восстановления документов

Документируйте процедуры резервного копирования и восстановления, а копию документации храните в своей книге запусков.

Также следует вести руководство по эксплуатации для каждой базы данных. Это руководство по эксплуатации должно фиксировать расположение резервных копий, имена устройств резервного копирования (если таковые имеются) и время, необходимое для восстановления тестовых резервных копий.

Риск безопасности восстановления резервных копий из ненадежных источников

В этом разделе описывается риск безопасности, связанный с восстановлением резервных копий из ненадежных источников в любую среду SQL Server, включая локальную среду, Управляемый экземпляр SQL Azure, SQL Server на виртуальных машинах Azure и любую другую среду.

Почему это важно

Восстановление файлов резервного копирования SQL (.bak) приводит к потенциальному риску при возникновении резервной копии из ненадежного источника. Риск безопасности усугубляется, когда среда SQL Server имеет несколько экземпляров, так как она усиливает область угрозы. Хотя резервные копии, оставшиеся в пределах доверенной границы, не вызывают проблем безопасности, восстановление вредоносной резервной копии может скомпрометировать безопасность всей среды.

Вредоносный .bak файл может:

• Возьмите на себя весь экземпляр SQL Server.
• Повышение привилегий и получение несанкционированного доступа к базовому узлу или виртуальной машине.

Эта атака происходит перед выполнением любых проверочных скриптов или проверок безопасности, что делает её чрезвычайно опасной. Восстановление ненадежной резервной копии эквивалентно запуску ненадежных приложений на критическом сервере или виртуальной машине и внедрению произвольного выполнения кода в среду.

Лучшие практики

Выполните следующие рекомендации по обеспечению безопасности резервного копирования, чтобы снизить угрозу для сред SQL Server:

• Восстановление резервных копий рассматривается как операция с высоким риском.
• Уменьшите область воздействия угроз с помощью изолированных экземпляров.
• Разрешить только доверенные резервные копии: никогда не восстанавливайте резервные копии из неизвестных или внешних источников.
• Разрешите только те резервные копии, которые остаются внутри доверенной зоны: убедитесь, что резервные копии поступают из этой зоны.
• Не обходите механизмы безопасности ради удобства.
• Включите аудит на уровне сервера для записи событий резервного копирования и восстановления и устранения ошибки аудита.

Мониторинг хода выполнения с помощью XEvent

Операции резервного копирования и восстановления могут занять много времени из-за размера базы данных и сложности выполняемых операций. Когда возникают проблемы с одной из операций, используйте расширенное backup_restore_progress_trace событие для мониторинга прогресса в реальном времени. Дополнительные сведения о расширенных событиях см. в обзоре расширенных событий.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Образец вывода из Extended Event

Дополнительные сведения о задачах резервного копирования

• Создание плана обслуживания
• Создание задания агента SQL Server в SQL Server Management Studio
• Настройка расписания для задания агента SQL Server

Работа с устройствами резервного копирования и носителями резервного копирования

• Определение логического устройства резервного копирования для файла диска (SQL Server)
• Определение логического устройства резервного копирования для ленточного диска (SQL Server)
• Указание места назначения резервного копирования диска или ленты (SQL Server)
• Удаление устройства резервного копирования (SQL Server)
• Установка даты окончания срока действия резервной копии (SQL Server)
• Просмотр содержимого ленты или файла резервной копии (SQL Server)
• Просмотр файлов данных и журналов в резервном наборе данных (SQL Server)
• Просмотр свойств и содержимого логического устройства резервного копирования (SQL Server)
• Восстановление резервной копии с устройства (SQL Server)

Создание резервных копий

Для частичного резервного копирования или резервного копирования типа copy-only используйте оператор Transact-SQL BACKUP с параметром PARTIAL или COPY_ONLY соответственно.

Использование SSMS

• Создание полной резервной копии базы данных
• Резервное копирование журнала транзакций
• Резервное копирование файлов и групп файлов
• Создание разностной резервной копии базы данных (SQL Server)

Использование T-SQL

• Используйте Resource Governor для ограничения использования процессора с помощью резервного сжатия
• Резервное копирование журнала транзакций при повреждении базы данных (SQL Server)
• Включение или отключение контрольных сумм резервного копирования во время резервного копирования или восстановления (SQL Server)
• Указание резервного копирования или восстановления для продолжения или остановки после ошибки

Восстановление резервных копий данных

Использование SSMS

• Восстановление резервной копии базы данных с помощью SSMS
• Восстановление базы данных в новом расположении (SQL Server)
• Восстановление разностной резервной копии базы данных (SQL Server)
• Восстановить файлы и группы файлов (SQL Server)

Использование T-SQL

• Восстановить резервную копию базы данных по простой модели восстановления
• Восстановление базы данных до точки сбоя — полное восстановление
• Восстановление файлов и файловых групп поверх существующих файлов (SQL Server)
• Восстановить файлы в новое место (SQL Server)
• Восстановить основную базу данных

Восстановление журналов транзакций (модель полного восстановления)

Использование SSMS

• Восстановление базы данных в помеченную транзакцию (SQL Server Management Studio)
• Восстановление резервного копирования журнала транзакций (SQL Server)
• Восстановить базу данных SQL Server на момент времени (модель полного восстановления)

Использование T-SQL

• Восстановление базы данных SQL Server на момент времени (модель полного восстановления)
• Перезапуск прерванной операции восстановления
• Восстановление базы данных без восстановления данных

Связанный контент

• Обзор резервного копирования (SQL Server)
• Обзор восстановления и восстановления (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Операторы (Transact-SQL)
• Создание и восстановление резервных копий баз данных служб Analysis Services
• Резервное копирование и восстановление полнотекстовых каталогов и индексов
• Резервное копирование и восстановление реплицированных баз данных
• Журнал транзакций
• Модели восстановления (SQL Server)
• Наборы носителей, семейства носителей и резервные наборы данных (SQL Server)