sp_change_log_shipping_secondary_database (Transact-SQL)sp_change_log_shipping_secondary_database (Transact-SQL)

适用于: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

更改辅助数据库设置。Changes secondary database settings.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
sp_change_log_shipping_secondary_database  
[ @secondary_database = ] 'secondary_database',  
[, [ @restore_delay = ] 'restore_delay']  
[, [ @restore_all = ] 'restore_all']  
[, [ @restore_mode = ] 'restore_mode']  
[, [ @disconnect_users = ] 'disconnect_users']  
[, [ @block_size = ] 'block_size']  
[, [ @buffer_count = ] 'buffer_count']  
[, [ @max_transfer_size = ] 'max_transfer_size']  
[, [ @restore_threshold = ] 'restore_threshold']   
[, [ @threshold_alert = ] 'threshold_alert']   
[, [ @threshold_alert_enabled = ] 'threshold_alert_enabled']   
[, [ @history_retention_period = ] 'history_retention_period']  

参数Arguments

[ @restore_delay = ] 'restore_delay'辅助服务器在还原给定备份文件之前等待的时间,以分钟为单位。[ @restore_delay = ] 'restore_delay' The amount of time, in minutes, that the secondary server waits before restoring a given backup file. restore_delayint ,且不能为 NULL。restore_delay is int and cannot be NULL. 默认值为 0。The default value is 0.

[ @restore_all = ] 'restore_all'如果设置为1,则在还原作业运行时,辅助服务器将还原所有可用的事务日志备份。[ @restore_all = ] 'restore_all' If set to 1, the secondary server restores all available transaction log backups when the restore job runs. 否则,在原还了一个文件之后它将停止。Otherwise, it stops after one file has been restored. restore_allbit ,并且不能为 NULL。restore_all is bit and cannot be NULL.

[ @restore_mode = ] 'restore_mode'辅助数据库的还原模式。[ @restore_mode = ] 'restore_mode' The restore mode for the secondary database.

0 = 使用 NORECOVERY 还原日志。0 = restore log with NORECOVERY.

1 = 用备用还原日志。1 = restore log with STANDBY.

restorebit ,并且不能为 NULL。restore is bit and cannot be NULL.

[ @disconnect_users = ] 'disconnect_users'如果设置为1,则在执行还原操作时,用户将与辅助数据库断开连接。[ @disconnect_users = ] 'disconnect_users' If set to 1, users is disconnected from the secondary database when a restore operation is performed. 默认值 = 0。Default = 0. disconnect_usersbit ,并且不能为 NULL。disconnect_users is bit and cannot be NULL.

[ @block_size = ] 'block_size'用作备份设备的块大小的大小(以字节为单位)。[ @block_size = ] 'block_size' The size, in bytes, that is used as the block size for the backup device. block_sizeint ,默认值为-1。block_size is int with a default value of -1.

[ @buffer_count = ] 'buffer_count'备份或还原操作使用的缓冲区总数。[ @buffer_count = ] 'buffer_count' The total number of buffers used by the backup or restore operation. buffer_countint ,默认值为-1。buffer_count is int with a default value of -1.

[ @max_transfer_size = ] 'max_transfer_size'SQL ServerSQL Server向备份设备发出的最大输入或输出请求的大小(以字节为单位)。[ @max_transfer_size = ] 'max_transfer_size' The size, in bytes, of the maximum input or output request which is issued by SQL ServerSQL Server to the backup device. max_transfersizeint ,并且可以为 NULL。max_transfersize is int and can be NULL.

[ @restore_threshold = ] 'restore_threshold'在生成警报之前,还原操作允许间隔的分钟数。[ @restore_threshold = ] 'restore_threshold' The number of minutes allowed to elapse between restore operations before an alert is generated. restore_thresholdint ,且不能为 NULL。restore_threshold is int and cannot be NULL.

[ @threshold_alert = ] 'threshold_alert'超过还原阈值时引发的警报。[ @threshold_alert = ] 'threshold_alert' Is the alert to be raised when the restore threshold is exceeded. threshold_alert的值为int,默认值为14420。threshold_alert is int, with a default of 14420.

[ @threshold_alert_enabled = ] 'threshold_alert_enabled'指定在超出restore_threshold时是否引发警报。[ @threshold_alert_enabled = ] 'threshold_alert_enabled' Specifies whether an alert will be raised when restore_thresholdis exceeded. 1 = 启用;0 = 禁用。1 = enabled; 0 = disabled. threshold_alert_enabledbit ,并且不能为 NULL。threshold_alert_enabled is bit and cannot be NULL.

[ @history_retention_period = ] 'history_retention_period'将保留历史记录的时间长度(分钟)。[ @history_retention_period = ] 'history_retention_period' Is the length of time in minutes in which the history will be retained. history_retention_periodint。如果未指定值1440,则将使用该值。history_retention_period is int. A value of 1440 will be used if none is specified.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

None

备注Remarks

必须从辅助服务器上的master数据库运行sp_change_log_shipping_secondary_databasesp_change_log_shipping_secondary_database must be run from the master database on the secondary server. 此存储过程执行以下操作:This stored procedure does the following:

  1. 必要时,更改log_shipping_secondary_database记录中的设置。Changes the settings in the log_shipping_secondary_database records as necessary.

  2. 必要时,使用提供的参数更改辅助服务器上log_shipping_monitor_secondary中的本地监视器记录。Changes the local monitor record in log_shipping_monitor_secondary on the secondary server using supplied arguments, if necessary.

权限Permissions

只有sysadmin固定服务器角色的成员才能运行此过程。Only members of the sysadmin fixed server role can run this procedure.

示例Examples

此示例演示如何使用sp_change_log_shipping_secondary_database来更新数据库LogShipAdventureWorks的辅助数据库参数。This example illustrates using sp_change_log_shipping_secondary_database to update secondary database parameters for the database LogShipAdventureWorks.

EXEC master.dbo.sp_change_log_shipping_secondary_database   
 @secondary_database =  'LogShipAdventureWorks'  
,  @restore_delay = 0  
,  @restore_all = 1  
,  @restore_mode = 0  
,  @disconnect_users = 0  
,  @threshold_alert = 14420  
,  @threshold_alert_enabled = 1  
,  @history_retention_period = 14420;  

另请参阅See Also

关于 (SQL Server 的日志传送) About Log Shipping (SQL Server)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)