关于日志传送 (SQL Server)About Log Shipping (SQL Server)

适用对象: yesSQL Server(仅限 Windows)noAzure SQL 数据库 noAzure SQL 数据仓库 no并行数据仓库APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server使用日志传送,可以自动将“主服务器” 实例上“主数据库” 内的事务日志备份发送到单独“辅助服务器” 实例上的一个或多个“辅助数据库” 。Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. 事务日志备份分别应用于每个辅助数据库。The transaction log backups are applied to each of the secondary databases individually. 可选的第三个服务器实例(称为“监视服务器 ”)记录备份和还原操作的历史记录及状态,还可以在无法按计划执行这些操作时引发警报。An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

本主题内容:In this Topic:

优势Benefits

  • 为单个主数据库以及一个或多个辅助数据库(每个数据库都位于单独的 SQL ServerSQL Server实例上)提供灾难恢复解决方案。Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL ServerSQL Server.

  • 支持对辅助数据库的受限的只读访问权限(在还原作业之间的间隔期间)。Supports limited read-only access to secondary databases (during the interval between restore jobs).

  • 允许用户将延迟时间定义为:从主服务器备份主数据库日志到辅助服务器必须还原(应用)日志备份之间的时间。Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. 例如,如果主数据库上的数据被意外更改,则较长的延迟会很有用。A longer delay can be useful, for example, if data is accidentally changed on the primary database. 如果很快发现意外更改,则通过延迟,您可以在辅助数据库反映此更改之前从其中检索仍未更改的数据。If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.

术语和定义Terms and Definitions

主服务器 (primary server)primary server
位于生产服务器上的 SQL ServerSQL Server 实例。The instance of SQL ServerSQL Server that is your production server.

主数据库 (primary database)primary database
希望备份到其他服务器的主服务器上的数据库。The database on the primary server that you want to back up to another server. 通过 SQL Server Management StudioSQL Server Management Studio 进行的所有日志传送配置管理都是在主数据库中执行的。All administration of the log shipping configuration through SQL Server Management StudioSQL Server Management Studio is performed from the primary database.

辅助服务器 (secondary server)secondary server
想要在其中保留主数据库的热备用副本的 SQL ServerSQL Server 实例。The instance of SQL ServerSQL Server where you want to keep a warm standby copy of your primary database.

辅助数据库 (secondary database)secondary database
主数据库的热备用副本。The warm standby copy of the primary database. 辅助数据库可以处于 RECOVERING 状态或 STANDBY 状态,这将使数据库可用于受限的只读访问。The secondary database may be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access.

监视服务器 (monitor server)monitor server
跟踪日志传送的所有详细信息的 SQL ServerSQL Server 的可选实例,包括:An optional instance of SQL ServerSQL Server that tracks all of the details of log shipping, including:

  • 主数据库中事务日志最近一次备份的时间。When the transaction log on the primary database was last backed up.

  • 辅助服务器最近一次复制和还原备份文件的时间。When the secondary servers last copied and restored the backup files.

  • 有关任何备份失败警报的信息。Information about any backup failure alerts.

重要

配置监视服务器之后,只有先删除日志传送才能对其进行更改。Once the monitor server has been configured, it cannot be changed without removing log shipping first.

备份作业backup job
一种 SQL ServerSQL Server 代理作业,它执行备份操作,将历史记录信息记录到本地服务器和监视服务器上,并删除旧的备份文件和历史记录信息。A SQL ServerSQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. 启用日志传送后,将在主服务器实例上创建作业类别“日志传送备份”。When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance.

复制作业copy job
一种 SQL ServerSQL Server 代理作业,它将备份文件从主服务器复制到辅助服务器中的可配置目标,并在辅助服务器和监视服务器中记录历史记录。A SQL ServerSQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. 在数据库上启用日志传送后,将在日志传送配置中在各辅助服务器上创建作业类别“日志传送复制”。When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.

还原作业restore job
一种 SQL ServerSQL Server 代理作业,它将复制的备份文件还原到辅助数据库。A SQL ServerSQL Server Agent job that restores the copied backup files to the secondary databases. 它将历史记录信息记录在本地服务器和监视服务器上,并删除旧文件和旧历史记录信息。It logs history on the local server and the monitor server, and deletes old files and old history information. 在数据库上启用日志传送后,在辅助服务器实例上会创建作业类别“日志传送还原”。When log shipping is enabled on a database, the job category "Log Shipping Restore" is created on the secondary server instance.

警报作业alert job
一种 SQL ServerSQL Server 代理作业,它在备份或还原操作在指定阈值内未成功完成时为主数据库和辅助数据库引发警报。A SQL ServerSQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. 在数据库上启用日志传送后,在监视服务器实例上会创建作业类别“日志传送警报”。When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance.

提示

对于每个警报,您需要指定警报编号。For each alert, you need to specify an alert number. 此外,请确保配置警报以便在引发警报时通知操作员。Also, be sure to configure the alert to notify an operator when an alert is raised.

日志传送概述Log Shipping Overview

日志传送由三项操作组成:Log shipping consists of three operations:

  1. 在主服务器实例中备份事务日志。Back up the transaction log at the primary server instance.

  2. 将事务日志文件复制到辅助服务器实例。Copy the transaction log file to the secondary server instance.

  3. 在辅助服务器实例中还原日志备份。Restore the log backup on the secondary server instance.

日志可传送到多个辅助服务器实例。The log can be shipped to multiple secondary server instances. 在这些情况下,将针对每个辅助服务器实例重复执行操作 2 和操作 3。In such cases, operations 2 and 3 are duplicated for each secondary server instance.

日志传送配置不会自动从主服务器故障转移到辅助服务器。A log shipping configuration does not automatically fail over from the primary server to the secondary server. 如果主数据库变为不可用,可手动使任意辅助数据库联机。If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

您可以为了实现报表目的而使用辅助数据库。You can use a secondary database for reporting purposes.

此外,可以针对日志传送配置来配置警报。In addition, you can configure alerts for your log shipping configuration.

典型日志传送配置A Typical Log Shipping Configuration

下图显示了具有主服务器实例、三个辅助服务器实例和一个监视服务器实例的日志传送配置。The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. 此图阐释了备份作业、复制作业以及还原作业所执行步骤,如下所示:The figure illustrates the steps performed by backup, copy, and restorejobs, as follows:

  1. 主服务器实例执行备份作业以在主数据库上备份事务日志。The primary server instance runs the backup job to back up the transaction log on the primary database. 然后,该服务器实例将日志备份放入主日志备份文件(此文件将被发送到备份文件夹中)。This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. 在这个图中,备份文件夹位于共享目录“备份共享”中 。In this figure, the backup folder is on a shared directory-the backup share.

  2. 全部三个辅助服务器实例都执行其各自的复制作业,以将主日志备份文件复制到它本地的目标文件夹中。Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.

  3. 每个辅助服务器实例都执行其还原作业,以将日志备份从本地目标文件夹还原到本地辅助数据库中。Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

主服务器实例和辅助服务器实例将它们自己的历史记录和状态发送到监视服务器实例。The primary and secondary server instances send their own history and status to the monitor server instance.

显示备份、复制和还原作业的配置Configuration showing backup, copy, & restore jobs

互操作性Interoperability

日志传送功能可以与下列 SQL ServerSQL Server功能或组件一起使用:Log shipping can be used with the following features or components of SQL ServerSQL Server:

备注

AlwaysOn 可用性组Always On availability groups 和数据库镜像是互斥的。and database mirroring are mutually exclusive. 不能将数据库配置为同时用于这些互斥的功能。A database that is configured for one of these features cannot be configured for the other.

相关任务Related Tasks

另请参阅See Also

AlwaysOn 可用性组概述 (SQL Server)Overview of Always On Availability Groups (SQL Server)