Transact-SQL Reference

sp_change_monitor_role

Performs a role change on the log shipping monitor, setting the current secondary database a primary database.

Syntax

sp_change_monitor_role [ @primary_server = ] 'primary_server'
    , [ @secondary_server = ] 'secondary_server'
    , [ @database = ] 'secondary_database'
    , [ @new_source = ] 'new_tlog_source_directory'

Arguments

[@primary_server =] 'primary_server'

Is the name of the primary server being replaced. primary_server is sysname, with no default.

[@secondary_server =] 'secondary_server'

Is the name of the secondary server being converted to a primary. secondary_server is sysname, with no default.

[@database =] 'secondary_database'

Is the name of the secondary database being converted to a primary. secondary_database is sysname, with no default.

[@new_source =] 'new_tlog_source_directory'

Is the path to the directory where the new primary server will dump its transaction logs. new_tlog_source_directory is nvarchar(128), with no default.

Return Code Values

None

Result Sets

None

Remarks

sp_change_monitor_role must be run on the instance of SQL Server marked as the log shipping monitor.

In order to complete a log shipping role change, you must perform several steps in addition to running this procedure. For more information, see How to set up and perform a log shipping role change (Transact-SQL).

Permissions

Only members of the sysadmin fixed server role can execute sp_change_monitor_role.

Examples

This example shows how to change the monitor to reflect a new primary database. Database 'db2' becomes the new primary database, and will dump its transaction logs to directory '\\newprisrv1\tlogs\'.

EXEC sp_change_monitor_role @primary_server = 'srv1',
   @secondary_server = 'srv2'
   @database = 'db2',
   @new_source = '\\newprisrv1\tlogs\'