sp_change_primary_role

Transact-SQL Reference

Transact-SQL Reference

sp_change_primary_role

Removes the primary database from a log shipping plan.

Syntax

sp_change_primary_role [ @db_name = ] 'db_name'
    , [ @backup_log = ] backup_log
    , [ @terminate = ] terminate
    , [ @final_state = ] final_state
    , [ @access_level = ] access_level

Arguments

[@db_name =] 'db_name'

Specifies the name of the primary database to be removed. db_name is sysname, with no default.

[@backup_log =] backup_log

Backs up the tail end of the primary database transaction log. backup_log is bit, with a default of 1.

[@terminate =] terminate

Specifies that all pending transactions be immediately rolled back, and the primary database placed in single user mode for the duration of this stored procedure. terminate is bit, with a default of 1.

[@final_state =] final_state

Specifies the recovery state of the database after completion of this stored procedure. final_state is smallint, with a default of 1, and can be any of these values.

Value Description
1 RECOVERY
2 NO RECOVERY
3 STANDBY

For more information about the meaning of these options, see RESTORE.

[@access_level =] access_level

Specifies the access level of the database after completion of this stored procedure. access_level is smallint, with a default of 1, and can be any of these values.

Value Description
1 MULTI_USER
2 RESTRICTED_USER
3 SINGLE_USER

For more information about the meaning of these options, see ALTER DATABASE.

Return Code Values

1 (failure) or none (success)

Result Sets

None

Remarks

sp_change_primary_role must be run on the instance of SQL Server marked as the current primary server.

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).

The database transaction logs are backed up before removing it from the log shipping plan.

Permissions

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

Examples

This example shows how to remove the primary database from a log shipping plan.

EXEC sp_change_primary_role @db_name = 'db1',
   @job_id = '6F9619FF-8B86-D011-B42D-00C04FC964FF',

See Also

sp_change_monitor_role

sp_change_secondary_role

sp_resolve_logins