Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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