sp_change_log_shipping_secondary_primary (Transact-SQL)

APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Changes secondary database settings.

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' The name of the primary instance of the Microsoft SQL Server Database Engine in the log shipping configuration. primary_server is sysname and cannot be NULL.

[ @primary_database = ] 'primary_database' Is the name of the database on the primary server. primary_database is sysname, with no default.

[ @backup_source_directory = ] 'backup_source_directory' The directory where transaction log backup files from the primary server are stored. backup_source_directory is nvarchar(500) and cannot be NULL.

[ @backup_destination_directory = ] 'backup_destination_directory' The directory on the secondary server where backup files are copied to. backup_destination_directory is nvarchar(500) and cannot be NULL.

[ @file_retention_period = ] 'file_retention_period' Is the length of time in minutes in which the history will be retained. history_retention_period is int, with a default of NULL. A value of 14420 will be used if none is specified.

[ @monitor_server_security_mode = ] 'monitor_server_security_mode' The security mode used to connect to the monitor server.

1 = Windows Authentication;

0 = SQL Server Authentication. monitor_server_security_mode is bit and cannot be NULL.

[ @monitor_server_login = ] 'monitor_server_login' Is the username of the account used to access the monitor server.

[ @monitor_server_password = ] 'monitor_server_password' Is the password of the account used to access the monitor server.

Return Code Values

0 (success) or 1 (failure)

Result Sets



sp_change_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:

  1. Changes settings in the log_shipping_secondary records as necessary.

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


Only members of the sysadmin fixed server role can run this procedure.

See Also

About Log Shipping (SQL Server)
System Stored Procedures (Transact-SQL)