Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_change_log_shipping_secondary_primary (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

Changes secondary database settings.

Applies to: SQL Server (SQL Server 2008 through current version).

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.

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:

  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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft