在数据库损坏时备份事务日志 (SQL Server)Back Up the Transaction Log When the Database Is Damaged (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 ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中备份事务日志。This topic describes how to back up a transaction log when the database is damaged in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

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

建议Recommendations

  • 对于使用完整恢复模式或大容量日志恢复模式的数据库,通常需要在开始还原数据库前备份日志尾部。For a database that uses either the full or bulk-logged recovery model, you generally need to back up the tail of the log before beginning to restore the database. 在对日志传送配置进行故障转移之前,还应当备份主数据库的日志尾部。You also should back up the tail of the log of the primary database before failing over a log shipping configuration. 将结尾日志备份作为恢复数据库之前的最后一个日志备份还原可以防止失败后丢失工作。Restoring the tail-log backup as the final log backup before recovering the database avoids work loss after a failure. 有关结尾日志备份的详细信息,请参阅结尾日志备份 (SQL Server) For more information about tail-log backups, see Tail-Log Backups (SQL Server).

SecuritySecurity

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP 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

备份事务日志尾部To back up the tail of the transaction log

  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.

  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. 使 “仅复制备份” 处于取消选中状态。Leave Copy Only Backup deselected.

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

  9. “说明” 文本框中,输入结尾日志备份的说明。In the Description text box, enter a description for the tail-log backup.

  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. 通过单击 “磁盘”“磁带” ,选择备份目标的类型。Choose the type of backup destination by clicking Disk 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. “选项” 页上,可以通过单击下列选项之一来选择 “覆盖介质” 选项:On the Options page, 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, 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.

      或者在 “介质集名称” 文本框中输入名称。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 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

      对于该选项,请在 “新建介质集名称” 文本框中输入名称,并在 “新建介质集说明” 文本框中描述介质集(可选)。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 media set options, see Media Sets, Media Families, and Backup Sets (SQL Server).

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

    • 完成后验证备份Verify backup when finished.

    • 写入介质前检查校验和Perform checksum before writing to media.

    • 出现校验和错误时继续Continue on checksum error

    有关校验和的信息,请参阅在备份和还原期间可能的媒体错误 (SQL Server)For information on checksums, see Possible Media Errors During Backup and Restore (SQL Server).

  14. “事务日志” 部分,选中 “备份日志尾部,并使数据库处于还原状态”In the Transaction log section, check Back up the tail of the log, and leave database in the restoring state.

    这相当于指定以下 BACKUP 语句:This is equivalent to specifying the following BACKUP statement:

    BACKUP LOG <database_name> TO <backup_device> WITH NORECOVERY

    重要

    在还原时,“还原数据库”对话框将结尾日志备份的类型显示为“事务日志(仅备份)” 。At restore time, the Restore Database dialog box displays the type of a tail-log backup as Transaction Log (Copy Only).

  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 activates the Rewind the tape before unloading option.

  16. 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

使用 Transact-SQLUsing Transact-SQL

创建当前活动的事务日志的备份To create a backup of the currently active transaction log

  1. 执行 BACKUP LOG 语句以备份当前活动的事务日志,同时指定:Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:

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

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

    • NO_TRUNCATE 子句。The NO_TRUNCATE clause.

      只要事务日志文件是可访问的并且没有损坏,那么即使数据库不可访问,该子句也允许备份事务日志的活动部分。This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.

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

备注

此示例使用 AdventureWorks2012AdventureWorks2012,该对象使用简单恢复模式。This example uses the AdventureWorks2012AdventureWorks2012, 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).

此示例在数据库损坏且不可访问时备份当前活动事务日志(如果事务日志已损坏且可访问)。This example backs up the currently active transaction log when a database is damaged and inaccessible, if the transaction log is undamaged and accessible.

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1  
   WITH NO_TRUNCATE;  
GO  

另请参阅See Also

还原事务日志备份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
将 SQL Server 数据库还原到某个时间点(完整恢复模式) Restore a SQL Server Database to a Point in Time (Full Recovery Model)
备份数据库(“备份选项”页) Back Up Database (Backup Options Page)
备份数据库(“常规”页) Back Up Database (General Page)
应用事务日志备份 (SQL Server) Apply Transaction Log Backups (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
文件还原(简单恢复模式) File Restores (Simple Recovery Model)
文件还原(完整恢复模式)File Restores (Full Recovery Model)