备份文件和文件组Back Up Files and Filegroups

适用对象:是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 ServerSQL Server Management StudioSQL Server Management Studio或 PowerShell 在 Transact-SQLTransact-SQL中备份文件和文件组。This topic describes how to back up files and filegroups in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell. 当数据库大小和性能要求使完整数据库备份显得不切实际,则可以创建文件备份。When the database size and performance requirements make a full database backup impractical, you can create a file backup instead. 文件备份 包含一个或多个文件(或文件组)中的所有数据。A file backup contains all the data in one or more files (or filegroups).

有关文件备份的详细信息,请参阅 完整文件备份 (SQL Server)差异备份 (SQL Server)For more information about file backups, see Full File Backups (SQL Server) and Differential Backups (SQL Server).

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 不允许在显式或隐式事务中使用 BACKUP 语句。The BACKUP statement is not allowed in an explicit or implicit transaction.

  • 在简单恢复模式下,必须一起备份所有读/写文件。Under the simple recovery model, read/write files must all be backed up together. 这有助于确保将数据库还原到一致的时点。This helps make 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 创建的备份称为部分备份,请参阅部分备份 (SQL Server)A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup, see Partial Backups (SQL Server).

有关限制的详细信息,请参阅 备份概述 (SQL Server)For more information about limitations and restrictions, see Backup Overview (SQL Server).

建议Recommendations

默认情况下,每个成功的备份操作都会在 SQL ServerSQL Server 错误日志和系统事件日志中添加一个条目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 如果非常频繁地备份日志,这些成功消息会迅速累积,从而产生一个巨大的错误日志,这样会使查找其他消息变得非常困难。If you back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. 在这些情况下,如果任何脚本均不依赖于这些日志条目,则可以使用跟踪标志 3226 取消这些条目,请参阅 跟踪标志 (Transact-SQL)In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries, see Trace Flags (Transact-SQL).

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASEBACKUP LOG 权限 。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

  1. 连接到相应的 SQL Server 数据库引擎SQL Server Database Engine实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。After connecting to the appropriate instance of the SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展开 “数据库” ,然后根据数据库的不同,选择用户数据库,或展开 “系统数据库” ,再选择系统数据库。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 右键单击数据库,指向“任务” ,再单击“备份” 。Right-click the database, point to Tasks, and then click Back Up. 将出现 “备份数据库” 对话框。The Back Up Database dialog box appears.

  4. “数据库” 列表中,验证数据库名称。In the Database list, verify the database name. 您也可以从列表中选择其他数据库。You can optionally select a different database from the list.

  5. “备份类型” 列表中,选择 “完整”“差异”In the Backup type list, select Full or Differential.

  6. 对于 “备份组件” 选项,单击 “文件和文件组”For the Backup component option, click File and Filegroups.

  7. “选择文件和文件组” 对话框中,选择要备份的文件和文件组。In the Select Files and Filegroups dialog box, select the files and filegroups you want to back up. 可以选择一个或多个单个文件,也可以复选文件组的框,从而自动选择该文件组中的所有文件。You can select one or more individual files or check the box for a filegroup to automatically select all the files in that filegroup.

  8. 可以接受 “名称” 文本框中建议的默认备份集名称,也可以为备份集输入其他名称。Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

  9. (可选)在“说明”文本框中,输入备份集的说明 。(optional) In the Description text box, enter a description of the backup set.

  10. 指定备份集的过期时间:Specify when the backup set will expire:

    • 若要使备份集在特定天数后过期,请单击“之后” (默认选项),并输入备份集从创建到过期所需的天数。To have the backup set expire after a specific number of days, click After (the default option) and enter the number of days after set creation that the set will expire. 此值范围为 0 到 99999 天;0 天表示备份集将永不过期。This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

      默认值在 “服务器属性” 对话框(位于 “数据库设置” 页上)的 “默认备份媒体保持期(天)” 选项中设置。The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). 若要访问此选项,请在对象资源管理器中右键单击服务器名称,并选择“属性”,然后选择“数据库设置” 页。To access this option, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

    • 若要使备份集在特定日期过期,请单击 “在” ,并输入备份集的过期日期。To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

  11. 通过单击 “磁盘”“磁带” ,选择备份目标的类型。Choose the type of backup destination by clicking Disk or Tape. 若要选择包含单个介质集的多个磁盘驱动器或磁带机(最多为 64 个)的路径,请单击 “添加”To select the paths of up to 64 disk or tape drives that contain a single media set, click Add. 所选路径将显示在 “备份到” 列表中。The selected paths are displayed in the Backup to list.

    备注

    若要删除备份目标,请选择该备份目标并单击 “删除”To remove a backup destination, select it and click Remove. 若要查看备份目标的内容,请选择该备份目标并单击 “内容”To view the contents of a backup destination, select it and click Contents.

  12. 若要查看或选择高级选项,请在 “选择页” 窗格中单击 “选项”To view or select the advanced options, click Options in the Select a page pane.

  13. 可以通过单击以下选项之一来选择 “覆盖介质” 选项:Select an Overwrite Media option, by clicking one of the following:

    • 备份到现有介质集Back up to the existing media set

      对于此选项,请单击 “追加到现有备份集”“覆盖所有现有备份集”For this option, click either Append to the existing backup set or Overwrite all existing backup sets.

      有关备份到现有媒体集的信息,请参阅媒体集、媒体簇和备份集 (SQL Server)For information about backing up to an existing media set, see Media Sets, Media Families, and Backup Sets (SQL Server).

      • (可选)选择“检查介质集名称和备份集的过期日期” ,以使备份操作对媒体集和备份集的过期日期和时间进行验证。(optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

      • (可选)在“介质集名称”文本框中输入名称 。(optional) Enter a name in the Media set name text box. 如果没有指定名称,将使用空白名称创建介质集。If no name is specified, a media set with a blank name is created. 如果指定了介质集名称,将检查介质(磁带或磁盘),以确定实际名称是否与此处输入的名称匹配。If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name that you enter here.

      如果将介质名称保留空白,并选中该框以便与介质进行核对,则只有当介质上的介质名称也是空白时才能成功。If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

    • 备份到新介质集并清除所有现有备份集Back up to a new media set, and erase all existing backup sets

      对于该选项,请在 “新建介质集名称” 文本框中输入名称,并在 “新建介质集说明” 文本框中描述介质集(可选)。For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

      有关创建新媒体集的详细信息,请参阅媒体集、媒体簇和备份集 (SQL Server)For more information about creating a new media set, see Media Sets, Media Families, and Backup Sets (SQL Server).

  14. (可选)在“可靠性”部分中,选中 :(optional) In the Reliability section, check:

  15. 如果备份到磁带驱动器(如同 “常规” 页的 “目标” 部分指定的一样),则 “备份后卸载磁带” 选项处于活动状态。If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. 单击此选项可以启用 “卸载前倒带” 选项。Clicking this option enables the Rewind the tape before unloading option.

    备注

    除非备份的是事务日志(如同“常规” 页的“备份类型” 部分中指定的一样),否则“事务日志” 部分中的选项处于不活动状态。The options in the Transaction log section are inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).

  16. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本支持 备份压缩and later versions support backup compression. 默认情况下,是否压缩备份取决于 backup-compression default 服务器配置选项的值。By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. 但是,不管当前服务器级默认设置如何,都可以通过选中 “压缩备份” 来压缩备份,并且可以通过选中 “不压缩备份” 来防止压缩备份。However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    如需查看当前备份压缩默认值,请参阅查看或配置备份压缩默认值服务器配置选项To view the current backup compression default, see View or Configure the backup compression default Server Configuration Option

使用 Transact-SQLUsing Transact-SQL

若要创建文件或文件组备份,请使用 BACKUP DATABASE <file_or_filegroup> 语句。To create a file or filegroup backup, use a BACKUP DATABASE <file_or_filegroup> statement. 此语句至少必须指定以下各项:Minimally, this statement must specify the following:

  • 数据库名称。The database name.

  • FILE 或 FILEGROUP 子句(为每个文件或文件组分别指定)。A FILE or FILEGROUP clause for each file or filegroup, respectively.

  • 将写入完整备份的备份设备。The backup device on which the full backup will be written.

用于文件备份的基本 Transact-SQLTransact-SQL 语法如下:The basic Transact-SQLTransact-SQL syntax for a file backup is:

BACKUP DATABASE databaseBACKUP DATABASE database

{ FILE = logical_file_name | FILEGROUP = logical_filegroup_name } [ , ...f ]{ FILE =logical_file_name | FILEGROUP =logical_filegroup_name } [ ,...f ]

TO backup_device [ , ...n ]TO backup_device [ ,...n ]

[ WITH with_options [ , ...o ] ] ;[ WITH with_options [ ,...o ] ] ;

选项Option “说明”Description
databasedatabase 备份事务日志、部分数据库或完整的数据库时所用的源数据库。Is the database from which the transaction log, partial database, or complete database is backed up.
FILE = logical_file_name FILE =logical_file_name 指定要包含在文件备份中的文件的逻辑名称。Specifies the logical name of a file to include in the file backup.
FILEGROUP = logical_filegroup_name FILEGROUP =logical_filegroup_name 指定要包含在文件备份中的文件组的逻辑名称。Specifies the logical name of a filegroup to include in the file backup. 在简单恢复模式下,只允许对只读文件组执行文件组备份。Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.
[ , ...f ][ ,...f ] 表示可以指定多个文件和文件组的占位符。Is a placeholder that indicates that multiple files and filegroups may be specified. 不限制文件或文件组的数量。The number of files or filegroups is unlimited.
backup_device [ , ...n ]backup_device [ ,...n ] 指定一个列表,它包含 1 至 64 个用于备份操作的备份设备。Specifies a list of from 1 to 64 backup devices to use for the backup operation. 您可以指定物理备份设备,也可以指定对应的逻辑备份设备(如果已定义)。You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. 若要指定物理备份设备,请使用 DISK 或 TAPE 选项:To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name{ DISK | TAPE } =physical_backup_device_name

有关详细信息,请参阅 备份设备 (SQL Server)For more information, see Backup Devices (SQL Server).
WITH with_options [ , ...o ]WITH with_options [ ,...o ] 您也可以指定一个或多个附加选项,如 DIFFERENTIAL。Optionally, specifies one or more additional options, such as DIFFERENTIAL. 差异文件备份需要以完整文件备份为基础。A differential file backup requires a full file backup as a base.

有关详细信息,请参阅创建差异数据库备份 (SQL Server)For more information, see Create a Differential Database Backup (SQL Server).

在完整恢复模式下,还必须备份事务日志。Under the full recovery model, you must also back up the transaction log. 若要使用一整套文件的完整备份来还原数据库,您还必须拥有足够的日志备份,以便涵盖从第一个文件备份开始的所有文件备份。To use a complete set of full file backups to restore a database, you must also have enough log backups to span all the file backups, from the start of the first file backup.

有关详细信息,请参阅 备份事务日志 (SQL Server)数据库还原到一个新位置并且可以选择重命名该数据库。For more information, see Back Up a Transaction Log (SQL Server).

示例Examples

下面的示例备份了 Sales 数据库的辅助文件组的一个或多个文件。The following examples back up one or more files of the secondary filegroups of the Sales database. 此数据库使用完整恢复模式并且包含以下辅助文件组:This database uses the full recovery model and contains the following secondary filegroups:

  • 名为 SalesGroup1 的文件组,它包含文件 SGrp1Fi1SGrp1Fi2A filegroup named SalesGroup1 that has the files SGrp1Fi1 and SGrp1Fi2.

  • 名为 SalesGroup2 的文件组,它包含文件 SGrp2Fi1SGrp2Fi2A filegroup named SalesGroup2 that has the files SGrp2Fi1 and SGrp2Fi2.

A.A. 为两个文件创建文件备份Create a file backup of two files

下面的示例仅为 SGrp1Fi2 文件组的 SalesGroup1 文件和 SGrp2Fi2 文件组的 SalesGroup2 文件创建差异文件备份。The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.  
BACKUP DATABASE Sales  
   FILE = 'SGrp1Fi2',   
   FILE = 'SGrp2Fi2'   
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck';  
GO  

B.B. 创建辅助文件组的完整文件备份Create a full file backup of the secondary filegroups

下面的示例将对两个辅助文件组中的各个文件创建完整文件备份。The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.  
BACKUP DATABASE Sales  
   FILEGROUP = 'SalesGroup1',  
   FILEGROUP = 'SalesGroup2'  
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck';  
GO  

C.C. 创建辅助文件组的差异文件备份Create a differential file backup of the secondary filegroups

下面的示例将对两个辅助文件组中的各个文件创建差异文件备份。The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.  
BACKUP DATABASE Sales  
   FILEGROUP = 'SalesGroup1',  
   FILEGROUP = 'SalesGroup2'  
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'  
   WITH   
      DIFFERENTIAL;  
GO  

使用 PowerShellUsing PowerShell

设置和使用 SQL Server PowerShell 提供程序Set up and use the SQL Server PowerShell Provider.

使用 Backup-SqlDatabase cmdlet 并为 -BackupAction 参数的值指定 FilesUse the Backup-SqlDatabase cmdlet and specify Files for the value of the -BackupAction parameter. 此外,还指定下列参数之一:Also, specify one of the following parameters:

  • 若要备份某个特定文件,请指定 _-DatabaseFile_String 参数,其中 String 是要备份的一个或多个数据库文件。To back up a specific file, specify the -DatabaseFileString parameter, where String is one or more database files to be backed up.

  • 若要备份某个给定文件组中的所有文件,请指定 _-DatabaseFileGroup_String 参数,其中 String 是要备份的一个或多个数据库文件组。To back up all the files in a given filegroup, specify the -DatabaseFileGroupString parameter, where String is one or more database filegroups to be backed up.

下面的示例在 <myDatabase> 数据库中创建辅助文件组“FileGroup1”和“FileGroup2”中的每个文件的完整文件备份。The following example creates a full file backup of every file in the secondary filegroups 'FileGroup1' and 'FileGroup2' in the <myDatabase> database. 将在服务器实例 Computer\Instance的默认备份位置上创建备份。The backups are created on the default backup location of the server instance Computer\Instance.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Files -DatabaseFileGroup "FileGroup1","FileGroup2" 

另请参阅See Also

备份概述 (SQL Server) Backup Overview (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
备份历史记录和标头信息 (SQL Server) Backup History and Header Information (SQL Server)
备份数据库(“常规”页) Back Up Database (General Page)
备份数据库(“备份选项”页) Back Up Database (Backup Options Page)
完整文件备份 (SQL Server) Full File Backups (SQL Server)
差异备份 (SQL Server) Differential Backups (SQL Server)
文件还原(完整恢复模式) File Restores (Full Recovery Model)
文件还原(简单恢复模式)File Restores (Simple Recovery Model)