Share via

sp_change_log_shipping_secondary_primary (Transact-SQL)

  • Article

Changes secondary database settings.

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 = 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

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:

  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.

Permissions

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

See Also

Reference

System Stored Procedures (Transact-SQL)

Concepts

About Log Shipping (SQL Server)