完整文件备份 (SQL Server)Full File Backups (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 is relevant for SQL ServerSQL Server databases that contain multiple files or filegroups.

可以分别备份和还原 SQL ServerSQL Server 数据库中的文件。The files in a SQL ServerSQL Server database can be backed up and restored individually. 此外,可以指定整个文件组,而不是逐个指定每个构成文件。Also, you can specify a whole filegroup instead of specifying each constituent file individually. 请注意,如果文件组中的任何文件脱机(例如,由于正在还原该文件),则整个文件组均将脱机并且无法备份。Note that if any file in a filegroup is offline (for example, because the file is being restored), the whole filegroup is offline and cannot be backed up.

只读文件组的文件备份可以与部分备份一起使用。File backups of read-only filegroups can be combined with partial backups. 部分备份包括所有读/写文件组以及可选的一个或多个只读文件组。Partial backups include all the read/write filegroups and, optionally, one or more read-only filegroups. 有关详细信息,请参阅部分备份 (SQL Server)For more information, see Partial Backups (SQL Server).

文件备份可以用作差异文件备份的“差异基准 ”。A file backup can serve as the differential base for differential file backups. 有关详细信息,请参阅 差异备份 (SQL Server)For more information, see Differential Backups (SQL Server).

备注

除了在与“差异文件备份”明确进行比较的时候,完整文件备份通常称为文件备份。Full file backups are typically called file backups, except when they are being explicitly compared with differential file backups.

本主题内容:In This Topic:

文件备份的优点Benefits of File Backups

相对于数据库备份,文件备份具有如下优点:File backups offer the following advantages over database backups:

  • 使用文件备份使您能够只还原损坏的文件,而不用还原数据库的其余部分,从而加快了恢复速度。Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

    例如,如果数据库由位于不同磁盘上的若干个文件组成,在其中一个磁盘发生故障时,只需还原故障磁盘上的文件。For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. 可以快速还原已损坏的文件,并且恢复速度快于针对整个数据库的恢复速度。The damaged file can be quickly restored, and recovery is faster than it would be for an entire database.

  • 与完整数据库备份(对于超大型数据库而言,变得难以管理)相比,文件备份增加了计划和介质处理的灵活性。File backups increase flexibility in scheduling and media handling over full database backups, which for very large databases can become unmanageable. 文件或文件组备份的更高灵活性对于包含具有不同更新特征的数据的大型数据库也很有用。The increased flexibility of file or filegroup backups is also useful for large databases that contain data that has varying update characteristics.

文件备份的缺点Disadvantages of File Backups

  • 与完整数据库备份相比,文件备份的主要缺点是管理较复杂。The primary disadvantage of file backups compared to full database backups is the additional administrative complexity. 维护和跟踪这些完整备份是一种耗时的任务,所需空间可能会超过完整数据库备份的所需空间。Maintaining and keeping track of a complete set of these backups can be a time-consuming task that might outweigh the space requirements of full database backups.

  • 如果某个损坏的文件未备份,那么介质故障可能会导致无法恢复整个数据库。A media failure can make a complete database unrecoverable if a damaged file lacks a backup. 因此,必须维护一组完整的文件备份,对于完整/大容量日志恢复模式,还必须维护一个或多个日志备份,这些日志备份至少涵盖第一个完整文件备份和最后一个完整备份之间的时间间隔。You must therefore maintain a complete set of file backups, and, for the full/bulk-logged recovery model, one or more log backups covering minimally the interval between the first full file backup and last full file backup.

文件备份概述Overview of File Backups

完整文件备份指备份一个或多个文件或文件组中的所有数据。A full file backup backs up all the data in one or more files or filegroups. 文件备份在默认情况下包含足够的日志记录,可以将文件前滚至备份操作的末尾。By default, file backups contain enough log records to roll forward the file to the end of the backup operation.

备份只读文件或文件组的方法对每种恢复模式均相同。Backing up a read-only file or filegroup is the same for every recovery model. 在完整恢复模式下,一整套完整文件备份与跨所有文件备份的足够日志备份合起来等同于完整数据库备份。Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

一次只能进行一个文件备份操作。Only one file backup operation can occur at a time. 可以在一个操作中备份多个文件,但如果只需要还原一个文件,这样做可能会延长恢复时间。You can back up multiple files in one operation, but this might extend the recovery time if you only have to restore a single file. 这是因为查找该文件时,将读取整个备份。This is because to locate that file, the whole backup is read.

备注

单个文件可以从数据库备份中还原;但与从文件备份中还原相比,从数据库备份中找到和还原文件所需的时间更长。Individual files can be restored from a database backup; however, locating and restoring a file takes longer from a database backup than from a file backup.

文件备份和简单恢复模式File Backups and the Simple Recovery Model

在简单恢复模式下,必须一起备份所有读/写文件。Under the simple recovery model, read/write files must all be backed up together. 这样可以确保将数据库还原到一致的时点。This makes sure that the database can be restored to a consistent point in time. 请使用 READ_WRITE_FILEGROUPS 选项,而不是逐个指定每个读/写文件或文件组。Instead of individually specifying each read/write file or filegroup, use the READ_WRITE_FILEGROUPS option. 此选项用于备份数据库中的所有读/写文件组。This option backs up all the read/write filegroups in the database. 通过指定 READ_WRITE_FILEGROUPS 创建的备份称为部分备份。A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup. 有关详细信息,请参阅部分备份 (SQL Server)For more information, see Partial Backups (SQL Server).

文件备份和完全恢复模式File Backups and the Full Recovery Model

在完整恢复模式下,必须备份事务日志,不用考虑备份策略的其余部分。Under the full recovery model, you must back up the transaction log, regardless of the rest of your backup strategy. 一整套完整文件备份与涵盖从第一个文件备份开始的所有文件备份的足够日志备份合起来等同于完整数据库备份。A complete set of full file backups, together with enough log backups to span all the file backups from the start of the first file backup, is the equivalent of a full database backup.

仅使用文件备份和日志备份还原数据库的操作可能比较复杂。Restoring a database using just file and log backups can be complex. 因此,如果可能,最好执行完整数据库备份并在第一个文件备份开始之前开始日志备份。Therefore, if it is possible, it is a best practice to perform a full database backup and start the log backups before the first file backup. 下图显示了在创建数据库(在 t0 时间)之后立即执行完整数据库备份(在 t1 时间)的策略。The following illustration shows a strategy in which a full database backup is taken (at time t1) soon after the database is created (at time t0). 创建了第一个数据库备份之后,便可开始执行事务日志备份。This first database backup enables transaction log backups to start. 事务日志备份计划按设置的间隔执行。Transaction log backups are scheduled to occur at set intervals. 文件备份以最适合数据库业务要求的间隔执行。File backups occur at whatever interval best meets the business requirements for the database. 此图显示了四个文件组,每次备份其中的一个文件组。This illustration shows each of the four filegroups being backed up one at a time. 它们的备份顺序(A、C、B、A)反映了数据库的业务要求。The order in which they are backed up (A, C, B, A) reflects the business requirements of the database.

合并数据库、文件和日志备份的策略Strategy combining database, file, and log backups

备注

在完整恢复模式下,必须在还原读/写文件备份时前滚事务日志,以确保该文件与数据库的其余部分保持一致。Under the full recovery model, you must roll forward the transaction log when restoring a read/write file backup to make sure that the file is consistent with the rest of the database. 若要避免前滚大量事务日志备份,请考虑使用差异文件备份。To avoid rolling forward a lot of transaction log backups, consider using differential file backups. 有关详细信息,请参阅 差异备份 (SQL Server)For more information, see Differential Backups (SQL Server).

相关任务Related Tasks

创建文件或文件组备份To create a file or filegroup backup

备注

维护计划向导不支持文件备份。File backups are not supported by the Maintenance Plan Wizard.

另请参阅See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
备份概述 (SQL Server) Backup Overview (SQL Server)
备份和还原:互操作性和共存 (SQL Server) Backup and Restore: Interoperability and Coexistence (SQL Server)
差异备份 (SQL Server) Differential Backups (SQL Server)
文件还原(简单恢复模式) File Restores (Simple Recovery Model)
文件还原(完整恢复模式) File Restores (Full Recovery Model)
联机还原 (SQL Server) Online Restore (SQL Server)
段落还原 (SQL Server)Piecemeal Restores (SQL Server)