sql server 备份mysql_SQL Server 数据库的备份和还原

SQL Server 数据库的备份和还原Back Up and Restore of SQL Server Databases

03/30/2018

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

本文介绍备份 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.

本文介绍了 SQL Server 备份。This article introduces SQL Server backups. 有关备份 SQL Server 数据库的特定步骤，请参阅创建备份。For specific steps to back up SQL Server databases, see Creating backups.

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 还支持备份到 Azure Blob 存储服务和从其还原。In addition to local storage for storing the backups, SQL Server also supports backup to and restore from the Azure Blob Storage Service. 对于使用 Microsoft Azure Blob 存储服务存储的数据库文件， SQL Server 2016 (13.x) 提供相应的选项让你使用 Azure 快照来实现接近实时的备份和更快的还原。For database files stored using the Microsoft Azure Blob storage service, SQL Server 2016 (13.x) provides the option to use Azure snapshots for nearly instantaneous backups and faster restores. Azure 还为 Azure VM 中运行的 SQL Server 提供企业级备份解决方案。Azure also offers an enterprise-class backup solution for SQL Server running in Azure VMs. 作为完全托管的备份解决方案，它支持 AlwaysOn 可用性组、长期保留、时点恢复以及集中管理和监视。A fully-managed backup solution, it supports Always On availability groups, long-term retention, point-in-time recovery, and central management and monitoring.

为何备份？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 备份到 Azure Blob 存储服务，可以在本地位置之外的其他区域创建一个站外备份，这样在发生影响本地位置的自然灾难时仍可以使用数据库。By using SQL Server Backup to 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.

此外，数据库备份对于进行日常管理(如将数据库从一台服务器复制到另一台服务器、设置 Always On 可用性组Always 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 Always On 可用性组Always 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 备份也可以写入 Azure Blob 存储服务，并且使用 URL 格式来指定备份文件的目标和名称。SQL Server backups can also be written to an Azure Blob storage service, and URL format is used to specify the destination and the name of the backup file..

备份介质backup media

已写入一个或多个备份的一个或多个磁带或磁盘文件。One or more tapes or disk files to which one or more backups have been written.

数据备份 (data backup)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)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)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)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)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)

recoverrecover

将数据库恢复到稳定且一致的状态。To return a database to a stable and consistent state.

recoveryrecovery

将数据库恢复到事务一致状态的数据库启动阶段或 Restore With Recovery 阶段。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)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 balances the business requirements for maximum data availability and minimum data loss, while considering the cost of maintaining and storing backups.

备份和还原策略包含备份部分和还原部分。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, how restores should be performed to meet your goals for database availability and minimizing data loss, and how restores are tested.

设计有效的备份和还原策略需要仔细计划、实现和测试。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 and have tested the restored database for physical consistency. 必须考虑各种因素。You must consider a variety of factors. 其中包括：These include:

组织在生产数据库方面的目标，尤其是对可用性和防止数据丢失或损坏的要求。The goals of your organization regarding your production databases, especially the requirements for availability and protection of data from loss or damage.

每个数据库的特性包括：大小、使用模式、内容特性以及数据要求等。The nature of each database: 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.

最佳做法建议Best Practice Recommendations

使用独立的存储Use Separate Storage

重要

确保将数据库备份放在与数据库文件不同的物理位置或设备上。Ensure that you place your database backups on a separate physical location or device from the database files. 存储数据库的物理驱动器出现故障或崩溃时，可恢复性取决于能否访问存储备份的独立驱动器或远程设备以执行还原。When your physical drive that stores your databases malfunctions or crashes, recoverability depends on the ability to access the separate drive or remote device that stored the backups in order to perform a restore. 请记住，你可以在同一个物理磁盘驱动器中创建多个逻辑卷或分区。Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. 在为备份选择存储位置之前，请仔细研究磁盘分区和逻辑卷布局。Carefully study the disk partition and logical volume layouts before choosing a storage location for the backups.

选择适当的恢复模式Choose appropriate recovery model

备份和还原操作发生在恢复模式的上下文中。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. 因此，数据库的恢复模式决定了数据库支持的备份类型和还原方案，以及事务日志备份的大小。Thus, the recovery model of a database determines what types of backups and restore scenarios are supported for the database, and what the size of the transaction log backups would be. 通常，数据库使用简单恢复模式或完整恢复模式。Typically, a database uses either the simple recovery model or the full recovery model. 可以在执行大容量操作之前切换到大容量日志恢复模式，以补充完整恢复模式。The full recovery model can be augmented 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. 为了在大容量日志操作期间最大程度减少对日志大小的影响，同时允许这些操作的可恢复性，请使用大容量日志恢复模式。To minimize impact on log size during bulk-logged operations while at the same time allowing for recoverability of those operations, use bulk-logged 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.

完整数据库备份需要多少磁盘空间？How much disk space will a full database backup require?

你的企业需要维护过去多久的备份？How far in the past does your business require to maintain backups?

确保你已根据应用程序需求和业务需求制定了适当的备份计划。Make sure you have a proper backup schedule established according to the needs of the application and business requirements. 随着备份变得陈旧，数据丢失风险会更高，除非你有办法重新生成故障点之前的所有数据。As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the data till the point of failure. 由于存储资源限制而选择处理旧备份之前，请考虑是否需要以前的可恢复性Before you choose to dispose of old backups due to storage resource limitations, consider if recoverability is required that far in the past

估计完整数据库备份的大小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.

计划备份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. 另外建议在还原备份后，通过数据库的 DBCC CHECKDB 执行数据库一致性检查，以验证备份媒体是否未损坏。It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not damaged.

验证媒体稳定性和一致性Verify Media Stability and Consistency

使用备份实用工具提供的验证选项(BACKUP T-SQL 命令、SQL Server 维护计划、备份软件或解决方案等)。Use the verification options provided by the backup utilities (BACKUP T-SQL command, SQL Server Maintenance Plans, your backup software or solution, etc). 有关示例，请参阅 [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) 使用 BACKUP CHECKSUM 等高级功能来检测备份媒体本身的问题。For an example, see [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Use advanced features like BACKUP CHECKSUM to detect problems with the backup media itself.

文档备份/还原策略Document Backup/Restore Strategy

建议您将备份和还原过程记录下来并在运行手册中保留记录文档的副本。We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.

我们还建议你为每个数据库维护一个操作手册。We also 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.

使用 xEvent 监视进度Monitor 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

有关备份任务的详细信息More about backup tasks

使用备份设备和备份媒体Working with backup devices and backup media

创建备份Creating backups

注意！Note! 对于部分备份或仅复制备份，必须分别使用带 PARTIAL 或 COPY_ONLY 选项的 Transact-SQLTransact-SQLBACKUP 语句。For partial or copy-only backups, you must use the Transact-SQLTransact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

还原数据备份Restore data backups

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

还原事务日志(完整恢复模式)Restore transaction logs (Full Recovery Model)

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

更多信息和资源More information and resources