备份事务日志Back Up a Transaction Log

适用对象:是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 a transaction log in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

开始之前Before You Begin

限制和局限Limitations and restrictions

不允许在显式或隐式事务中使用 BACKUP 语句。The BACKUP statement is not allowed in an explicit or implicit transaction. 显式事务就是可以显式地在其中定义事务的开始和结束的事务。An explicit transaction is one in which you explicitly define both the start and end of the transaction.

建议Recommendations

  • 如果数据库使用完整恢复模式或大容量日志恢复模式,则必须足够频繁地备份事务日志,以保护数据和避免事务日志变满If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data, and to prevent the transaction log from filling. 这将截断日志,并且支持将数据库还原到特定时间点。This truncates the log and supports restoring the database to a specific point in time.

  • 默认情况下,每个成功的备份操作都会在 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 frequently, these success messages accumulate quickly, resulting in huge error logs, making 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 权限 。The BACKUP DATABASE and BACKUP LOG permissions needed are granted by default to members of the sysadmin fixed server role, and the db_owner and db_backupoperator fixed database roles. 开始操作前,先检查是否有正确的权限。Check for the correct permissions before you begin.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。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. 在因尝试备份或还原而访问物理资源 之前,备份设备物理文件中的权限问题可能并不明显。Permissions problems on the backup device's physical file may not become obvious to you until you attempt to access the physical resource when you try to backup or restore. 因此再次强调,开始操作前,先检查权限。So again, check permissions before you begin.

使用 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 box, verify the database name. 您也可以从列表中选择其他数据库。You can optionally select a different database from the list.

  5. 验证恢复模式是 FULL 还是 BULK_LOGGEDVerify that the recovery model is either FULL or BULK_LOGGED.

  6. “备份类型” 列表框中,选择 “事务日志”In the Backup type list box, select Transaction Log.

  7. (可选)选择“仅复制备份”创建仅复制备份 。(optional) Select Copy Only Backup to create a copy-only backup. “仅复制备份”是SQL ServerSQL Server 独立于常规备份序列的SQL ServerSQL Server备份,请参阅仅复制备份 (SQL Server)A copy-only backup is a SQL ServerSQL Server backup that is independent of the sequence of conventional SQL ServerSQL Server backups, see Copy-Only Backups (SQL Server).

    备注

    选择“差异” 选项时,无法创建仅复制备份。When the Differential option is selected, you cannot create a copy-only backup.

  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 dialog box, 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. 通过单击 “磁盘”“URL”“磁带” ,选择备份目标的类型。Choose the type of backup destination by clicking Disk, URL or Tape. 若要选择包含单个介质集的多个磁盘或磁带机(最多为 64 个)的路径,请单击 “添加”To select the paths 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.

  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

      对于此选项,请单击“追加到现有备份集”或“覆盖所有现有备份集”,请参阅媒体集、媒体系列和备份集 (SQL Server)For this option, click either Append to the existing backup set or Overwrite all existing backup sets, 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 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

      对于该选项,请在“新建介质集名称”文本框中输入名称,并在“新建介质集说明”文本框中描述介质集(可选),请参阅媒体集、媒体系列和备份集 (SQL Server)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, see Media Sets, Media Families, and Backup Sets (SQL Server).

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

  15. “事务日志” 区域中:In the Transaction log section:

    • 对于例行的日志备份,请保留默认选项 “通过删除不活动的条目截断事务日志”For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.

    • 若要备份日志尾部(即活动的日志),请选中“备份日志尾部,并使数据库处于还原状态” 。To back up the tail of the log (the active log), check Back up the tail of the log, and leave database in the restoring state.

      备份日志尾部失败后执行结尾日志备份,以防丢失所做的工作。A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. 在失败之后且在开始还原数据库之前,或者在故障转移到辅助数据库时,备份活动日志(结尾日志备份)。Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. 选择此选项等效于在 Transact-SQL BACKUP LOG 语句中指定 NORECOVERY 选项。Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL.

      有关结尾日志备份的详细信息,请参阅结尾日志备份 (SQL Server) For more information about tail-log backups, see Tail-Log Backups (SQL Server).

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

  17. 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, see View or Configure the backup compression default Server Configuration Option.

    若要加密备份文件,请选中 “加密备份” 复选框。To encrypt the backup file check the Encrypt backup check box. 选择要用于加密备份文件的加密算法,并提供一个证书或非对称密钥。Select an encryption algorithm to use for encrypting the backup file and provide a Certificate or Asymmetric key. 可用于加密的算法是:The available algorithms for encryption are:

    • AES 128AES 128

    • AES 192AES 192

    • AES 256AES 256

    • Triple DESTriple DES

使用 Transact-SQLUsing Transact-SQL

执行 BACKUP LOG 语句以备份事务日志,同时指定下列对象:Execute the BACKUP LOG statement to back up the transaction log, specifying the following:

  • 要备份的事务日志所属的数据库的名称。The name of the database to which the transaction log that you want to back up belongs.

  • 写入事务日志备份的备份设备。The backup device where the transaction log backup is written.

重要

此示例使用 AdventureWorks2012AdventureWorks2012 数据库,该数据库使用简单恢复模式。This example uses the AdventureWorks2012AdventureWorks2012 database, which uses the simple recovery model. 若要允许日志备份,请在完整备份数据库之前,将数据库设置为使用完整恢复模式。To permit log backups, before taking a full database backup, the database was set to use the full recovery model.

有关详细信息,请参阅查看或更改数据库的恢复模式 (SQL Server)For more information, see View or Change the Recovery Model of a Database (SQL Server).

以下示例将在以前创建的已命名备份设备 AdventureWorks2012AdventureWorks2012 上创建 MyAdvWorks_FullRM_log1数据库的事务日志备份。This example creates a transaction log backup for the AdventureWorks2012AdventureWorks2012 database to the previously created named backup device, MyAdvWorks_FullRM_log1.

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1;  
GO  

使用 PowerShellUsing PowerShell

设置和使用 SQL Server PowerShell 提供程序Set up and use the SQL Server PowerShell Provider. 使用 Backup-SqlDatabase cmdlet 并为 -BackupAction 参数的值指定 LogUse the Backup-SqlDatabase cmdlet and specify Log for the value of the -BackupAction parameter.

下面的示例在服务器实例 <myDatabase> 的默认备份位置创建数据库 Computer\Instance的日志备份。The following example creates a log backup of the <myDatabase> database to the default backup location of the server instance Computer\Instance.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Log  

Related tasksRelated tasks

另请参阅See also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
应用事务日志备份 (SQL Server) Apply Transaction Log Backups (SQL Server)
维护计划 Maintenance Plans
完整文件备份 (SQL Server)Full File Backups (SQL Server)