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

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

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

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

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

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

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

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

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

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

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

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

создание резервных копий
Процесс создания резервной копии [существительного] путем копирования записей данных из базы данных SQL Server или записей журналов из журнала транзакций.

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

устройство резервного копирования
Диск или ленточное устройство, на которые записываются резервные копии SQL Server для последующего восстановления. Резервные копии SQL Server также можно записать в Хранилище BLOB-объектов Azure, а формат URL-адреса используется для указания назначения и имени файла резервной копии. Дополнительные сведения см. в статье sql Server Backup and Restore with Microsoft Хранилище BLOB-объектов Azure.

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

резервное копирование данных
Резервная копия данных всей базы данных (резервная копия базы данных), части базы данных (частичная резервная копия) или набора файлов данных или файловых групп (резервная копия файлов).

резервное копирование базы данных
Резервная копия базы данных. Полные резервные копии базы данных отображают состояние всей базы данных на момент завершения резервного копирования. Разностные резервные копии базы данных содержат только изменения базы данных с момента последнего полного резервного копирования.

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

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

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

recover
Для возврата базы данных в стабильное и согласованное состояние.

recovery
Фаза запуска или восстановления базы данных, которая приводит базу данных в состояние согласованности транзакций.

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

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

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

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

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

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

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

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

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

Рекомендации

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

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

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

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

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

Операции резервного копирования и восстановления выполняются в контексте модели восстановления. Модель восстановления является свойством базы данных, которое задает, как выполняется управление журналом транзакций. Таким образом, модель восстановления базы данных определяет, какие типы резервных копий и сценарии восстановления поддерживаются для базы данных и каким будет размер резервных копий журнала транзакций. Обычно база данных использует простую модель восстановления или модель полного восстановления. Модель полного восстановления может быть дополнена путем переключения на модель с неполным протоколированием перед выполнением массовых операций. Основные сведения о моделях восстановления и их влиянии на управление журналом транзакций см. в разделе Журнал транзакций (SQL Server).

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

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

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

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

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

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

  Если изменения часты, учтите следующее.

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

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

• Касаются ли обычно изменения небольшой или же значительной части базы данных?

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

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

• За какой прошлый период компании нужны резервные копии?

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

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

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

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

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

Сведения о ограничениях параллелизма во время резервного копирования см. в разделе "Обзор резервного копирования" (SQL Server).

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

Тестирование резервных копий

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

Проверка стабильности и согласованности носителя

Используйте параметры проверки, предоставляемые служебными программами резервного копирования (команда BACKUP T-SQL, планы обслуживания SQL Server, программное обеспечение резервного копирования или решение и т. д.). Пример см. в разделе [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Используйте дополнительные функции, например BACKUP CHECKSUM, чтобы выявить проблемы с самим носителем резервного копирования. Дополнительные сведения см. в статье Возможные ошибки носителей во время резервного копирования и восстановления (SQL Server)

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

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

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

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

-- Create the backup_restore_progress_trace extended event esssion
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  

Пример выходных данных расширенного события

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

• Создание плана обслуживания

• Создание задания

• Планирование задания

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

• Определение логического устройства резервного копирования для файла диска (SQL Server)

• Определение логического устройства резервного копирования для ленточного накопителя (SQL Server)

• Указание диска или ленты в качестве места назначения архивации (SQL Server)

• Удаление устройства резервного копирования (SQL Server)

• Назначение срока хранения резервной копии (SQL Server)

• Просмотр содержимого ленты резервного копирования или файла (SQL Server)

• Просмотр файлов данных и журналов в резервном наборе данных (SQL Server)

• Просмотр свойств и содержимого логического устройства резервного копирования (SQL Server)

• Восстановление резервной копии с устройства (SQL Server)

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

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

• Создание полной резервной копии базы данных (SQL Server)

• Создание резервной копии журнала транзакций (SQL Server)

• Резервное копирование файлов и файловых групп (SQL Server)

• Создание разностной резервной копии базы данных (SQL Server)

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

• Использование регулятора ресурсов для ограничения загрузки ЦП при сжатии резервной копии (компонент Transact-SQL)

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

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

• Определение того, продолжает ли выполняться операция архивации или восстановления после возникновения ошибки (SQL Server)

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

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

• Restore a Database Backup Using SSMS

• Восстановление базы данных в новое расположение (SQL Server)

• Восстановление разностной резервной копии базы данных (SQL Server)

• Восстановление файлов и файловых групп (SQL Server)

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

• Восстановление резервной копии базы данных в простой модели восстановления (Transact-SQL)

• Восстановление базы данных до точки отказа в модели полного восстановления (Transact-SQL)

• Восстановление файлов и файловых групп по существующим файлам (SQL Server)

• Восстановление файлов в новое расположение (SQL Server)

• Восстановление базы данных master (Transact-SQL)

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

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

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

• Восстановление резервной копии журнала транзакций (SQL Server)

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

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

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

• Перезапуск прерванной операции восстановления (язык Transact-SQL)

• Восстановление базы данных без восстановления данных (Transact-SQL)

Дополнительные сведения и ресурсы

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