sp_change_log_shipping_secondary_primary (Transact-SQL)sp_change_log_shipping_secondary_primary (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


[ @primary_server = ] 'primary_server',  
[ @primary_database = ] 'primary_database',  
[, [ @backup_source_directory = ] 'backup_source_directory']  
[, [ @backup_destination_directory = ] 'backup_destination_directory']  
[, [ @file_retention_period = ] file_retention_period]  
[, [ @monitor_server_security_mode = ] monitor_server_security_mode]  
[, [ @monitor_server_login = ] 'monitor_server_login']  
[, [ @monitor_server_password = ] 'monitor_server_password']  


[ @primary_server = ] 'primary_server'日志传送配置中的MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine主实例的名称。[ @primary_server = ] 'primary_server' The name of the primary instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine in the log shipping configuration. primary_serversysname ,且不能为 NULL。primary_server is sysname and cannot be NULL.

[ @primary_database = ] 'primary_database'主服务器上的数据库的名称。[ @primary_database = ] 'primary_database' Is the name of the database on the primary server. primary_database sysname,无默认值。primary_database is sysname, with no default.

[ @backup_source_directory = ] 'backup_source_directory'存储主服务器中的事务日志备份文件的目录。[ @backup_source_directory = ] 'backup_source_directory' The directory where transaction log backup files from the primary server are stored. backup_source_directorynvarchar (500) ,且不能为 NULL。backup_source_directory is nvarchar(500) and cannot be NULL.

[ @backup_destination_directory = ] 'backup_destination_directory'辅助服务器上将备份文件复制到的目录。[ @backup_destination_directory = ] 'backup_destination_directory' The directory on the secondary server where backup files are copied to. backup_destination_directorynvarchar (500) ,且不能为 NULL。backup_destination_directory is nvarchar(500) and cannot be NULL.

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

[ @monitor_server_security_mode = ] 'monitor_server_security_mode'用于连接到监视服务器的安全模式。[ @monitor_server_security_mode = ] 'monitor_server_security_mode' The security mode used to connect to the monitor server.

1 = Windows 身份验证;1 = Windows Authentication;

0 = SQL ServerSQL Server身份验证。0 = SQL ServerSQL Server Authentication. monitor_server_security_modebit ,并且不能为 NULL。monitor_server_security_mode is bit and cannot be NULL.

[ @monitor_server_login = ] 'monitor_server_login'用于访问监视服务器的帐户的用户名。[ @monitor_server_login = ] 'monitor_server_login' Is the username of the account used to access the monitor server.

[ @monitor_server_password = ] 'monitor_server_password'用于访问监视服务器的帐户的密码。[ @monitor_server_password = ] 'monitor_server_password' Is the password of the account used to access the monitor server.

返回代码值Return Code Values

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

结果集Result Sets



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

  1. 根据需要更改log_shipping_secondary记录中的设置。Changes settings in the log_shipping_secondary records as necessary.

  2. 如果监视服务器不同于辅助服务器,则会根据需要使用提供的参数更改监视服务器上log_shipping_monitor_secondary中的监视记录。If the monitor server is different from the secondary server, changes monitor record in log_shipping_monitor_secondary on the monitor server using supplied arguments, if necessary.


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

另请参阅See Also

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