Резервное копирование и восстановление баз данных SQL ServerBack Up and Restore of SQL Server Databases

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

В этой статье описаны преимущества резервного копирования баз данных SQL ServerSQL Server, основные условия резервного копирования и восстановления, а также приведены стратегии резервного копирования и восстановления для SQL ServerSQL Server и рассмотрены вопросы безопасности, связанные с резервным копированием и восстановлением в SQL ServerSQL Server.This article describes the benefits of backing up SQL ServerSQL Server databases, basic backup and restore terms, and introduces backup and restore strategies for SQL ServerSQL Server and security considerations for SQL ServerSQL Server backup and restore.

Нужны пошаговые инструкции?Looking for step by step instructions? Этот раздел не содержит конкретные шаги по выполнению резервного копирования.This topic does not provide any specific steps for how to do a back up! Если вы хотите перейти непосредственно к сведениям о создании резервных копий, прокрутите эту страницу вниз до раздела ссылок, упорядоченного по задачам резервного копирования и по потребности в SSMS или T-SQL.If you want to get right to actually backing up, scroll down this page to the links section, organized by backup tasks and whether you want to use SSMS or T-SQL.

Компонент резервного копирования и восстановления SQL Server обеспечивает необходимую защиту важных данных, которые хранятся в базах данных SQL ServerSQL Server .The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL ServerSQL Server databases. Чтобы минимизировать риск необратимой потери данных, необходимо регулярно создавать резервные копии баз данных, в которых будут сохраняться производимые изменения данных.To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. Хорошо продуманная стратегия резервного копирования и восстановления защищает базы от потери данных при повреждениях, происходящих из-за различных сбоев.A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Проверьте выбранную стратегию, выполнив восстановление баз данных из набора резервных копий; это поможет эффективно среагировать на реальные проблемы.Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

В дополнение к локальному хранилищу для хранения резервных копий SQL Server поддерживает резервное копирование и восстановление из службы хранилища больших двоичных объектов Windows Azure.In addition to local storage for storing the backups, SQL Server also supports backup to and restore from the Windows Azure Blob Storage Service. Дополнительные сведения см. в разделе Резервное копирование и восстановление SQL Server с помощью службы хранилища BLOB-объектов Microsoft Azure.For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. Для файлов базы данных, сохраненных в службе хранилища больших двоичных объектов Microsoft Azure, SQL Server 2016 (13.x)SQL Server 2016 (13.x) предоставляет возможность использовать моментальные снимки Azure для практически мгновенного создания резервных копий и ускорения восстановления.For database files stored using the Microsoft Azure Blob storage service, SQL Server 2016 (13.x)SQL Server 2016 (13.x) provides the option to use Azure snapshots for nearly instantaneous backups and faster restores. Дополнительные сведения см. в разделе Резервные копии моментальных снимков файлов для файлов базы данных в Azure.For more information, see File-Snapshot Backups for Database Files in Azure.

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

  • Создание резервных копий баз данных SQL ServerSQL Server , выполнение проверочных процедур восстановления резервных копий и хранение резервных копий в безопасном месте вне рабочей площадки помогают предотвратить возможную необратимую потерю данных.Backing up your SQL ServerSQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss. Резервное копирование — единственный способ защитить данные.Backing up is the only way to protect your data.

    При правильном создании резервных копий баз данных можно будет восстановить данные после многих видов сбоев, включая следующие:With valid backups of a database, you can recover your data from many failures, such as:

    • сбой носителя;Media failure.
    • ошибки пользователей (например, удаление таблицы по ошибке);User errors, for example, dropping a table by mistake.
    • сбои оборудования (например, поврежденный дисковый накопитель или безвозвратная потеря данных на сервере);Hardware failures, for example, a damaged disk drive or permanent loss of a server.
    • стихийные бедствия.Natural disasters. При выполнении резервного копирования SQL Server в службу хранилища больших двоичных объектов Windows Azure можно создать резервную копию в регионе, отличном от региона своего фактического локального расположения, на случай возникновения природных катастроф в своем регионе.By using SQL Server Backup to Windows Azure Blob storage service, you can create an off-site backup in a different region than your on-premises location, to use in the event of a natural disaster affecting your on-premises location.
  • Кроме того, резервные копии баз данных полезны и при выполнении повседневных административных задач, например для копирования баз данных с одного сервера на другой, настройки Группы доступности AlwaysOnAlways On availability groups или зеркального отображения баз данных и архивирования.Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Группы доступности AlwaysOnAlways On availability groups or database mirroring, and archiving.

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

создание резервных копийback up [verb]
Процесс создания резервной копии путем копирования записей данных из базы данных SQL ServerSQL Server или записей журнала из ее журнала транзакций.The process of creating a backup [noun] by copying data records from a SQL ServerSQL Server database, or log records from its transaction log.

резервная копияbackup [noun]
Копия данных, которая может использоваться для восстановления данных в случае возникновения ошибки.A copy of data that can be used to restore and recover the data after a failure. Резервные копии баз данных также могут использоваться для восстановления копии базы данных в новом расположении.Backups of a database can also be used to restore a copy the database to a new location.

устройстворезервного копирования backup device
Диск или ленточное устройство, на которые записываются резервные копии SQL Server для последующего восстановления.A disk or tape device to which SQL Server backups are written and from which they can be restored. Резервные копии SQL Server можно также записать в службу хранилища больших двоичных объектов Windows Azure, а формат URL используется, чтобы указать назначение и имя файла резервной копии.SQL Server backups can also be written to a Windows Azure Blob storage service, and URL format is used to specify the destination and the name of the backup file.. Дополнительные сведения см. в разделе Резервное копирование и восстановление SQL Server с помощью службы хранилища BLOB-объектов Microsoft Azure.For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

носитель данных резервной копииbackup media
Одна магнитная лента или несколько или один файл на диске или несколько, в которые были записаны одна резервная копия или несколько.One or more tapes or disk files to which one or more backups have been written.

резервное копирование данныхdata backup
Резервная копия данных всей базы данных (резервная копия базы данных), части базы данных (частичная резервная копия) или набора файлов данных или файловых групп (резервная копия файлов).A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

резервное копирование базы данныхdatabase backup
Резервная копия базы данных.A backup of a database. Полные резервные копии базы данных отображают состояние всей базы данных на момент завершения резервного копирования.Full database backups represent the whole database at the time the backup finished. Разностные резервные копии базы данных содержат только изменения базы данных с момента последнего полного резервного копирования.Differential database backups contain only changes made to the database since its most recent full database backup.

разностная резервная копияdifferential backup
Резервная копия данных, основанная на последней полной или частичной резервной копии базы данных или набора файлов данных или файловых групп (базовой копии для разностного копирования), которая содержит только данные, измененные по сравнению с базовой копией для разностного копирования.A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data that has changed since that base.

полная резервная копияfull backup
Резервная копия, которая содержит все данные заданной базы данных или наборов файлов или файловых групп, а также журналов для обеспечения возможности последующего восстановления этих данных.A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

резервная копия журналовlog backup
Резервная копия журналов транзакций, включающая все записи журнала, не входившие в предыдущую резервную копию журналов.A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (модель полного восстановления)(full recovery model)

восстановитьrecover
Для возврата базы данных в стабильное и согласованное состояние.To return a database to a stable and consistent state.

recoveryrecovery
Фаза запуска или восстановления базы данных, которая приводит базу данных в состояние согласованности транзакций.A phase of database startup or of a restore with recovery that brings the database into a transaction-consistent state.

модель восстановленияrecovery model
Свойство базы данных, с помощью которого выполняется управление обслуживанием журналов транзакций в базе данных.A database property that controls transaction log maintenance on a database. Существует три модели восстановления: простая модель восстановления, модель полного восстановления и модель восстановления с неполным протоколированием.Three recovery models exist: simple, full, and bulk-logged. Модель восстановления базы данных определяет требования к резервному копированию и восстановлению.The recovery model of database determines its backup and restore requirements.

восстановлениеrestore
Многоэтапный процесс, в ходе которого все данные и страницы журнала копируются из указанной резервной копии SQL ServerSQL Server в определенную базу данных, а затем выполняется накат всех фиксированных транзакций, записанных в резервной копии журнала, путем внесения новых данных на основе зарегистрированных изменений.A multi-phase process that copies all the data and log pages from a specified SQL ServerSQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

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

Операции резервирования и восстановления данных следует адаптировать под конкретную среду с учетом доступных ресурсов.Backing up and restoring data must be customized to a particular environment and must work with the available resources. Таким образом, для надежной работы операций резервирования и восстановления необходима стратегия резервирования и восстановления.Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. Правильно созданная стратегия резервирования и восстановления увеличивает доступность данных и уменьшает их потери, учитывая требования пользователей.A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.

Важно!

Базу данных и резервные копии следует размещать на отдельных устройствах резервного копирования.Place the database and backups on separate devices. В противном случае при сбое устройства, содержащего базу данных, резервные копии окажутся недоступными.Otherwise, if the device containing the database fails, your backups will be unavailable. Кроме того, размещение данных и их резервных копий на различных устройствах повышает производительность ввода-вывода как при записи резервных копий, так и в процессе эксплуатации базы данных.**Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.**

Стратегия резервирования и восстановления состоит из части, относящейся к резервированию, и части, относящейся к восстановлению.A backup and restore strategy contains a backup portion and a restore portion. Часть, относящаяся к резервированию, определяет тип и частоту создания резервных копий, тип и скоростные характеристики оборудования, необходимого для их создания, способ проверки резервных копий, а также местонахождение и тип носителя резервных копий (включая и вопросы безопасности).The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). Часть, относящаяся к восстановлению, определяет ответственного за проведение операций восстановления, а также методы их проведения, позволяющие удовлетворить требования пользователей по доступности данных и минимизации их потерь.The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet your goals for availability of the database and for minimizing data loss. Рекомендуется документировать процедуры резервирования и восстановления и хранить копию этой документации в документации по задаче.We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.

Разработка эффективной стратегии резервирования и восстановления требует тщательного планирования, реализации и тестирования.Designing an effective backup and restore strategy requires careful planning, implementation, and testing. Необходимо тестирование.Testing is required. До тех пор пока не были успешно восстановлены все резервные копии во всех сочетаниях, вовлеченных в стратегию восстановления, нет и стратегии резервного копирования.You do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy. Необходимо оценить ряд факторов.You must consider a variety of factors. следующие основные параметры.These include the following:

  • Производственные задачи организации, относящиеся к базе данных, особенно требования к доступности данных и их защите от потери.The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.

  • Свойства каждой базы данных: размер, типичное использование, характер содержимого, требования к данным и т. д.The nature of each of your databases: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.

  • Ограничения на ресурсы, например: оборудование, персонал, пространство для хранения носителей резервных копий, физическая безопасность этих носителей и так далее.Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

Влияние модели восстановления на резервное копирование и восстановлениеImpact of the recovery model on backup and restore

Операции резервного копирования и восстановления выполняются в контексте моделей восстановления.Backup and restore operations occur within the context of a recovery model. Модель восстановления является свойством базы данных, которое задает, как выполняется управление журналом транзакций.A recovery model is a database property that controls how the transaction log is managed. Также модель восстановления базы данных определяет, какие типы резервных копий и сценарии восстановления поддерживаются для базы данных.Also, the recovery model of a database determines what types of backups and what restore scenarios are supported for the database. Обычно база данных использует простую модель восстановления или модель полного восстановления.Typically a database uses either the simple recovery model or the full recovery model. Модель полного восстановления может быть дополнена путем переключения на модель с неполным протоколированием перед выполнением массовых операций.The full recovery model can be supplemented by switching to the bulk-logged recovery model before bulk operations. Основные сведения о моделях восстановления и их влиянии на управление журналом транзакций см. в разделе Журнал транзакций (SQL Server).For an introduction to these recovery models and how they affect transaction log management, see The Transaction Log (SQL Server)

Лучший выбор модели восстановления базы данных зависит от бизнес-требований.The best choice of recovery model for the database depends on your business requirements. Чтобы избежать управления журналом транзакций и упростить резервное копирование и восстановление, используйте простую модель восстановления.To avoid transaction log management and simplify backup and restore, use the simple recovery model. Чтобы снизить вероятность потери результатов работы ценой увеличения административных издержек, используйте модель полного восстановления.To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. Дополнительные сведения о влиянии моделей восстановления на создание резервных копий и восстановление см. в разделе Общие сведения о резервном копировании (SQL Server).For information about the effect of recovery models on backup and restore, see Backup Overview (SQL Server).

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

После того как выбрана модель восстановления, соответствующая бизнес-требованиям определенной базы данных, необходимо спланировать и выполнить соответствующую стратегию резервного копирования.After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy. Оптимальная стратегия зависит от многих факторов, среди которых наиболее важны следующие.The optimal backup strategy depends on a variety of factors, of which the following are especially significant:

  • Сколько часов в день приложения имеют доступ к базе данных?How many hours a day do applications have to access the database?

    Если существует прогнозируемый внепиковый период, рекомендуется запланировать полное резервное копирование базы данных именно на этот период.If there is a predictable off-peak period, we recommend that you schedule full database backups for that period.

  • Насколько часты и вероятны изменения и обновления?How frequently are changes and updates likely to occur?

    Если изменения часты, учтите следующее.If changes are frequent, consider the following:

    • В рамках простой модели восстановления рассмотрите возможность запланировать разностное резервное копирование между полными резервными копированиями базы данных.Under the simple recovery model, consider scheduling differential backups between full database backups. Разностная резервная копия сохраняет только те изменения, которые были внесены с момента последнего полного резервного копирования.A differential backup captures only the changes since the last full database backup.

    • В рамках модели полного восстановления следует запланировать частное резервное копирование журналов.Under the full recovery model, you should schedule frequent log backups. Планирование разностного резервного копирования между полными резервными копиями сокращает время восстановления путем сокращения количества резервных копий журналов, которые необходимо восстанавливать после восстановления данных.Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

  • Касаются ли обычно изменения небольшой или же значительной части базы данных?Are changes likely to occur in only a small part of the database or in a large part of the database?

    В большой базе данных, в которой изменения концентрируются в части файлов или файловых групп, полезно частичное резервное копирование или резервное копирование файлов.For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and or file backups can be useful. Дополнительные сведения см. в разделах Частичные резервные копии (SQL Server) и Полные резервные копии файлов (SQL Server).For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).

  • Сколько места на диске требуется для полного резервного копирования базы данных?How much disk space will a full database backup require?

    Оценка размера полной резервной копии базы данныхEstimate the size of a full database backup

    Перед тем как выбрать стратегию резервного копирования и восстановления, необходимо рассчитать, какой объем места на диске необходим для полной резервной копии базы данных.Before you implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. При выполнении операции резервного копирования данные, содержащиеся в базе данных, копируются в файл резервной копии.The backup operation copies the data in the database to the backup file. Резервная копия содержит только фактические данные в базе данных, а не любое неиспользованное пространство.The backup contains only the actual data in the database and not any unused space. Поэтому резервная копия обычно меньше, чем база данных.Therefore, the backup is usually smaller than the database itself. Размер полной резервной копии базы данных вы можете вычислить с помощью системной хранимой процедуры sp_spaceused.You can estimate the size of a full database backup by using the sp_spaceused system stored procedure. Дополнительные сведения см. в разделе sp_spaceused (Transact-SQL).For more information, see sp_spaceused (Transact-SQL).

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

Влияние, оказываемое осуществлением резервного копирования на выполняемые транзакции, минимально, поэтому операции резервного копирования могут выполняться одновременно с выполнением обычных операций.Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. Резервное копирование в SQL ServerSQL Server можно выполнять с минимальным влиянием на рабочие нагрузки.You can perform a SQL ServerSQL Server backup with minimal effect on production workloads.

Сведения об ограничениях параллелизма во время резервного копирования см. в разделе Общие сведения о резервном копировании (SQL Server).For information about concurrency restrictions during backup, see Backup Overview (SQL Server).

После принятия решения о том, какой тип резервного копирования необходим и как часто его выполнять, рекомендуется запланировать регулярное резервное копирование как часть плана обслуживания базы данных.After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. Дополнительные сведения о планах обслуживания и об их создании для резервных копий баз данных и журналов см. в разделе Use the Maintenance Plan Wizard.For information about maintenance plans and how to create them for database backups and log backups, see Use the Maintenance Plan Wizard.

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

Можно сказать, что стратегия восстановления отсутствует, пока резервные копии не протестированы.You do not have a restore strategy until you have tested your backups. Очень важно полностью протестировать стратегию резервного копирования для каждой базы данных, восстанавливая копию базы данных в систему тестирования.It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. Необходимо протестировать восстановление каждого типа резервной копии, которую планируется использовать.You must test restoring every type of backup that you intend to use.

Рекомендуется вести руководство по эксплуатации для каждой базы данных.We recommend that you maintain an operations manual for each database. Такое руководство по эксплуатации должно указывать расположение резервных копий, имена устройств резервного копирования (если есть), время, требуемое для восстановления тестовой резервной копии.This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.

Отслеживание хода выполнения с помощью xEventMonitor progress with xEvent

Операции резервного копирования и восстановления могут выполняться в течение длительного времени из-за размера базы данных и сложности выполняемых операций.Backup and restore operations can take a considerable amount of time due to the size of a database and the complexity of the operations involved. При возникновении проблемы с любой из операций можно использовать расширенное событие backup_restore_progress_trace для отслеживания хода выполнения в реальном времени.When issues arise with either operation, you can use the backup_restore_progress_trace extended event to monitor progress live. Дополнительные сведения о расширенных событиях см. в разделе Расширенные события.For more information about extended events, see extended events.

Предупреждение

С помощью расширенных событий backup_restore_progress_trace можно повысить производительность и существенно сэкономить дисковое пространство.Using the backup_restore_progress_trace extended event can cause a performance issue and consume a significant amount of disk space. Используйте эти события в течение короткого периода времени, проявляйте внимание и тщательно проверяйте свои изменения перед их реализацией в рабочей среде.Use for short periods of time, exercise caution, and test thoroughly before implementing in production.

-- 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  

Пример выходных данных расширенного событияSample output from extended event

Пример выходных данных события резервного копирования xevent Пример выходных данных события восстановления xeventExample of back up xevent output Example of restore xevent output

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

Работа с устройствами резервного копирования и носителями резервных копийWorking with backup devices and backup media

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

Примечание.Note! Для создания частичной резервной копии или резервной копии только для копирования используется инструкция Transact-SQLTransact-SQLBACKUP с параметром PARTIAL или COPY_ONLY.For partial or copy-only backups, you must use the Transact-SQLTransact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

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

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

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

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

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

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

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

Общие сведения о резервном копировании (SQL Server) Backup Overview (SQL Server)
Обзор процессов восстановления (SQL Server) Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Создание и восстановление резервных копий баз данных служб Analysis Services Backup and Restore of Analysis Services Databases
Создание резервных копий и восстановление полнотекстовых каталогов и индексов Back Up and Restore Full-Text Catalogs and Indexes
Создание резервных копий реплицируемых баз данных и восстановление из них Back Up and Restore Replicated Databases
Журнал транзакций (SQL Server) The Transaction Log (SQL Server)
Модели восстановления (SQL Server) Recovery Models (SQL Server)
Наборы носителей, семейства носителей и резервные наборы данных (SQL Server)Media Sets, Media Families, and Backup Sets (SQL Server)