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',