Backup Overview (SQL Server)Backup Overview (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍 SQL ServerSQL Server 备份组件。This topic introduces the SQL ServerSQL Server backup component. 备份 SQL ServerSQL Server 数据库对于保护您的数据至关重要。Backing up your SQL ServerSQL Server database is essential for protecting your data. 本讨论涵盖了备份类型和备份限制。This discussion covers backup types, and backup restrictions. 该主题还介绍了 SQL ServerSQL Server 备份设备和备份介质。The topic also introduces SQL ServerSQL Server backup devices and backup media.


备份 [动词] (back up)back up [verb]
SQL ServerSQL Server 数据库或其事务日志中将数据或日志记录复制到备份设备(如磁盘),以创建数据备份或日志备份。Copies the data or log records from a SQL ServerSQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.

备份 [名词] (backup)backup [noun]
可用于在失败后还原或恢复数据的 SQL ServerSQL Server 数据副本。A copy of SQL ServerSQL Server data that can be used to restore and recover the data after a failure. 在数据库级别以及针对数据库的一个或多个文件或文件组创建 SQL ServerSQL Server 数据的备份。A backup of SQL ServerSQL Server data is created at the level of a database or one or more of its files or filegroups. 不能创建表级备份。Table-level backups cannot be created. 除了数据备份之外,完整恢复模式要求创建事务日志的备份。In addition to data backups, the full recovery model requires creating backups of the transaction log.

恢复模式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.

备份类型Types of backups

仅复制备份 (copy-only backup)copy-only backup
独立于正常 SQL ServerSQL Server 备份序列的特殊用途备份。A special-use backup that is independent of the regular sequence of SQL ServerSQL Server backups.

数据备份 (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 extents that have changed since the differential base.

部分差异备份仅记录自上一次部分备份(称为“差异基准”)以来文件组中发生更改的数据区。A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

完整备份 (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)

文件备份 (file backup)file backup
一个或多个数据库文件或文件组的备份。A backup of one or more database files or filegroups.

部分备份 (partial backup)partial backup
仅包含数据库中部分文件组的数据(包含主要文件组、每个读/写文件组以及任何可选指定的只读文件中的数据)。Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

备份介质术语和定义Backup media terms and definitions

备份设备 (backup device)backup device
要将 SQL ServerSQL Server 备份写入其中以及可从其中还原的磁盘或磁带设备。A disk or tape device to which SQL ServerSQL 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.. 有关详细信息,请参阅使用 Microsoft Azure Blob 存储服务执行 SQL Server 备份和还原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 backup have been written.

备份集 (backup set)backup set
通过成功的备份操作添加到介质组的备份内容。The backup content that is added to a media set by a successful backup operation.

介质簇 (media family)media family
在介质集中的单个非镜像设备或一组镜像设备上创建的备份。Backups created on a single nonmirrored device or a set of mirrored devices in a media set

介质集 (media set)media set
备份介质(磁带或磁盘文件)的有序集合,使用固定类型和数量的备份设备向其写入了一个或多个备份操作。An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.

镜像介质集 (mirrored media set)mirrored media set
介质集的多个副本(镜像)。Multiple copies (mirrors) of a media set.

备份压缩Backup compression

SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本支持压缩备份,并且 SQL Server 2008SQL Server 2008 及更高版本可以还原压缩后的备份。and later versions support compressing backups, and SQL Server 2008SQL Server 2008 and later versions can restore a compressed backup. 有关详细信息,请参阅备份压缩 (SQL Server)For more information, see Backup Compression (SQL Server).

备份操作限制Backup operations restrictions

可以在数据库在线并且正在使用时进行备份。Backup can occur while the database is online and being used. 但是,存在下列限制。However, the following restrictions exist.

无法备份脱机数据Cannot back up offline data

隐式或显式引用脱机数据的任何备份操作都会失败。Any backup operation that implicitly or explicitly references data that is offline fails. 一些典型示例包括:Some typical examples include the following:

  • 您请求完整数据库备份,但是数据库的一个文件组脱机。You request a full database backup, but one filegroup of the database is offline. 由于所有文件组都隐式包含在完整数据库备份中,因此,此操作将会失败。Because all filegroups are implicitly included in a full database backup, this operation fails.

    若要备份此数据库,可以使用文件备份并仅指定联机的文件组。To back up this database, you can use a file backup and specify only the filegroups that are online.

  • 请求部分备份,但是有一个读/写文件组处于脱机状态。You request a partial backup, but a read/write filegroup is offline. 由于部分备份需要使用所有读/写文件组,因此该操作失败。Because all read/write filegroups are required for a partial backup, the operation fails.

  • 请求特定文件的文件备份,但是其中有一个文件处于脱机状态。You request a file backup of specific files, but one of the files is not online. 该操作失败。The operation fails. 若要备份联机文件,可以省略文件列表中的脱机文件并重复该操作。To back up the online files, you can omit the offline file from the file list and repeat the operation.

通常,即使一个或多个数据文件不可用,日志备份也会成功。Typically, a log backup succeeds even if one or more data files are unavailable. 但如果某个文件包含大容量日志恢复模式下所做的大容量日志更改,则所有文件都必须都处于联机状态才能成功备份。However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.

并发限制Concurrency restrictions

SQL ServerSQL Server 可以使用联机备份过程来备份数据库。uses an online backup process to allow for a database backup while the database is still being used. 在备份过程中,可以进行多个操作;例如:在执行备份操作期间允许使用 INSERT、UPDATE 或 DELETE 语句。During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation. 但是,如果在正在创建或删除数据库文件时尝试启动备份操作,则备份操作将等待,直到创建或删除操作完成或者备份超时。However, if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.

在数据库备份或事务日志备份的过程中无法执行的操作包括:Operations that cannot run during a database backup or transaction log backup include the following:

  • 文件管理操作,如含有 ADD FILE 或 REMOVE FILE 选项的 ALTER DATABASE 语句。File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

  • 收缩数据库或文件操作。Shrink database or shrink file operations. 这包括自动收缩操作。This includes auto-shrink operations.

  • 如果在进行备份操作时尝试创建或删除数据库文件,则创建或删除操作将失败。If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

如果备份操作与文件管理操作或收缩操作重叠,则产生冲突。If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. 无论哪个冲突操作首先开始,第二个操作总会等待第一个操作设置的锁超时。(超时期限由会话超时设置控制。)如果在超时期限内释放锁,第二个操作将继续执行。Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. (The time-out period is controlled by a session time-out setting.) If the lock is released during the time-out period, the second operation continues. 如果锁超时,则第二个操作失败。If the lock times out, the second operation fails.

Related tasksRelated tasks

备份设备和备份媒体Backup devices and backup media

创建备份Create a backup


对于部分备份或仅复制备份,必须分别使用带 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.

以及更多!And more!

SQL Server 数据库的备份和还原 Back Up and Restore of SQL Server Databases
还原和恢复概述 (SQL Server) Restore and Recovery Overview (SQL Server)
维护计划 Maintenance Plans
事务日志 (SQL Server) The Transaction Log (SQL Server)
恢复模式 (SQL Server)Recovery Models (SQL Server)