创建差异数据库备份 (SQL Server)Create a Differential Database Backup (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 Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中创建差异数据库备份。Create a differential database backup in SQL Server 2019 (15.x)SQL Server 2019 (15.x) using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题的内容Sections in this topic

准备工作Before you begin

限制和局限Limitations and restrictions

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

先决条件Prerequisites

  • 创建差异数据库备份需要有以前的完整数据库备份。Creating a differential database backup requires a previous full database backup. 如果你的数据库从未进行过备份,则请在创建任何差异备份之前,先执行完整数据库备份。If your database has never been backed up, run a full database backup before creating any differential backups. 有关详细信息,请参阅 创建完整数据库备份 (SQL Server)中创建差异数据库备份。For more information, see Create a Full Database Backup (SQL Server).

建议Recommendations

  • 当差异备份的大小增大时,还原差异备份会显著延长还原数据库所需的时间。As the differential backups increase in size, restoring a differential backup will significantly increase the time required to restore a database. 建议按设定的间隔执行新的完整备份,以便为数据建立新的差异基准。We recommend that you take a new full backup at set intervals to establish a new differential base for the data. 例如,您可以每周执行一次整个数据库的完整备份(即完整数据库备份),然后在该周内执行一系列常规的差异数据库备份。For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.

SecuritySecurity

首先检查你的权限!Check your permissions first!

BACKUP DATABASE 和 BACKUP LOG 权限默认为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role, the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题将会妨碍备份操作。Ownership and permission problems on the backup device's physical file will interfere with a backup operation. SQL ServerSQL Server 需能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。needs to 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. 在你因尝试备份或还原而访问物理资源之前,备份设备物理文件中的权限问题并不明显。Permissions problems on the backup device's physical file will not be obvious until the physical resource is accessed when you attempt the backup or restore.

SQL Server Management StudioSQL Server Management Studio

创建差异数据库备份Create a differential database backup

  1. 连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine 实例之后,在“对象资源管理器”中,单击服务器名称以展开服务器树。After connecting to the appropriate instance of the MicrosoftMicrosoft 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 box, verify the database name. 您也可以从列表中选择其他数据库。You can optionally select a different database from the list.

    可以执行任意恢复模式(完整、大容量日志或简单)的差异备份。You can perform a differential backup for any recovery model (full, bulk-logged, or simple).

  5. “备份类型” 列表框中,选择 “差异”In the Backup type list box, select Differential.

    重要

    选择了“差异” 后,请验证是否清除了“仅复制备份” 复选框。When you selectDifferential , verify that the Copy Only Backup check box is cleared.

  6. 对于 “备份组件” ,请单击 “数据库”For Backup component, click Database.

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

  8. 或者,在 “说明” 文本框中,输入备份集的说明。Optionally, in the Description text box, enter a description of the backup set.

  9. 指定备份集的过期时间: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; 0 days means 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, 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.

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

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

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

  12. 可以通过单击以下选项之一来选择 “覆盖介质” 选项: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. 或者,选中 “检查介质集名称和备份集过期时间” 复选框,并在 “介质集名称” 文本框中输入名称(可选)。Optionally, check the Check media set name and backup set expiration check box and, optionally, 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 if the actual name matches the name 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.

  13. 或者,在 “可靠性” 部分中,选中:In the Reliability section, optionally, check:

  14. 如果备份到磁带驱动器(如同 “常规” 页的 “目标” 部分指定的一样),则 “备份后卸载磁带” 选项处于活动状态。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 activates 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).

  15. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更高版本支持 备份压缩and later supports 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

    备注

    另外,可以使用维护计划向导创建差异数据库备份。Alternatively, you can use the Maintenance Plan Wizard to create differential database backups.

Transact-SQLTransact-SQL

创建差异数据库备份Create a differential database backup

  1. 执行 BACKUP DATABASE 语句可以创建差异数据库备份,同时指定:Execute the BACKUP DATABASE statement to create the differential database backup, specifying:

    • 要备份的数据库的名称。The name of the database to back up.

    • 写入完整数据库备份的备份设备。The backup device where the full database backup is written.

    • DIFFERENTIAL 子句,用于指定仅备份自上次创建完整数据库备份之后已更改的数据库部分。The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last full database backup was created are backed up.

    要求语法为:The required syntax is:

    BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIALBACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL

示例 (Transact-SQL)Example (Transact-SQL)

以下示例为 MyAdvWorks 数据库创建完整数据库备份和差异数据库备份。This example creates a full and a differential database backup for the MyAdvWorks database.

-- Create a full database backup first.  
BACKUP DATABASE MyAdvWorks   
   TO MyAdvWorks_1   
   WITH INIT;  
GO  
-- Time elapses.  
-- Create a differential database backup, appending the backup  
-- to the backup device containing the full database backup.  
BACKUP DATABASE MyAdvWorks  
   TO MyAdvWorks_1  
   WITH DIFFERENTIAL;  
GO  

另请参阅See Also

差异备份 (SQL Server) Differential Backups (SQL Server)
创建完整数据库备份 (SQL Server) Create a Full Database Backup (SQL Server)
备份文件和文件组 (SQL Server) Back Up Files and Filegroups (SQL Server)
还原差异数据库备份 (SQL Server) Restore a Differential Database Backup (SQL Server)
还原事务日志备份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
维护计划 Maintenance Plans
完整文件备份 (SQL Server)Full File Backups (SQL Server)