sp_change_log_shipping_secondary_primary (Transact-SQL)
Changes secondary database settings.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
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']
Arguments
- [ @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 = Microsoft Windows authentication;
0 = Microsoft 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
None
Remarks
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.
Permissions
Only members of the sysadmin fixed server role can run this procedure.
See Also
Reference
System Stored Procedures (Transact-SQL)
Other Resources
Log Shipping
Log Shipping Tables and Stored Procedures