Applies To: SQL Server 2014, SQL Server 2016 Preview
Changes secondary database settings.
Applies to: SQL Server (SQL Server 2008 through current version).
sp_change_log_shipping_secondary_primary [ @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.
0 (success) or 1 (failure)
sp_change_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:
Changes settings in the log_shipping_secondary records as necessary.
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.