sp_change_secondary_role
Converts the secondary database of a log shipping plan into a primary database.
Syntax
sp_change_secondary_role [ @db_name = ] 'db_name'
, [ @do_load = ] do_load
, [ @force_load = ] force_load
, [ @final_state = ] final_state
, [ @access_level = ] access_level
, [ @terminate = ] terminate
, [ @keep_replication = ] keep_replication
, [ @stopat = ] stop_at_time
Arguments
[@db_name =] db_name
Specifies the name of the secondary database. db_name is sysname, with no default.
[@do_load =] do_load
Specifies that any pending transaction logs be copied and restored before converting db_name to a primary database. do_load is bit, with a default of 1.
[@force_load =] force_load
Specifies that the –ForceLoad option be used in restoring any pending transaction logs to the secondary database. This option is ignored unless do_load is set to 1. force_load 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.
[@terminate =] terminate
Specifies that all pending transactions be immediately rolled back, and the secondary database placed in single user mode for the duration of this stored procedure. terminate is bit, with a default of 1.
[@keep_replication =] keep_replication
Specifies that replication settings be preserved when restoring any pending transaction logs on the secondary database. This option is ignored unless do_load is set to 1. keep_replication is bit, with a default of 0.
[@stopat =] stop_at_time
Specifies that when applying any pending transaction logs, the secondary database be restored to the state it was in as of the specified date and time. This option is ignored unless do_load is set to 1. stop_at_time is datetime, with a default of NULL.
Return Code Values
0 (success) or –1 (failure)
Result Sets
None
Remarks
sp_change_secondary_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).
Permissions
Only members of the sysadmin fixed server role can execute sp_change_secondary_role.
Examples
This example shows how to convert the secondary database to a primary database. Previously shipped transaction logs are applied on the secondary database before it is converted.
EXEC sp_change_secondary_role @db_name = 'db2',
@do_load = 1,
@final_state = 1,
@access_level = 3