Performs a role change on the log shipping monitor, setting the current secondary database a primary database.
sp_change_monitor_role [ @primary_server = ] 'primary_server'
, [ @secondary_server = ] 'secondary_server'
, [ @database = ] 'secondary_database'
, [ @new_source = ] 'new_tlog_source_directory'
[@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
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).
Only members of the sysadmin fixed server role can execute sp_change_monitor_role.
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\'