如何配置SQL Server日志传送

SQL Server Log shipping provides a DR (disaster recovery) solution for the SQL Server databases and it can be configured at the database level. In a specific time-gap, SQL Server Transaction Log backup will be taken and copied to the destination site and will be restored. This complete activity or solution will be done by SQL Server job and each step is configured by the user. The learner may confront the difficulties in a couple of steps or while troubleshooting but for the experienced user, it is too easy to set up and handle the SQL Server log shipping set up errors.

SQL Server日志传送为SQL Server数据库提供了DR（灾难恢复）解决方案，可以在数据库级别对其进行配置。 在特定的时间间隔内，将进行SQL Server事务日志备份，并将其复制到目标站点，然后将其还原。 此完整的活动或解决方案将由SQL Server作业完成，并且每个步骤都由用户配置。 学习者可能会在几个步骤中或在进行故障排除时遇到困难，但对于有经验的用户而言，设置和处理SQL Server日志传送设置错误太容易了。

Transaction logs contain a log of all the transactions happening in a SQL Server database. This is extremely helpful in preventing data loss in case of a system failure, in case, you are new to transaction logs in SQL Server, these logs are described in great detail here, A beginner’s guide to SQL Server transaction logs.

事务日志包含SQL Server数据库中发生的所有事务的日志。 如果您不熟悉SQL Server中的事务日志，这对于防止系统故障时的数据丢失非常有帮助，此处将详细介绍这些日志， 即SQL Server事务日志的初学者指南 。

SQL Server instance from which the transaction log backup is shipping is called as primary and the SQL Server instance where transaction log backup is copying and restoring is called as the secondary in the SQL Server log shipping. Before beginning the setup of SQL Server log shipping, the database must be in full recovery model or Bulk-logged is the mandate thing in the SQL Server log shipping. If your database is not in the full recovery model or Bulk logged then below the T-SQL proclamation can assist with making it in Full or Bulk-logged model.

从中传送事务日志备份SQL Server实例称为主服务器，从中拷贝和还原事务日志备份SQL Server实例称为SQL Server日志传送中的辅助服务器。 在开始设置SQL Server日志传送之前，数据库必须处于完全恢复模式，否则SQL Server日志传送中必须进行大容量日志记录。 如果您的数据库不在完全恢复模型或大容量日志记录中，则下面的T-SQL声明可以帮助使其成为完全或大容量日志记录模型。

To make your database in the Full recovery model:

要使数据库处于完全恢复模式：

USE [master]
GO
ALTER DATABASE [publisher] SET RECOVERY FULL WITH NO_WAIT
GO

To make your database in the Bulk Logged recovery model:

要使数据库成为大容量日志恢复模型，请执行以下操作：

USE [master]
GO
ALTER DATABASE [publisher] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

Here, the publisher is the database name at the primary server end. Even the database recovery model can be changed by SSMS also with utilizing the directory as below:

在这里，发布者是主服务器端的数据库名称。 SSMS甚至可以利用以下目录来更改数据库恢复模型：

Right-click on database name >> Property >> Option >> Recovery Model

右键单击数据库名称>>属性>>选项>>恢复模型

If your database is not in Full or Bulk Logged recovery model, then SQL Server Log shipping step will return an error as “This database cannot be a primary database in a log shipping configuration because it uses the simple recovery model. You must use the full or bulk-logged recovery model before transaction logs can be generated.“.

如果您的数据库不是完全恢复或批量记录恢复模式，则SQL Server日志传送步骤将返回错误“ 该数据库不能是日志传送配置中的主数据库，因为它使用简单的恢复模型。 必须先使用完整或批量记录的恢复模型，然后才能生成事务日志。 ”。

借助SSMS配置SQL Server日志传送 (Configure SQL Server Log shipping with the help of SSMS)

As an initial step, the publisher database must be enabled for the SQL Server log shipping configuration. To enable the SQL Server log shipping at the primary end, open the property of the database or expand the task list of a database in the SSMS.

首先，必须为SQL Server日志传送配置启用发布者数据库。 若要在主端启用SQL Server日志传送，请打开数据库的属性或在SSMS中展开数据库的任务列表。

Click on the Ship Transaction Logs; a checkbox will be available with the title as Enable this as a primary database in a log shipping configuration. Select this checkbox to proceed ahead:

点击船舶交易日志 ; 复选框将可用，标题为“ 在日志传送配置中将其启用为主数据库” 。 选中此复选框以继续进行操作：

Here, we are going to setup SQL Server Log shipping for the publisher database.

在这里，我们将为发布者数据库设置SQL Server日志传送。

为数据库启用SQL Server日志传送配置 (Enable the database for SQL Server Log shipping configuration)

As stated above, a database needs to be enabled for this SQL Server Log shipping configuration. When this checkbox is selected, the user will be allowed to schedule the SQL Server transaction log backup job.

如上所述，需要为此SQL Server日志传送配置启用数据库。 选中此复选框后，将允许用户安排SQL Server事务日志备份作业。

A Backup Setting button will allow the user to set up the scheduler of SQL Server transaction log backup.

备份设置按钮将允许用户设置SQL Server事务日志备份的调度程序。

事务日志备份设置 (Transaction Log Backup Settings)

SQL Server transaction log backup setting screen allows addressing the backup folder path. Both the Network path and the file system path should be specified in the form. This is the file system location where a backup job will put the transaction log and it will be picked up for copy and restore to the secondary server. You will notice a few more fields in the dialog box.

SQL Server事务日志备份设置屏幕允许寻址备份文件夹路径。 网络路径和文件系统路径均应在表格中指定。 这是备份作业将在其中放置事务日志的文件系统位置，它将被选择进行复制并还原到辅助服务器。 您将在对话框中注意到更多字段。

Delete files older than parameter is to delete the transaction log after (n) Hours/Minutes/Days.

删除早于参数的文件是在（n）小时/分钟/天之后删除事务日志。

Alert if no backup occurs within parameter is to alert if a backup does not occur within (n) Hours/Minutes/Days.

如果参数中未发生任何备份，则发出警报，如果在（n）小时/分钟/天之内未发生备份，则发出警报。

For example, 72 hours is defined for the Delete files older than the parameter value. So here, the file will be deleted from the file system after 72 hours. Make sure that the file should be copied to the destination or secondary server before deleting it. If any backup does not occur on the primary database within 1 hour, then an alert will be triggered because Alert if no backup occurs setting value is 1 hour in the above screen.

例如，为删除早于参数值的文件定义72小时。 因此，这里，文件将在72小时后从文件系统中删除。 在删除文件之前，请确保将文件复制到目标服务器或辅助服务器。 如果在1小时之内没有在主数据库上进行任何备份，则将触发警报，因为在上面的屏幕中“ 如果没有发生备份的警报”的设置值为1小时。

Users can have the right to change the backup job name as well if the user wants to modify it.

如果用户想要修改备份作业名称，则用户也可以更改它。

备份作业计划 (Backup Job Schedule)

Click on the Schedule button in the Transaction Log Backup Setting form. The user will allow configuring the job properties. The scheduler can be defined to run with a polling period in a unit of minutes, hour or day. By default, the setting is to run a backup job in a time gap of 15 minutes. The job execution polling period depends on the backup plan because it is dependent on the reading of a number of transactions in (n) minutes in the database, Transaction Log backup size and very important thing RPO (Recovery Point Objective) and RTO(Recovery Time Objective).

单击事务日志备份设置表单中的计划按钮。 用户将允许配置作业属性。 可以将调度程序定义为以分钟，小时或天为单位的轮询周期运行。 默认情况下，该设置是在15分钟的时间间隔内运行备份作业。 作业执行轮询周期取决于备份计划，因为它取决于数据库在（n）分钟内读取大量事务，事务日志备份大小以及非常重要的RPO （恢复点目标）和RTO （恢复时间）目的）。

配置辅助服务器实例和数据库 (Configure secondary server instance and databases)

Once database backup is scheduled, the SQL Server transaction log backup process will be started to create one full backup of the database. Now, at the secondary or disaster recovery site, that transaction log backup needs to be restored to make it in use as primary when disaster happens.

计划数据库备份后，将启动SQL Server事务日志备份过程以创建数据库的完整备份。 现在，在辅助站点或灾难恢复站点上，需要还原该事务日志备份，以使其在发生灾难时用作主日志。

Click on the Add button, SQL Server will ask to add the instance name and database details on which transaction log backup is going to be restored.

单击“添加”按钮，SQL Server将要求添加实例名称和数据库详细信息，将在该实例名称和数据库详细信息上恢复事务日志备份。

辅助数据库设置（初始化辅助数据库） (Secondary Database settings (Initialize the Secondary Database))

The secondary server instance field can be filled up by SQL Server instance name or SQL Server instance IP address and Port number. On a successful connection, the database name will be visible in the drop-down list of the Secondary database field. The user needs to select the database name, on which a user wants to restore the SQL Server Transaction log backup.

辅助服务器实例字段可以用SQL Server实例名称或SQL Server实例IP地址和端口号填充。 成功连接后，数据库名称将在“ 辅助数据库”字段的下拉列表中可见。 用户需要选择用户要在其上还原SQL Server事务日志备份的数据库名称。

Before shipping the transaction log at the secondary site, a full backup of the primary database must be required to restore at a disaster site or secondary instance end. To perform the restoration of a full backup, SSMS will ask to perform restore operation with three options.

在将事务日志发送到辅助站点之前，必须要求主数据库的完整备份才能在灾难站点或辅助实例端进行还原。 要执行完整备份的还原，SSMS将要求执行三个选项的还原操作。

Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if doesn’t exist). This option will take a full backup of the primary database on the backup folder directory by the backup job and then the backup will be copied to the secondary server and then the backup will be restored on the secondary database.

是的，生成主数据库的完整备份并将其还原到辅助数据库（如果不存在，则创建辅助数据库）。 此选项将通过备份作业对备份文件夹目录中的主数据库进行完整备份，然后将备份复制到辅助服务器，然后将备份还原到辅助数据库上。

Yes, restore an existing backup of the primary database into the secondary database (and create the secondary database if it doesn’t exist). If already database full backup is generated by the user or exists, then the user will be asked to specify the network path of that full backup. Once a backup is verified, a copy job will move that backup file to the secondary site and backup will be restored by the scheduler job.

是的，将主数据库的现有备份还原到辅助数据库（如果不存在，则创建辅助数据库） 。 如果用户已经生成或存在数据库完全备份，则将要求用户指定该完全备份的网络路径。 验证备份后，复制作业会将该备份文件移至辅助站点，并且调度程序作业将还原备份。

No, the secondary database is initialized. When a backup is manually restored by the user at the secondary site, then this option will be selected. When a user wants to configure SQL Server Log shipping for the large-sized database, then database administrators always use this option. But make sure that the database needs to be restored with No recovery or Standby mode.

否，辅助数据库已初始化 。 当用户在辅助站点上手动还原备份时，将选择此选项。 当用户想要为大型数据库配置SQL Server日志传送时，数据库管理员将始终使用此选项。 但是请确保需要使用“ 无恢复”或“ 待机”模式来还原数据库。

辅助数据库设置（复制文件） (Secondary Database Settings (Copy Files))

Copy files screen will ask the user to fill up the destination folder for copied files on the secondary or disaster site. Usually, that folder directory path should be located on the secondary server. Transaction log backup will be restored on the secondary database from this directory. You will find one more parameter, Delete copied files after. Same as the primary site, transaction log backup will be deleted from the file system after the (n) Hours/Days.

“复制文件”屏幕将要求用户为辅助站点或灾难站点上的复制文件填充目标文件夹。 通常，该文件夹目录路径应位于辅助服务器上。 事务日志备份将从该目录恢复到辅助数据库上。 您还会找到一个参数， 在之后删除文件 。 与主站点相同，事务日志备份将在（n）小时/天后从文件系统中删除。

Here, 72 Hours is defined for the above SQL Server Log shipping configuration. So, the SQL Server Transaction log backup file will be deleted after 72 hours. The user will allow modifying the Copy job name as well.

在这里，为上述SQL Server日志传送配置定义了72小时。 因此，SQL Server事务日志备份文件将在72小时后删除。 用户还将允许修改复印作业名称。

复制作业配置 (Copy Job Configuration)

Users can configure the copy job scheduler property as defined in the below screen. Occurs every parameter will allow defining the polling period of job execution. Here, we have used default values as 15 minutes for this setup.

用户可以按照以下屏幕中的定义配置复制作业计划程序属性。 发生每个参数将允许定义作业执行的轮询周期。 在这里，我们将默认值设置为15分钟。

辅助数据库设置（还原事务日志） (Secondary Database Settings (Restore Transaction Log))

The last step at the secondary site is the configuration of the restore process of SQL Server Transaction log backup. The database must be either in No recovery mode or Standby mode when the Transaction log is shipped on the secondary. Users will be asked to select any one of them (No recovery / Standby).

辅助站点上的最后一步是配置SQL Server事务日志备份的还原过程。 辅助日志附带事务日志时，数据库必须处于“ 无恢复”模式或“ 备用”模式。 系统将要求用户选择其中之一（无恢复/待机）。

Delay restoring backups at least and Alert if restore occurs within are two options in the Transaction log restore step. If a user wants to cause some delay in the restore of the database after copying, then the delay can be defined with (n) in Minutes/Hours/Days. If any backup is restored within the (n) minutes, then alert will be triggered to the configured mail list.

事务日志还原步骤中的两个选项至少要延迟还原备份，并且如果发生还原 ，则发出警报 。 如果用户要在复制后导致数据库恢复延迟，则可以用分钟/小时/天的（n）定义延迟。 如果在（n）分钟内恢复了任何备份，则将触发对已配置邮件列表的警报。

Here, the user is allowed to modify the Restore job name.

在此，允许用户修改“ 还原”作业名称。

恢复作业配置 (Restore job configuration)

Users can configure the restore job scheduler property as defined in the below screen. Occurs every parameter will allow defining the polling period of job execution. Here, we have used default values as 15 minutes for this setup.

用户可以按照以下屏幕中的定义配置还原作业计划程序属性。 发生每个参数将允许定义作业执行的轮询周期。 在这里，我们将默认值设置为15分钟。

保存SQL Server日志传送配置 (Save SQL Server Log Shipping Configuration)

On save Log shipping configuration, the configuration will be saved and the job will be created at the primary and secondary end. The below screen will show you the status of each action of the Log shipping. If any of the action is failed, then Status will be failed with the error message, refer to the below screen.

在保存日志传送配置时，将保存该配置，并将在主要和次要端创建作业。 以下屏幕将向您显示日志传送的每个操作的状态。 如果任何操作失败，则状态将失败，并显示错误消息，请参见以下屏幕。

On a successful set up of Log shipping for the database, the user can check the secondary database at the disaster site. In the below screen, the secondary database subscriber is in the restoring mode, which means SQL Server transaction log backup is getting restored.

成功设置数据库的日志传送后，用户可以在灾难站点检查辅助数据库。 在下面的屏幕中，辅助数据库订阅服务器处于还原模式，这意味着SQL Server事务日志备份正在还原。

After a successful setup of a log shipping, a user must monitor the complete process of Log shipping on a daily basis.

成功设置日志传送后，用户必须每天监视日志传送的完整过程。

摘要 (Summary )

In this article, we learned how to configure the SQL Server Log Shipping feature in SQL Server and how it works with transaction log backups to handle Disaster Recovery. You can read more about this topic here, what is SQL Server log shipping.

在本文中，我们学习了如何在SQL Server中配置SQL Server日志传送功能，以及它如何与事务日志备份一起使用来处理灾难恢复。 您可以在此处阅读有关此主题的更多信息， 什么是SQL Server日志传送 。

翻译自: https://www.sqlshack.com/how-to-configure-sql-server-log-shipping/